01-15-2020 01:31 AM
01-15-2020 01:31 AM
01-15-2020 08:28 AM
First, I commend you for doing your own payroll. Back in the 70s, before Excel even existed, I was using one of the precursor spreadsheets (just for fun, mind you) to do my own taxes. And that's when I learned the benefits of creating a tax table, having the cutoffs for each income level and associated tax percent etc., all there, so I could do "what-if" analyses based on different income levels, and so forth. It was fun, but over time I realized I'd learned what I could, and my income got more complicated, so I moved to tax packages. My point is not that you should do that; rather, I'm just saying I appreciate how your goal is to have a workable table that you can refer to using HLOOKUP or INDEX and MATCH.... You are being wise to avoid hard-coding as much as possible.
That said, my career also included a stint as the director of the HR and Payroll database of a major corporation, and during that time I extended my learning of the value of business tables, not only to avoid hard-coding data that can change from year to year, but also to help make SQL queries legible/understandable. That applies here, in the sense that you want your Excel formulas to be things you can make sense of.
All of which leads me to wonder if you wouldn't benefit from taking that CSV file you get from Revenue sources and converting it (once each year) to a table of your own design, a table with only one header row instead of two, and a table with headers that are clear. You could even use HLOOKUP or whatever to do that conversion, so it would be consistent so long as they were consistent from year to year; changing it only when they changed. But YOU would have a single table that conformed to good Excel principles (single header row), and could even then use the Name....Create capability to assign names to the various cells from that Revenue table....and your formulas would then be intelligible.
You asked for any/all advice, so I thought I'd throw those thoughts into the hopper.
If you'd like to pursue further....I'd be interested in seeing what that CSV file looks like in its entirety.
by Snuffles00 on May 29, 2020
by Joe McDaid on September 25, 2018
Posted in Microsoft Ignite The Tour 2019 on October 10, 2019