Dynamic sheet reference based on adjacent cell values

Referencing sheets based on cell value

To anyone who can assist,

I am having some difficulty creating a formula that references a different sheet dynamically based on the results of a different cell.

For example, I want the results in cell D4 to pull from the sheet name mentioned in cell D2.

My formula is designed to first check if the cell I need to start with is blank (so I don’t get a ton of excess information I used a probably not eloquent if function to return blanks if my starting point is blank)

After that, I am using a vlookup to return a value in a chart on the sheet mentioned in cell D2

So my formula in cell D4 I am using looks something like this (in theory):

=IF( “Reference to Cell D2 I cant get to work” !$M$3="",""",VLOOKUP(Summary!$B4, “Second reference to Cell D2 I can’t get to work” !$C$3:$R$25,11,FALSE))
Where M3 is a check value on the other sheet where data entry starts.

The reason I want to write the formula in this manner is so that I can then copy it over roughly 500 times which I would prefer to not have to change manually. The cell D2 would need to be a relative reference based on the location of my hypothetical cell D4.

If anyone can help me figure out how to compile this formula so it works, I would be very grateful.

Tony,

Thank you for the quick reply, that worked brilliantly for the first error I was encountering. I am having some challenges extrapolating that to the second reference inside of my vlookup formula. Can you provide any guidance on that section?

Thank you!

That is beautiful. Thank you so much for all your help!