Forum Discussion
Find and Replace
In general, of course, one of the truisms of spreadsheet design is to avoid hard-coding a variable into a formula. We would always recommend against, for example, calculating sales tax on a product by using a formula that says =(A1*.0875) where A1 is the product price and .0875 is the sales tax rate. Instead, you have a named range called SalesTax and it has the value 0.0875 in it; so when the sales tax rate changes (as it inevitably will), you change it one place, and the formula can stay always constant =(A1*SalesTax)
What you've been doing is "hard code" the year (which changes, you know, annually) into formulas, as well as into folder and file names. It probably worked just fine for years 2010-2019, but getting into the 2020s has created some major hiccups.
You did say the conversion from 2019 to 2020 went well just now. If you had done the 2020 to 2021 first, then the 2019 to 2020 would still work (I'm thinking)....so a question: did you make a safety backup copy of the original files before you started these changes? Or might there be an archive to retrieve those from?
Anyway, longer term, you want to be able (I'm guessing) to keep each year's records, but you don't want the formulas to need to be changed; which means, in short, those years shouldn't be hard-coded into the formulas.
All that being said, I wonder if --and this might take some major work on those existing formulas--you'd be better served long term by naming your workbooks/files/folders something more "generic" --e.g., CURRENTYEAR, NEXTYEAR--and then using a business table or two that let you know what years are being referred to. It's hard to be more specific, but you get the idea.....and, yes, I realize it's not a solution to your immediate problem.