Excel Number To Reference? How to display a cell content

[Jonnyabc 0]

Hey, guys...long time no see. Hopefully I'm gonna kill at least two birds with one stone here (get something posted on here to keep my website alive as well as get some info). So here's the deal:How do I make a value appear in a cell where I have the reference to a cell? No, I'm not talking anything as simple as "=C25". Here, I'll give sort of an example.Suppose we have a table of data ranging from C1:E5. We also have an input cell at A3, and a formula cell for the results in B3 (think of it like a Google search page). Suppose I typed D3 into the input cell, I want B3 to display the results for D3.To do this, we have to convert the text "D3" into literally a cell reference. It's sort of like the opposite of MATCH(), but I can't seem to find anything useful out there about it.This isn't a matter of IF() logic, nor is it as common as using LOOKUP(). Please realize this is only an example for what I need.

Edited August 14, 2010 by Jonnyabc (see edit history)

[BuffaloHelp 24]

This is the classic example of how Excel can be both complex and simple in database.

What you might be searching for is Excel INDIRECT() function.

Is it correct to understand it that when you enter "D3" in A3 cell, B3 will show the value stored in cell D3?

Try putting this formula in B3 cell:

=INDIRECT(A3)

where A3 is your input cell.

This will return the value in the referenced cell number in A3.


You can take this bit further and write the condition if the input cell A3 is blank, it does not show "#REF!"

=IF(A3="","",INDIRECT(A3))

Take this further and show if the cell referenced is empty or not

=IF(A3="","",IF(INDIRECT(A3)="","Cell Empty",INDIRECT(A3)))

[Jonnyabc 0]

Thanks Buffalo! That is true...however, in the real-life example, I would not be using A3...B3 would determine the number and I would then concat the column letter next to it like so...{=INDIRECT("A"&MAX(...))} . Basically I'm looking for the max number in a column and I want it to output both it's location as well as it's value, but I can figure it out from there.

Thanks again!

This is the classic example of how Excel can be both complex and simple in database.
What you might be searching for is Excel INDIRECT() function.

Is it correct to understand it that when you enter "D3" in A3 cell, B3 will show the value stored in cell D3?

Try putting this formula in B3 cell:

=INDIRECT(A3)

where A3 is your input cell.

This will return the value in the referenced cell number in A3.


You can take this bit further and write the condition if the input cell A3 is blank, it does not show "#REF!"

=IF(A3="","",INDIRECT(A3))

Take this further and show if the cell referenced is empty or not

=IF(A3="","",IF(INDIRECT(A3)="","Cell Empty",INDIRECT(A3)))