Mar 21 2021 11:16 PM
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.
Mar 22 2021 01:13 AM
@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.
Mar 22 2021 03:27 AM
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!
Mar 22 2021 03:17 PM
Mar 22 2021 03:18 PM