Formulas within Cell References
 

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

Have you tried the formula

=Concatenate("$J$",H1)


--
humejap
------------------------------------------------------------------------
humejap's Profile: http://www.excelforum.com/member.php...fo&userid=5506
View this thread: http://www.excelforum.com/showthread...hreadid=395098

I tried to do that but it will not work in the actual cell reference. I'm stumped.

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

I missed the original question, but whilst
=Concatenate("$J$",H1)
will join to give $J$ joined to the contents of H1 you might need
=Indirect("$J$"&H1)
if you want to pick up the value in the cell indicated by the concat
statement.






jhockstr Wrote:
I tried to do that but it will not work in the actual cell reference.
I'm stumped.



humejap Wrote:
Have you tried the formula

=Concatenate("$J$",H1)


--
humejap

------------------------------------------------------------------------
humejap's Profile:
http://www.excelforum.com/member.php...fo&userid=5506
View this thread:
http://www.excelforum.com/showthread...hreadid=395098


--
jhockstr


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=395435