Data from many Excel sheets...

[JianBean]

I have a problem with an Excel spreadsheet that I'm working on.

It has 10 sheets: 'Master', 'D1', 'D2', 'D3',.... Each of the 'D' sheets is a data sheet, all in the same format, just different data.
The master sheet has a value at the top that I need to use to tell it which sheet to pull it from.
I was wondering, is there someway to put, say the value 'D4' at the top of the Master sheet, in cell A1, and have the rest of the sheet pull it's data from that sheet?

...Without having to say =if(A1='D1',D1!A1,if(A1='D2',D2!A1)... that's just a lot to have to type for every cell.
And I think I'll be adding more sheets later!... Any ideas? I really need this to work! Thank in advance!!

-Bean

 

[SkipVought]

Hi,

If I were going to do it, I'd do it using MS Query (Data/Get External Data).

I'd set up the query for sheet D1 and then MACRO RECORD editing the query.

Then I would modify the code to take the Sheet Name Value from A1 in the Query String.

I'f use the Worksheet_Change event to test if the change is in A1 to fire the Query.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[GlennUK]

Hi Bean,

you could use the INDIRECT function to create a reference to the required sheet.

So, in your Master sheet, this formula:
=INDIRECT(A1&"!B2")
will give you the result from cell B2in whatever sheet has been specified in cell A1 of the Master sheet.

However if you are generating loads of formulae to do this kind of linking, you will need to have the B2 part of the string automatically generated, or use the OFFSET function to give you cells relative to a position on the required sheet, like this:
=OFFSET(INDIRECT($A$1&"!A1"),ROW()-1,COLUMN()-1)
This formula gets the cell values from whatever sheet that has been chosen, at a position equivalent to where this formula is entered on the Master sheet.

Have a play with these amd see what you get.

Cheers, Glenn.

 

[JianBean]

Thank you for your responses! And thank you very much for your speed!

Skip-
your idea seems a little more involved than I need to get right now, but it may come in handy in the next little while, so thank you very much!
Glenn-
thank you aswell, this was a little more what I was looking for, and it works perfectly! And thanks for the extra tip on the OFFSET() function!

Even more than what I needed, thanks guies!

-Bean

 

[GlennUK]

My pleasure Bean.

As you might have guessed, I've had to do similar stuff many times.

Cheers, Glenn.