Excel Formula Using Defined Names

[AlexaS]

Ok, so I'm probably just not using the correct search terms (wouldn't be the first time), but I'm on a bit of a time-crunch with this one:

I have a list of numbers which match up to a defined cell name on a different sheet. I'm hoping to make the results variable.

For example what I currently have is:

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]ID
[/TD]
[TD]Calculate %
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]=_23_Calc
[/TD]
[/TR]
[TR]
[TD]156
[/TD]
[TD]=_156_Calc
[/TD]
[/TR]
</TBODY>[/TABLE]


23 and 156 are my ID numbers that the list is based on. The =_23_Calc is the defined cell name from the other sheets where the percentage is calculated. Is there anyway to have the cell pull the reference based on the value in the relevant ID cell?

I.E.

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]ID
[/TD]
[TD]Calculate %
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]=_&"A2"&_Calc
[/TD]
[/TR]
[TR]
[TD]156
[/TD]
[TD]=_&"A3"&_Calc
[/TD]
[/TR]
</TBODY>[/TABLE]

So that even if the list changes, we don't need to change the formulas as well. Is this possible? Am I just typing the formula wrong (because the above didn't like me)?

Thanks for any help you might be able to give

 

[Special-K99]

Try
=INDIRECT("_"&A2&"_Calc")

 

[pplstuff]

This sounds like a mess of a spreadsheet, but here's the formula you're looking for.

=indirect("_"&a2&"_Calc")

In the future, look into vlookup() or Index(match()) functions.

 

[AlexaS]

I've never used indirect before. And you should have seen the spreadsheet before I got to it. Also - the calculation is based off of a list of values going down. From what I've struggled through with vlookup before, it wouldn't have worked for this.

Thank you both!