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!
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!