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

Copper 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.