Formulas within Cell References

J

jhockstr

Hello All,

I need to use a number that was calculated from a formula in a cel
reference. Here is the situation: I have a large spreadsheet tha
changes periodically. I need to count the number of items in tha
spreadsheet(i will use COUNTA - already works) and then take tha
number and use it as a row number in a cell reference like this:

In cell H1: =COUNTA(A2:A9999)

Then take that number in cell H1 and insert that into say $J$XXXX wher
XXXX represents the number in cell H1.

I'm looking to increase the calculation speed of the workbook by onl
calculating cells that I need to.

Thanks for the help,
Jaso
 
B

BizMark

jhockstr,

You can use the INDIRECT function to do this.

i.e.

=INDIRECT("$J$" & H1)
'returns the cell in Column J and in the row number calculated in H1
as a reference.

e.g. You can work out the SUM of all values entered in Column J with
=SUM(INDIRECT("$J$1:$J$" & H1))

Regards,
BizMar
 
Top