Change which sheet is referenced via data validation

baxterm

New Member
Joined
Jun 21, 2020
Messages
4
Office Version
  1. 2019
Platform
  1. MacOS
Hi, searching doesn't seem to have turned anything up for this but also I'm not 100% sure what to search for!

What I'd like to create: I have a cell with a Data Validation List in it that let's me choose from the names of all the workbook sheets. On the same sheet I have a bunch of cells that will reference cells in another sheet. I'll always want to reference the same cells but in different sheets. So I'd choose the sheet in this dropdown and a few dozen cells would then fill correspondingly. If I wanted to reference a different sheet I'd simply change the selection in the dropdown cell.

The data validation list will always mirror the names of the sheets (simply numbered 1,2,3 and so on) so that's easy to create. It's also easy to make one cell reference another from a different sheet but what I would like it do is use the info currently in the data validation cell to create that reference.

In my case I have the list of sheets in cell B1

Then I would like, for example, Cell A4 to reference cell C10 in whichever sheet I have selected in cell B1
So how do I use the ='SheetName'!C10 command to actually become ='[B1 entry]'!C10

Is it possible? Is there a different approach I should be taking?

Thanks as always!
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,433
Office Version
  1. 365
Platform
  1. Windows
You could use indirect
Excel Formula:
=INDIRECT("'"&B1&"'!C10")
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,433
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

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