Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


excel reference another tab

excel reference another tab

excel reference another tab

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?

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.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: excel reference another tab

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.


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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close