Use list to switch reference to different tabs / worksheets

[decksie]

Hello

I have similar data multiple worksheets. I want to be able to switch the worksheet my main tab is referencing using a dropdown list.

Let's say for example I have multiple worksheets containing similar data from different months. 'Nov21' 'Dec21' 'Jan22' etc.
Going forward I will create a new tab with data at the end of each month.

On a main sheet I want to use a vlookup function to reference the other tabs. What I'd like to do is use a drop down list and select between the different worksheets where the data in my main tab will pull data from the relevant worksheet. Can this be done?

Thank you

 

[RasGhul]

Yes we can use an array formula or spill function & filter, what version of excel do you have?

Can you post some sample data for us to look at with XL2BB

 

[decksie]

Hi RasGhul, I have excel professional plus 2013.
I've tried but even though I managed to add XL2BB to the add-in's list, my workplace policy won't let me use it. It's very restrictive, I can't install anything.

Can you perhaps show me a very simple example


Sheet1: A1 can select 2 options. 'Morrison' or 'Albanese'. It's a dropdown list. Those two names are also the names of two additional worksheets in the workbook.
In cell A1 of worksheet 'Morrison' it is populated with 'Scott Morrison'
In cell A1 of worksheet 'Albanese' it is populated with 'Anthony Albanese'

Basically I was a formula in a cell in sheet1 where if I switch the dropdown list between 'Morrison' or 'Albanese' it returns the value in A1 of those sheets, i.e 'Scott Morrison' or 'Anthony Albanese' respectively.

It's a simply example but I reckon I could adapt the logic.

thank you! I've spent hours googling.... I probably don't know what to look for.

 

[decksie]

I think I may have found a solution. Don't waste your time, I'll see if this can help me first.

Select From Drop Down And Pull Data From Different Sheet in Microsoft Excel 2016

Easy way to vlookup from different sheets. Pull data from the different sheet using drop down and VLOOKUP with the help of INDIRECT function. VLOOKUP from different sheets.

www.exceltip.com

Thanks