+ Reply to Thread
Results 1 to 2 of 2

Reference Multiple Sheets to 1 Sheets Automatically

  1. #1
    Registered User
    Join Date
    02-21-2017
    Location
    Castelford
    MS-Off Ver
    2013
    Posts
    89

    Reference Multiple Sheets to 1 Sheets Automatically

    Hi,

    I have a big work book which is running multiple vlookups but it is really slow so i'm trying to put all my information onto one worksheet and have the vlookup look at that sheet rather than 20 other worksheets as well. i was wondering if there is a way to reference the multiple sheets to the one sheet automatically. I need to be able to add rows to the other sheets as these will keep growing over time.

    The sheet i would like to put all the other information is called "combined parts"

    I have attached an example of what i would like to do.

    Thanks.
    ND
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Reference Multiple Sheets to 1 Sheets Automatically

    Since you have Excel 2013, you also have Excel tables. One of the advantages of Excel tables is that they know exactly how big they are. The combined table is called Table_Combined. So you can do a VLOOKUP against the table like so:
    =VLOOKUP(Value,Table_Combined,2,False) - you don't have to guess how many rows Table_Combined has. Likewise any pivot tables or formulas that reference the table will reference exactly the right amount of data every time.

    I also included another utility to get the sheet list: the utility gets all the sheets and puts them in column C. The program uses this list to determine what sheets to combine. Manually delete the sheets you don't want to process.

    Here are a couple of references on tables:
    http://www.utteraccess.com/wiki/Tables_in_Excel
    https://www.thespreadsheetguru.com/b...t-excel-tables
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [Question] Sumifs multiple sheets with flexible sheets reference
    By tranhaithang in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-29-2014, 04:53 AM
  2. Replies: 7
    Last Post: 11-25-2012, 01:24 AM
  3. Automatically inserting new rows in sheets or updating the sheets
    By AlexOram in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-03-2012, 07:35 AM
  4. Excel 2007 : Reference multiple sheets
    By BrennB in forum Excel General
    Replies: 0
    Last Post: 07-16-2010, 12:08 PM
  5. reference multiple sheets
    By the6thplague in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 10-14-2009, 07:05 PM
  6. Create pivot automatically in all the sheets based on the data in sheets
    By aravindhan_31 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-29-2008, 11:59 AM
  7. How to reference cells in Next/Prev Sheets automatically?
    By theouaegis in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-13-2006, 01:56 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1