Home

External reference to different tabs

%3CLINGO-SUB%20id%3D%22lingo-sub-362692%22%20slang%3D%22en-US%22%3EExternal%20reference%20to%20different%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362692%22%20slang%3D%22en-US%22%3E%3CP%3ENeed%20some%20syntax%20help%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20stand%20alone%20worksheet%20%2F%20file%20where%20I%20am%20summarizing%20information%20from%20an%20external%20workbook.%26nbsp%3B%20The%20external%20workbook%20has%20individual%20tabs%2C%20all%20with%20the%20same%20information%20and%20format%2C%20but%20different%20values.%26nbsp%3B%20For%20example%2C%20the%20external%20workbook%20has%20tabs%20for%20Monday%2C%20Tuesday%2C%20Wednesday%2C%20etc.%26nbsp%3B%20The%20information%20documents%20time%20spent%20working%2C%20driving%2C%20sleeping%2C%20etc%20adding%20to%2024%20hrs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20in%20the%20separate%20summary%20file%20is%20on%20a%20single%20tab%20labeled%20'summary%2C'%20and%20creates%20a%20single%20table%20with%20all%20of%20that%20information.%26nbsp%3B%20In%20the%20summary%20table%2C%20the%20reference%20has%20the%20form%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D'%5BReference%20file%20example.xlsx%5DMonday'!%24B2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20save%20time%2C%20I%20want%20to%20replace%20the%20tab%20name%20(Monday%20above)%20with%20my%20own%20text%2C%20preferably%20from%20a%20table%20within%20the%20same%20spreadsheet.%26nbsp%3B%20This%20way%2C%20I%20can%20copy%20the%20link%2C%20and%20reference%20the%20local%20table%20that%20contains%20the%20proper%20tab%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThus%2C%20within%20my%20summary%20sheet%2C%20I'd%20have%20a%20vertical%20table%20of%20cells%20that%20contain%20text%20like%20Monday%2C%20Tuesday%2C%20Wednesday.%26nbsp%3B%20%26nbsp%3BIf%20I%20created%20a%20new%20tab%20in%20the%20external%20file%20called%20Saturday%2C%20I%20would%20drag%20the%20formula%20to%20the%20right%2C%20and%20it%20would%20create%20a%20new%20cell%20with%20the%20name%20Saturday.%26nbsp%3B%20I%20could%20then%20drag%20down%20the%20columns%20to%20fill%20in%20the%20information%20(thus%20the%20absence%20of%20a%20%24%20in%20front%20of%20the%202%20in%20the%20formula%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20external%20reference%20workbook%20would%20look%20like%20this.%26nbsp%3B%20There%20would%20be%20individual%20tabs%2C%20each%20for%20a%20day%20of%20the%20week%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ETime%20Card%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EMonday%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWorking%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDriving%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESleeping%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELoafing%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETotal%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20summary%20worksheet%2C%20in%20a%20stand%20alone%20file%2C%20would%20look%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EMonday%3C%2FTD%3E%3CTD%3ETuesday%3C%2FTD%3E%3CTD%3EWednesday%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWorking%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDriving%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESleeping%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELoafing%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETotal%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMonday%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETuesday%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWednesday%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESaturday%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDragging%20the%20cell%20with%20the%20heading%20Wednesday%20would%20do%202%20things%3A%3C%2FP%3E%3COL%3E%3CLI%3EContinue%20the%20reference%20to%20the%20external%20workbook.%3C%2FLI%3E%3CLI%3EContain%20the%20reference%20to%20the%20new%20tab%20in%20that%20workbook%20with%20the%20name%20Saturday%20from%20the%20table%20below%20it.%3C%2FLI%3E%3C%2FOL%3E%3CP%3ESo%2C%20each%20time%20someone%20updates%20the%20values%20on%20the%20tabs%20in%20the%20external%20workbook%2C%20the%20summary%20values%20would%20also%20change.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20an%20example%20of%20what%20I'm%20doing%2C%20not%20the%20real%20thing.%26nbsp%3B%20I'd%20be%20using%20the%20same%20type%20of%20cell%20references%20in%20the%20formulas%2C%20though.%26nbsp%3B%20I%20want%20to%20do%20this%20without%20using%20macros...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%20-%20The%20table%20with%20the%20days%20of%20the%20week%20can%20be%20in%20a%20row%20instead%20of%20a%20column%2C%20so%20I%20can%20drag%20the%20formula%20to%20the%20right%20and%20retain%20the%20cell%20references.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-362692%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Bill114
New Contributor

Need some syntax help:

 

I have a stand alone worksheet / file where I am summarizing information from an external workbook.  The external workbook has individual tabs, all with the same information and format, but different values.  For example, the external workbook has tabs for Monday, Tuesday, Wednesday, etc.  The information documents time spent working, driving, sleeping, etc adding to 24 hrs.

 

The table in the separate summary file is on a single tab labeled 'summary,' and creates a single table with all of that information.  In the summary table, the reference has the form:

 

='[Reference file example.xlsx]Monday'!$B2

 

To save time, I want to replace the tab name (Monday above) with my own text, preferably from a table within the same spreadsheet.  This way, I can copy the link, and reference the local table that contains the proper tab name.

 

Thus, within my summary sheet, I'd have a vertical table of cells that contain text like Monday, Tuesday, Wednesday.   If I created a new tab in the external file called Saturday, I would drag the formula to the right, and it would create a new cell with the name Saturday.  I could then drag down the columns to fill in the information (thus the absence of a $ in front of the 2 in the formula above.

 

The external reference workbook would look like this.  There would be individual tabs, each for a day of the week:

Time Card 
 Monday
Working8
Driving2
Sleeping8
Loafing6
Total24

 

The summary worksheet, in a stand alone file, would look like this:

 

 MondayTuesdayWednesday
Working8612
Driving212
Sleeping888
Loafing692
Total242424
    
Monday   
Tuesday   
Wednesday  
Saturday   

 

Dragging the cell with the heading Wednesday would do 2 things:

  1. Continue the reference to the external workbook.
  2. Contain the reference to the new tab in that workbook with the name Saturday from the table below it.

So, each time someone updates the values on the tabs in the external workbook, the summary values would also change. 

 

This is an example of what I'm doing, not the real thing.  I'd be using the same type of cell references in the formulas, though.  I want to do this without using macros...

 

Edit - The table with the days of the week can be in a row instead of a column, so I can drag the formula to the right and retain the cell references.

 

Any ideas?  Thank you!

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies