combine data from specific cells from multiple sheets

[NicholasCombe]

Hi there. 

I have been trying to find out a way to do this and searching, but perhaps I'm not using the right questions. 

I have a sheet containing income from returning recycling containers for a 10c refund for each month. Each month is exactly the same layout, so the summary of refund value for each month is always C29. 

We always donate 25% to 5 charities, but the charities change each month. How can I make a summary sheet, that creates a table with the summary of the money received for each month? 

I also want to create a summary sheet for all the charities that we donate to over the year. 

So far I have just been copying and pasting, but the data changes as we update it each month. 

[#2 · Accepted solution]

@NicholasCombe 

You are more likely to get assistance if you attach a spreadsheet rather than a picture, as we won't have to retype your data to demonstrate proposed techniques. In the attached workbook, I typed partial examples only, just for January and February.

Maybe I do not understand the first part of your request: "make a summary sheet, that creates a table with the summary of the money received for each month". The technique for referencing data in another worksheet* is simple: As C29 on the monthly worksheets always contains the total of the Refund Values, formulas on the summary sheet will refer to <the_sheet_name>!C29, e.g., the formula =January!C29. (If any worksheet name contains a space or special character, you must wrap it inside apostrophes, e.g., 'Jan Data'!C29.) Similarly for "Income after donation" values (Net Income) in E33.

* - I see that the Microsoft documentation does not specifically mention Excel for Mac, but I expect the syntax to be the same as for the Windows and web versions.

To reduce the number of times you have to type the worksheet names into formulas, you can even use the INDIRECT function to build and evaluate the references, as you will see in columns B and C of the Summary worksheet.

As for the second part of your request, this can be done in a trickier fashion (without data duplication, and with fewer formulas). But to keep it simple, I just used a helper worksheet (the Aggregation worksheet) to gather copies of the needed data into one area. This worksheet can even be hidden. (The only formulas so far are for cells A2 and A8. If you add months/worksheets, add equivalent formulas for each month further down in column A.)

Then, on the Summary worksheet, one formula (in E5) examines the content of column A in the Aggregation worksheet, FILTERing out the blank rows, selecting UNIQUE values, and SORTing them in ascending order. To get the donation totals, there is a range of formulas (from F5 down), that use the charity name (in column E) to selectively sum the monies in column B of the Aggregation worksheet (displaying an empty string if no charity name is present for that row).

Assumption: The charity names do not change during the year. (For any that do change, you will get multiple rows in the summary.)

[#3]

Thanks SnowMan55. The indirect function is very helpful. I'll try to use the aggregation worksheet with the rest of the months. Cheers.