excel reference another tab
excel reference another tab
(OP)
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?
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?
Talk To Other Members
RE: excel reference another tab
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!
RE: excel reference another tab
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: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: excel reference another tab
Yes I did stumble upon the indirect function and got it to work as I intended.