Hello,

I am trying to make a master sheet that has data pulled from different sheets via a drop down box. I think I have found a way to do it with a redicutlous amount of If statements and vlookups with in one formula. It won't allow me finish the formula because here are too many. I tried it in advance on a smaller scale and it does what I want. This is obvously not the best way to get all the info but i'm not sure of another way. Thanks in advance.

here is the formula i want to use:

=IF(H1=M2,VLOOKUP(Test!M3,'Report 1'!A9:E27,2,FALSE),IF(H1=M3,VLOOKUP(M3,'Report 4'!A9:E27,2,FALSE),IF(Test!H1=Test!M4,VLOOKUP(Test!M3,'Report 7'!A9:E27,2,FALSE),IF(H1=M5,VLOOKUP(M3,'Report 10'!A9:E27,2,false),IF(Test!H1=Test!M6,VLOOKUP(Test!M3,'Report 13'!A9:E27,2,false),IF(Test!H1=Test!M7,VLOOKUP(Test!M3,'Report 16'!A9:E27,2,false),IF(Test!H1=Test!M8,VLOOKUP(Test!M3,'Report 19'!A9:E27,2,false),IF(Test!H1=Test!M9,VLOOKUP(Test!M3,'Report 22'!A9:E27,2,false),IF(Test!H1=Test!M10,VLOOKUP(Test!M3,'Report 25'!A9:E27,2,false), IF(Test!H1=Test!M11,VLOOKUP(Test!M3,'Report 28'!A9:E27,2,false), IF(Test!H1=Test!M12, VLOOKUP(Test!M3,'Report 31'!A9:E27,2,false),IF(Test!H1=Test!M13,VLOOKUP(Test!M3,'Report 34'!A9:E27,2,false),IF(Test!H1=Test!M14,VLOOKUP(Test!M3,'Report 37'!A9:E27,2,false))))

H1 is connected to the drop down, and the M column is the corresponding name that relates to the different sheet. Test is the master sheet I want it to be on

This is pretty confusing but some of you guys are pretty good so give it a shot.

Thanks