How to get a Drop down populated with sheet names to fill range of sheet cells?

0
votes

Hi I hope someone can help.

I have a workbook column A is populated with titles leaving column B for data. There is only these 2 columns in use.

I have the same titles in column A replicated on a series of sheets with column B filled out with client data.

On my first sheet (no data in column B) I'm attempting to use a single drop-down populated with the names given to the sheets on my workbook. When a sheet name is selected from the drop-down This is to fill in the data from the corresponding sheet. It's worth noting that all the titles are in the same cells on each sheet.

I can amalgamate all the client data onto one sheet if it's required for a solution but require one set of the data from a single drop-down on a separate sheet

I thought I might manage this with with some kind of range Vlookup linked to a separate sheet with the individual sheet names but i can't get my head round it. Any help greatly appreciated.

Generally I use formulas in excel so if it's possible with VLookup or similar that's great if not then please drop some code for VBA.

1
excelexcel-formulavba
Indirect should work for you. =VLOOKUP(B1,INDIRECT(A2&"!A:C"),3,0), this example the worksheet name would be located in cell A2. – Davesexcel
Hi Having difficulty getting this to work. Only have 2 columns on each sheet column A titles and B being the data. the formula references columns A:C? and set 3? I've tried altering this but it's not coming out. – Oliver Crosby
I was just showing you how to use indirect. INDIRECT(A2&"!A:C"), A2 is has the name of the sheet. A:C was just an example. I don't know what your ranges are in your situation. – Davesexcel

1 Answers

0
votes

From your description, Sheet11 is a customer data sheet and looks like this ...

enter image description here

The sheet that is looking up customer data looks like this ...

enter image description here

Cell D2 contains the drop down list to choose which customer data sheet to look at, in this case Sheet11.

Cell B2 contains the following formula, filled down... 

=IF(ISERROR(VLOOKUP(A2,INDIRECT("'"&$D$2&"'!"&"A:B"),2,FALSE)),"",VLOOKUP(A2,INDIRECT("'"&$D$2&"'!"&"A:B"),2,FALSE))

It constructs a string representing the customer data to look in, and uses the INDIRECT function to convert that string into a valid reference. It uses VLookup to find the data of interest. If VLookup returns an error, a null string is returned, otherwise the customer data is returned.

Note: When constructing the string, the sheet name is enclosed in single quotes, in the event there is a space in the sheet name.