Contact US

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!

*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! Already a Member? Login


Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

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