Create a formula from 10 Worksheets using an array to return by Date, Name, and Sum the Amount

%3CLINGO-SUB%20id%3D%22lingo-sub-2226984%22%20slang%3D%22en-US%22%3ECreate%20a%20formula%20from%2010%20Worksheets%20using%20an%20array%20to%20return%20by%20Date%2C%20Name%2C%20and%20Sum%20the%20Amount%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2226984%22%20slang%3D%22en-US%22%3E%3CP%3EUsing%20a%20PC.%20O%2FS%20is%20Windows%2010%20Pro.%20Excel%20for%20Microsoft%20365.%3C%2FP%3E%3CP%3ECreate%20a%20formula%20that%20draws%20from%20an%20array%20(F15%3AI31)%20in%26nbsp%3B%3CSPAN%3E10%20Worksheets%20which%20returns%20values%20in%20this%20order%3A%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%22Date%22%20in%20Sequential%20Order%20in%20Column%20F%2C%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%22Name%22%26nbsp%3B%3C%2FSPAN%3E(Name%20of%20Offering)%26nbsp%3B%3CSPAN%3Emade%20on%20that%20date%20in%20Column%20G%2C%20then%2C%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%22Amount%22%20-%20return%20SUM%26nbsp%3B%3C%2FSPAN%3Ein%20Column%20F%20for%26nbsp%3B%3CSPAN%3Eall%20the%20%22%3C%2FSPAN%3E%3CSPAN%3EAmount%22%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eentries%20given%20on%26nbsp%3B%20that%20%22Date%2C%22%20using%20the%20same%20%22Name%22.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EA%20test%20file%20named%20%22Contribution%22%20has%20been%20attached.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2226984%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%3CLINGO-SUB%20id%3D%22lingo-sub-2227126%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20from%2010%20Worksheets%20using%20an%20array%20to%20return%20by%20Date%2C%20Name%2C%20and%20Sum%20the%20Amount%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2227126%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F41479%22%20target%3D%22_blank%22%3E%40Abraham%20M.%20Payton%20Jr.%3C%2FA%3E%26nbsp%3BIf%20feasible%2C%20I%20would%20like%20to%20recommend%20you%20to%20collect%20all%20data%20on%20contributions%20in%20one%20single%20list%20(e.g.%20Date%2C%20Name%2C%20Amount%2C%20Type%2C%20Comment).%20Then%2C%20use%20some%20of%20Excel's%20built-in%20features%20(like%20Pivot%20tables)%20to%20summarise%20the%20data%20in%20accordance%20with%20your%20needs.%20That%20may%20be%20a%20summary%20to%20each%20contributor%2C%20or%20a%20summary%20of%20all%20types%20of%20donations%20during%20a%20period.%20This%20is%20demonstrated%20in%20the%20attached%20file%20and%20can%20be%20easily%20adapted%20to%20a%20situation%20where%20you%20are%20dealing%20with%20more%20than%2010%20contributors.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPS%3A%20Your%20original%20file%20seems%20to%20contain%20real%20peoples%20names%20and%20addresses.%20If%20so%2C%20please%20remove%20the%20file%20from%20your%20post%20%26nbsp%3Band%20upload%20one%20where%20you%20have%20anonymised%20the%20worksheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Using a PC. O/S is Windows 10 Pro. Excel for Microsoft 365.

Create a formula that draws from an array (F15:I31) in 10 Worksheets which returns values in this order:

"Date" in Sequential Order in Column F,

"Name" (Name of Offering) made on that date in Column G, then,

"Amount" - return SUM in Column F for all the "Amount" entries given on  that "Date," using the same "Name".

A test file named "Contribution" has been attached.

4 Replies

@Abraham M. Payton Jr. If feasible, I would like to recommend you to collect all data on contributions in one single list (e.g. Date, Name, Amount, Type, Comment). Then, use some of Excel's built-in features (like Pivot tables) to summarise the data in accordance with your needs. That may be a summary to each contributor, or a summary of all types of donations during a period. This is demonstrated in the attached file and can be easily adapted to a situation where you are dealing with more than 10 contributors. 

 

PS: Your original file seems to contain real peoples names and addresses. If so, please remove the file from your post  and upload one where you have anonymised the worksheets.

 

 

@Riny_van_Eekelen 

Once one has a single table, it is largely a matter of personal preference whether to go with formulas or pivot tables.  It is the bringing together data from a collection of sheets into a single table that strikes me as being the major headache.

 

Is there a 'for each sheet in book' option available in Power Query or would that require adapting the M instructions from 'for each file in folder'?

 

I think I could just about do the data consolidation using a recursive Lambda function and INDIRECT to pick sheet names from a list but it strikes me as something of a sledgehammer to crack an inconveniently shaped nut!

 

Thank you but I did not realize it would entail all of that. I will remove this post. Again, thank you.
Thank you for trying to help me with this.