Use list to switch reference to different tabs / worksheets

decksie

New Member
Joined
Apr 1, 2016
Messages
16
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
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
737
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 1, 2016
Messages
16
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,699
Messages
5,766,001
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top