excel reference another tab 1

[structSU10]

I have a spreadsheet in which I want to pull data from different tabs into another. I want to be able to automate this a little by doing the following.

My tabs are labeled AREA 1, AREA 2, etc.

I have data within these tabs that I want to populate a table in a new tab with, but I want to be able to mix and match which AREA populates the sheet.

I was thinking to have a cell that I could type in AREA 1, then within the appropriate cells the data from the tab AREA 1 would populate, and I could just change the one cell to say AREA 2 to alter the table.

I don't know how to make this happen - I have tried a couple things, but the only thing I get to work (since I cant figure it out) is to just have 'AREA 1'!A1, and copy/drag for each area that I am referencing.

Hopefully that was clear... any ideas?

 

[SkipVought]

Hi,

You've kinda shot yourself in the foot by chopping you your data into separate Areax sheets. Is that something that you could change and put all your Area data into a sheet with an Area column? It would make YOUR life a lot simpler!

I also assume that your table has unique headings in row 1.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[GregLocock]

Whatever, you can use indirection in modern excel, using the INDIRECT function

so in b1 type AREA1
in b2 type Q

in b3 type 21

in B4 type =indirect(b1&"!"&B2&text(b3,"0"))

which will read AREA1!Q21 into your worksheet. This has many possibilities.





Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[structSU10]

Thanks,

Yes I did stumble upon the indirect function and got it to work as I intended.