SOLVED
Home

Consolidating data from multiple worksheets

%3CLINGO-SUB%20id%3D%22lingo-sub-260771%22%20slang%3D%22en-US%22%3EConsolidating%20data%20from%20multiple%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260771%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EWhen%20Excel%20does%20a%20consolidation%20from%20different%20worksheets%20and%20the%20%22Create%20links%20to%20source%20data%22%20is%20ticked%20it%20will%20put%20the%20Workbook%20name%20in%20summary%20sheet%20and%20even%20though%20the%20sheets%20have%20different%20names%20that%20it%20summarize%20it%20does%20not%20pull%20through%20the%20worksheet%20name%20so%20that%20it%20is%20clear%20from%20which%20sheet%20it%20is%20consolidating%20the%20numbers.%3C%2FP%3E%3CP%3EI%20even%20gave%20the%20areas%20that%20is%20consolidated%20range%20names%20to%20see%20if%20that%20will%20work%20but%20it%20does%20not.%3C%2FP%3E%3CP%3ESee%20column%20C%20on%20the%20Summary%20sheet%20as%20even%20though%20you%20can%20see%20in%20the%20formula%20from%20which%20sheet%20it%20is%20drawing%20the%20name%20it%20calls%20all%20the%20rows%20Book1.%3C%2FP%3E%3CP%3EHow%20can%20you%20get%20it%20to%20name%20the%20rows%20according%20to%20the%20sheets%20that%20they%20are%20coming%20from%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-260771%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-326124%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20data%20from%20multiple%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326124%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20see%20how%20to%20consolidate%20multiple%20cells%20at%20one%20time%20with%20consolidating%20worksheets%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-261772%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20data%20from%20multiple%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-261772%22%20slang%3D%22en-US%22%3Eby%20the%20way%2C%20I%20did%20not%20use%20consolidation%20function%2C%20just%20used%20cell%20reference%20formula...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-261769%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20data%20from%20multiple%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-261769%22%20slang%3D%22en-US%22%3E%3CP%3Epls%20see%20attached%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-261752%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20data%20from%20multiple%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-261752%22%20slang%3D%22en-US%22%3Elook%20at%20sheets%20Dotsure%2C%20brokers%2C%20binders%20-%20there%20are%20formula%20there%20-%20being%20referenced%20by%20the%20summary%20sheet.%3CBR%20%2F%3Eglad%20it%20helped..%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-261723%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20data%20from%20multiple%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-261723%22%20slang%3D%22en-US%22%3E%3CP%3ELorenzo%3C%2FP%3E%3CP%3EHow%20did%20you%20get%20the%20Row%20Names%20into%20Column%20C%20and%20the%20product%20name%20into%20Column%20B%20on%20the%20summary%20sheet.%20Was%20this%20added%20manually%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20added%20a%20sheet%20called%20%22Erica%20Test%22%20did%20exactly%20the%20same%20in%20the%20consolidation%20function%20as%20you%20did%20but%20the%20result%20looks%20different.%20The%20values%20are%20the%20same%20but%20the%20row%20labels%20and%20columns%20gets%20grouped%20and%20no%20row%20labels%20pull%20through%3F%3C%2FP%3E%3CP%3EWhat%20am%20I%20missing%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-261720%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20data%20from%20multiple%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-261720%22%20slang%3D%22en-US%22%3ESorry%20Lorenzo%2C%20my%20computer%20did%20not%20download%20the%20file%20correctly%20the%20first%20time%2C%20when%20I%20did%20it%20again%20I%20could%20view%20it%20properly.%3CBR%20%2F%3EThanks%20for%20the%20help%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-261717%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20data%20from%20multiple%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-261717%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Lorenzo%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20that%20is%20what%20I%20am%20looking%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20you%20do%20it%20using%20the%20consolidate%20function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-260904%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20data%20from%20multiple%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260904%22%20slang%3D%22en-US%22%3E%3CP%3Epls%20see%20attached%20file.%3C%2FP%3E%3CP%3Ehope%20this%20is%20what%20your%20looking%20for.%3C%2FP%3E%3CP%3Ethanks..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Erica Visser
Occasional Contributor

Hi

When Excel does a consolidation from different worksheets and the "Create links to source data" is ticked it will put the Workbook name in summary sheet and even though the sheets have different names that it summarize it does not pull through the worksheet name so that it is clear from which sheet it is consolidating the numbers.

I even gave the areas that is consolidated range names to see if that will work but it does not.

See column C on the Summary sheet as even though you can see in the formula from which sheet it is drawing the name it calls all the rows Book1.

How can you get it to name the rows according to the sheets that they are coming from?

8 Replies
Solution

pls see attached file.

hope this is what your looking for.

thanks..

 

Hi Lorenzo

 

Yes that is what I am looking for.

 

How do you do it using the consolidate function

Sorry Lorenzo, my computer did not download the file correctly the first time, when I did it again I could view it properly.
Thanks for the help

Lorenzo

How did you get the Row Names into Column C and the product name into Column B on the summary sheet. Was this added manually?

 

I added a sheet called "Erica Test" did exactly the same in the consolidation function as you did but the result looks different. The values are the same but the row labels and columns gets grouped and no row labels pull through?

What am I missing?

look at sheets Dotsure, brokers, binders - there are formula there - being referenced by the summary sheet.
glad it helped..
by the way, I did not use consolidation function, just used cell reference formula...

I am trying to see how to consolidate multiple cells at one time with consolidating worksheets?

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