Nov 05 2021 11:12 AM
Nov 05 2021 11:12 AM
What you see attached is a template. I'm working off one just like it that I designed to go from 2016-2025. Now I'm working on a MASTER to go from 2026-2035. The MASTER takes two separate formulas and compares them. This way I know Payroll is right for a given week.
The first formula is simple. It's on the tab MASTER. It will look at 300 excel files, when I'm done, and will total up the weekly pay. 001 is what I call an EOW (End of Week). A Technicians work is filled in and it totals up his pay. It reports back to the MASTER and gives me a grand total for the week. Simple enough.
Here's my "monster". The second tab, 01.03.26, is a combination of events. Take 001 for example. There will be a formula in B4, one in D4, he/she may have a debt to pay or is investing in savings so there would be an extra row added beneath and a formula would be in D5. Lots of formulas. Normally I run that out for 5 weeks. So when one week is done, I move the tab to the back and recreate all the formulas 5 weeks in advance. Starting to see my issue?
This would be a typical formula for cell D4 for the date Week Ending 01.03.2026
='F:\2026\TOSI 2026-2035 TECH (A)\2026-2035 TECH TEMPLATES\[001.xlsx]Pay Sheet'!O6
This formula would be duplicated 300 times but 001 would be 002, etc.etc.
Can a formula be created where there would be a cell on 01.03.26 where you would change the O6 in the formula above to O9 and all 300 formulas on that sheet would recognize that change?
Yeah I drive my CPA nuts too...Thanks Folks.
Nov 05 2021 11:46 AMSolution
@DaCrab_101 wrote: Yeah I drive my CPA nuts too...
I'm sure you do.
To be blunt, your design gets in the way of Excel's abilities to help you accomplish what you want to do. So many blank rows is (more accurately, it may be) a way to help humans look at data and see things more clearly.
Arranging your data that way, however, gets in the way of Excel's abilities to use a clean array of data as a database and, based on that, perform the kind of summaries, data extractions--analyses of various types--that Excel excels at.
So for the mental health of any here who might be able to help--and, who knows, maybe the mental health of your CPA as well--are you open to re-designing your whole approach?
Nov 05 2021 12:07 PM