EXCEL: Need drop down populated with sheets in current workbook 2

[mrmac228]

I have a workbook with some worksheets. I want a drop down on a separate/new worksheet that contains the worksheet names.

Based on this selection I then want another drop down on this new sheet to contain the data in the first row of thae sheet (the heading row) selected from drop down 1.

Based on this I can then finally have a last drop down that contains the actual data in the column selected from drop down 2.

At the moment I can't even see how to get a drop down to refer to data on another sheet. Is this possible before I waste my time? Can I only do this via VB?

Thanks in advance

I love deadlines. I like the whooshing sound they make as they fly by - Douglas Adams

 

[GlennUK]

Do some experiments with Data Validation/List and use =INDIRECT to refer to named lists.

I would have named lists of the column headings for each sheet, each list named the same as the sheet that is corresponds to. ( to name a list use Insert/Name/Define )

So, the first drop-down would simply refer to a list of the sheets. Then for the second drop-down you would do Data/Validation/List and type =INDIRECT($B$2) assuming that cell B2 contains the first drop-down.

As for you your final request to show the data from the column name selected ... this can be done easily if all of the columns are named differently across the entire workbook, by naming the data range the same as the headings, and using =INDIRECT in the Data/Validation specification again.

Let us know how you get on.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

What, it sems, you want to do can all be done without vb code.

Another very powerful function to consider is the OFFSET function. Glenn mentioned Named Ranges,defined in Insert/Name/Define. Check out this FAQ on DYNAMIC RANGES...

How can I rename a table as it changes size faq68-1331

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[mrmac228]

Skip, Glenn thanks v much for your responses.

I've managed to have a little play and this is looking like what I need although I haven't had much time to play.

Just an update to say thanks and I hadn't meant to leave it so long before getting back to you.

Cheers

I love deadlines. I like the whooshing sound they make as they fly by - Douglas Adams