Lazy formula

[Sportschamp]

OK, I'm trying to create a lazy formula. Hopefully someone can tell me if this is even possible. I'll try my best to explain my issue. I'm tracking monthly donations by people: I have each month in a separate sheet with everyone's name that donates on a monthly basis, and a totals sheet that keeps a running total. What I want to accomplish is if I add a new donor (lets say in March) I want a formula to automatically update the rest of the months and my total sheet with the new donor name. The kicker is everyone is in alphabetically order by last name, and I'm just adding the new donor at the end and sorting.

I tried using what I call the duplicate cell formula where what I type in a cell duplicates itself. Problem is when i sort the sheet by name it messes up my totals sheets formulas.

Hopefully this all makes since. If not let me know and I will try to explain better.

Please let me know if this is even possible to do.

Thanks

 

[James006]

Hi,

What you are after ... is indeed possible ... With Excel everything is possible ...!!!

although you would have to go beyond simple formulas ... with a little bit of macros ...

HTH

 

[wist]

Hi. I think solution to your problem is not via formula but via PivotTable and some modification to your file. If I understood correctly, you can achieve what you need by:
- just having 1 sheet with a Table where you would insert all donations (name, amount & date) and it doesn't matter whether they are sorted alphabetically or not,
- 12 sheets with a PivotTable referencing to the Table with the donations, but in each sheet you filter only 1 month
- 1 sheet with another PivotTable with just the totals and not filtering the months

with this you can just insert the data in the first sheet and then just refresh all pivots in one click which can be sorted however you like and can display either all donors even if they didn't donate that month or just the ones that donated that month

And if you are open to modifying your file even further I'd recommend to just have 2 sheets instead of 14 ..one with the Data and one with PivotTable showing all months and totals ...it's just 14 columns: names, 12 months and the totals are already there as well. Seems more elegant to me

 

[Sportschamp]

Wist
Thanks, for that pivot table idea. I may try this out and see how it works.