Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
I tried to do that but it will not work in the actual cell reference. I'm stumped.
Quote:
|
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fixed cell references | Excel Discussion (Misc queries) | |||
More- AutoFill with Non-Seqeuntial Cell References ? | Excel Worksheet Functions | |||
How to use complex no. functions that have cell references? | Excel Worksheet Functions | |||
Excel formulas based upon the color shading of a cell? | Excel Worksheet Functions | |||
Cell References and External Data | Excel Worksheet Functions |