Active cell as input to formula

Question

I was wondering if I can use the active cell, by that I mean the cell that is "highlighted" at a given time with the square border after there was a mouse click there, as an argument in a function. For example, I have created a table with weighted averages with 3 weights: w1 is given in the column headers (kindly see the file below), w2 in the row headers, and w3 which complements w1 and w2 to 1. What I'd like to do is have cells outside the table show the weights the average got when a cell in the table is selected. For example: Screenshot: http://imgur.com/emmBH5S/ file can be found here: https://drive.google.com/file/d/0B_7-00fdslR7Tm11ODRVS296ckk/ Here we see the cell K12 is active, and for K12 w1=0.2, w2=0.15, and so, the cells in the weights tables above (rows 3-4) get the appropriate values according to the weights in the active cell. (of course I manually created this one for illustration purposes) Is there a way to do that? Preferably without VBA if possible Couldn't find any…

Accepted Answer

You don't need VBA (although you may prefer it).

```
W1: =INDEX($F$8:$AA$29,1,MAX(COLUMN(INDIRECT(CELL("address")))-(COLUMN(F8)-1),1))
W2: =INDEX($F$8:$AA$29,MAX(ROW(INDIRECT(CELL("address")))-(ROW(F8)-1),1),1)
W3: =J4-(G4+H4)
```

The `CELL` function with the `address` argument returns the address for whichever cell is active. I use `INDIRECT` to convert that address (just a string) to a cell reference. Then I use

```
=INDEX(Range, 1, Column of Reference)
```

to get the w1 value - the value in the first row and the same column as the active cell. The formula doesn't care what cell you make active, so I stuck a `MAX` in there so it would return a zero if you're out of the range.

Note that simply selecting a cell won't trigger the change. Once you select a cell, press F9 to calculate the sheet to get the proper results.

Active cell as input to formula

Solution

Problem