Getting values from the same cell address in different spreadsheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2072781%22%20slang%3D%22en-US%22%3EGetting%20values%20from%20the%20same%20cell%20address%20in%20different%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2072781%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20with%2025%20tabs%20containing%20structured%20data%20(text%20and%20numbers)%20for%2025%20separate%20entities.%26nbsp%3B%20I%20want%20to%20create%20a%20summary%20sheet%20for%20all%2025%20entities%20that%20references%20certain%20cells%20in%20each%20tab%20and%20reports%20the%20contents%20or%20value%20of%20that%20cell%20in%20separate%20columns.%26nbsp%3B%20I'd%20appreciate%20some%20help%20or%20pointers%20on%20how%20to%20automate%20this%20process%2C%20so%20I%20don't%20have%20to%20manually%20go%20look%20up%20each%20tab.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2072781%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2072841%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20values%20from%20the%20same%20cell%20address%20in%20different%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2072841%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933903%22%20target%3D%22_blank%22%3E%40EseOmats1425%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20job%20for%20INDIRECT%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20workbook%20where%20I%20do%20what%20you're%20seeking%20to%20do.%20Here's%20an%20example%20of%20the%20formula%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDIRECT(%24D4%26amp%3B%22!%22%26amp%3BC%241)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eessentially%20this%20formula%2C%20on%20the%20summary%20sheet%2C%20builds%20a%20cell%20reference%20comprising%20the%20name%20of%20the%20sheet%20%5Bfound%20in%20cell%20D4%20of%20the%20summary%20sheet%5D%20and%20the%20cell%20on%20that%20sheet%20%5Bwhatever%20cell%20reference%20is%20found%20in%20cell%20C1%20of%20the%20summary%20sheet%5D%2C%20and%20brings%20the%20value%20from%20there%20into%20the%20summary%20sheet.%3C%2FP%3E%3CP%3ESaid%20another%20way%2C%20%3CSTRONG%3EINDIRECT%3C%2FSTRONG%3E%20causes%20Excel%20to%20read%20that%20as%20%3CSTRONG%3E%3DSheetName!B1%26nbsp%3B%3C%2FSTRONG%3Eor%20%3CSTRONG%3E%3DSheetName!C5%20%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20can%20copy%20the%20formula%20down%20to%20lower%20rows%20and%20across%20to%20other%20columns%3B%20it%20continues%20to%20refer%20to%20Column%20D%20but%20with%20different%20rows%20containing%20different%20sheet%20names%3B%20it%20continues%20to%20refer%20to%20Row%201%2C%20but%20Columns%20C%20through%20M%20%5Bor%20whatever%5D%2C%20each%20containing%20different%20cell%20references%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20help%20de-code%20this%2C%20let%20me%20give%20you%20a%20link%20to%20an%20instructional%20website%3A%20%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-indirect-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-indirect-function%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2075718%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20values%20from%20the%20same%20cell%20address%20in%20different%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2075718%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20man!%3C%2FP%3E%3CP%3EI%20tried%20it%20and%20it%20works%20great%20for%20my%20purpose.%3C%2FP%3E%3CP%3EThe%20help%20is%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2076521%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20values%20from%20the%20same%20cell%20address%20in%20different%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2076521%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20thought%2C%20the%20data%20values%20I%20was%20trying%20to%20gather%20from%20each%20tab%20(each%20sheet)%20are%20not%20always%20at%20the%20exact%20same%20cell%20address.%26nbsp%3B%20Some%20values%20are%20(and%20the%20INDIRECT%20function%20worked%20well%20then)%20but%20some%20are%20not.%26nbsp%3B%20I%20can't%20do%20a%20Lookup%20because%20the%20criterion%20column%20is%20not%20sorted%20in%20any%20order.%3C%2FP%3E%3CP%3EHow%20do%20I%20find%20these%20specific%20numbers%3F%3C%2FP%3E%3CP%3EWhat%20I%20mean%20is%2C%20%26nbsp%3B%20I'm%20trying%20to%20find%20the%20value%20in%20the%20B%20column%20when%20%22Total%20Funding%22%20or%20%22Total%20Expenses%22%20(or%20any%20other%20text)%20is%20in%20the%20A%20column.%26nbsp%3B%20These%20specific%20text%20criteria%20are%20always%20in%20the%20A%20column%2C%20but%20they%20can%20be%20in%20different%20rows%20depending%20on%20the%20organization%20(separate%20sheet).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20kindly%20for%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2076874%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20values%20from%20the%20same%20cell%20address%20in%20different%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2076874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F933903%22%20target%3D%22_blank%22%3E%40EseOmats1425%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20do%20you%20mean%20by%20'structured%20data'.%26nbsp%3B%20Are%20they%20Excel%20Tables%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20so%20you%20can%20use%20%3CSTRONG%3EINDIRECT%3C%2FSTRONG%3E%20to%20return%20a%20column%20of%20the%20table%20based%20upon%20the%20name%20of%20the%20table%20concatenated%20with%20the%20column%20reference.%26nbsp%3B%20That%20way%20if%20a%20table%20appears%20in%20a%20different%20place%2C%20is%20a%20different%20size%20or%20the%20columns%20are%20in%20a%20different%20order%2C%20it%20makes%20no%20difference.%26nbsp%3B%20An%20example%20might%20be%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20INDIRECT(%40Tables%26amp%3B%22%5BDate%5D%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewhere%20%3CSTRONG%3E%40Tables%26nbsp%3B%3C%2FSTRONG%3Eis%20a%20relative%20reference%20to%20a%20particular%20member%20of%20a%20list%20of%20%3CSTRONG%3ETables%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%5BDate%5D%3C%2FSTRONG%3E%20is%20a%20field%20found%20within%20the%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a spreadsheet with 25 tabs containing structured data (text and numbers) for 25 separate entities.  I want to create a summary sheet for all 25 entities that references certain cells in each tab and reports the contents or value of that cell in separate columns.  I'd appreciate some help or pointers on how to automate this process, so I don't have to manually go look up each tab.

6 Replies

@EseOmats1425 

 

This is a job for INDIRECT

 

I have a workbook where I do what you're seeking to do. Here's an example of the formula:

=INDIRECT($D4&"!"&C$1)

essentially this formula, on the summary sheet, builds a cell reference comprising the name of the sheet [found in cell D4 of the summary sheet] and the cell on that sheet [whatever cell reference is found in cell C1 of the summary sheet], and brings the value from there into the summary sheet.

Said another way, INDIRECT causes Excel to read that as =SheetName!B1 or =SheetName!C5

I can copy the formula down to lower rows and across to other columns; it continues to refer to Column D but with different rows containing different sheet names; it continues to refer to Row 1, but Columns C through M [or whatever], each containing different cell references

 

to help de-code this, let me give you a link to an instructional website: https://exceljet.net/excel-functions/excel-indirect-function

 

@mathetes 

Thank you, man!

I tried it and it works great for my purpose.

The help is much appreciated.

@mathetes 

Hey!

 

So I thought, the data values I was trying to gather from each tab (each sheet) are not always at the exact same cell address.  Some values are (and the INDIRECT function worked well then) but some are not.  I can't do a Lookup because the criterion column is not sorted in any order.

How do I find these specific numbers?

What I mean is,   I'm trying to find the value in the B column when "Total Funding" or "Total Expenses" (or any other text) is in the A column.  These specific text criteria are always in the A column, but they can be in different rows depending on the organization (separate sheet).

 

Thanks kindly for the help.

@EseOmats1425 

What do you mean by 'structured data'.  Are they Excel Tables?  

If so you can use INDIRECT to return a column of the table based upon the name of the table concatenated with the column reference.  That way if a table appears in a different place, is a different size or the columns are in a different order, it makes no difference.  An example might be

= INDIRECT(@Tables&"[Date]")

where @Tables is a relative reference to a particular member of a list of Tables and [Date] is a field found within the table.

@Peter Bartholomew 

The data are in different tabs (sheets) in my Excel workbook and sometimes they are at different cell addresses.

I'm not familiar with Excel Tables.  Can you explain a bit further?

@EseOmats1425 

 

That same educational resource I sent you to earlier for info on INDIRECT can help you with questions like this on the nature of Excel Tables. https://exceljet.net/excel-tables

Spend some time there. Another good resource for getting more acquainted withe Excel is YouTube.