Another Excel question

General Questions
#1

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?

#2

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.

#3

+1
That’s the way to do it.

#4

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.

#5

Thanks, guys - I’ll try those tomorrow. Sounds like just what I needed.

This Board never ceases to amaze me.