Lookup array on a sheet based on Cell input

[gottimd]

In a workbook, I have three sheets labeled Sheet1,sheet2,sheet3.

If on sheet2, in cell a1, I type "Sheet1". How can I get this to make it a functional Vlookup formula on sheet1. I tried Indirect and Index formula's but I am not sure If I am using them correctly.

The actual formula I have right now is:
=IF(M2="Passing",VLOOKUP(F2,INDIRECT("N2")*B75:N120,7,FALSE))

Where N2, is the sheet name that will change in each row. Excel made my formula say ("N2")* but that gets me a "FALSE". What I am hoping it would do, is in cell n2, would be a sheet name, and it would look up that sheet name, same range on all sheets, and return the value.

 

[fairwinds]

Hi,

Try:

=IF(M2="Passing",VLOOKUP(F2,INDIRECT("'"&N2&"'!B75:N120"),7,FALSE))

 

[gottimd]

That worked, thanks for your help!!!!