I’ve got a neat idea to save quite a bit of work in a spreadsheet if I could only figure out how to do it. I don’t even know how to look it up in “Help”.
The problem: I have a command in a cell that brings in the value of another cell, in another spreadsheet. the command that does this is:
=’\NAME OF SPREADSHEET]AM900
I need to generate the cell designation dynamically by combining the AM (the column number) with the 900 (the row number).
This can be done as a text function easily, but I need to get the result incorporated into the command line as shown above so that the appropriate value will be pulled in from the second spreadsheet.
Any ideas?
There might be an easier way to do it, but this will get you the right result using formulas.
=INDIRECT(ADDRESS(A1,B1,"[my workbook.xlsx]Sheet1"))
ETA: where A1 contains the row number and B1 contains the column number.
+1
That’s the way to do it.
Yep, INDIRECT is an amazing little function. It’s pretty rare that you can get a computer to honestly take a character string constructed from other strings and have it interpreted as meaning something other than that literal character string. When I first read about it I thought it was absolutely useless until I needed to do the exact same thing you did: calculate the address of a cell dynamically.
You can also use the ADDRESS function to do something similar. It takes the row number and column number and returns a reference to a cell with at that row and column. This allows you to calculate numerically rows and columns without having to go to R1C1 notation.
Thanks, guys - I’ll try those tomorrow. Sounds like just what I needed.
This Board never ceases to amaze me.