Formula that will allow me to vlookup different tabs in Excel

[Viva_Excel]

I need a formula that will allow me to look up data on different worksheets. I have 5 worksheets (1 summary, and 4 with raw data). The raw data tabs all have the exact same number of rows and columns but the data is from a different region. I want the user to be able to select from a drop-down menu which region they want summary data tab to pull from using a vlookup formula. For example, I have five tabs in my workbook: Tab1) Summary Tab which needs to pull the data from the other four tabs, Tab2) named “West”, Tab3) named “East”, Tab4) named “South”, Tab5) named “North”. Using a drop-down list, I want to be able to select either West, East, South or North and have the vlookup formulas look at the corresponding tab for the data. So, in my example, if I select “North” from the drop-down menu, I want the vlookups to pull data from the “North” tab etc. I do not want to use PIVOT TABLES for this. Can anyone help me with this? Thanks in advance!!!</SPAN>

 

[BenMiller]

Let's say your data validation cell (drop-down) is A1 and you're searching for B1, try =VLOOKUP(B1,INDIRECT(A1&"!"&"A1:B1000"),2,0)

Change the A1:B1000 to where you're searching in the North, East, etc. tabs, and change the 2 to whichever column number you want to return.