Lookup Sheet name and return cell value in that sheet.

Hi All,

I have been looking for a few days now and can't seem to find a formula to works this one out.

I have a macro enabled sheet and a formula which generates a list of sheets on the Main Sheet, beside that I reference data that I would like to return a value based on the sheet name.

Column A contains all the Sheet Names, B the data I want it to return. For simplicity I have named the sheets by number.

I would essentially like cell B# to look at the data in Cell A# of the Main Page and match that with the Sheet name, then return Cell B4 of that sheet.

If an example is needed then I can extract a blank version of the document for reference.

Thank you in advance

Hi guys, I tried to formula but it is returning a #REF error, I'm not sure if it makes a difference but the values being returned are text and not numbers, I'll post a blank version for reference to try an explain what it is that I need, also the cell that is producing the sheet name contains an array formula which might be confusing the formula.

Tony, that is it, as simple matter of setting the "

Thank you all for your assistance. Reputation on its way!