Formulas within Cell References

[jhockstr]

Hello All,

I need to use a number that was calculated from a formula in a cell
reference. Here is the situation: I have a large spreadsheet that
changes periodically. I need to count the number of items in that
spreadsheet(i will use COUNTA - already works) and then take that
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 where
XXXX represents the number in cell H1.

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

Thanks for the help,
Jason


--
jhockstr

[humejap]

Have you tried the formula

=Concatenate("$J$",H1)

[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,
BizMark


--
BizMark