Forum Discussion
Find and Replace
I have a 'grand total' worksheet that pulls data from daily and monthly worksheets/workbooks. It works well but... I need to update the worksheet to replace every reference to 2020 with 2021. The change from 2019 to 2020 worked but the reference errors prevent the update to 2021. Here is a sample, the formula changes needed in one 'grand total' cell:
from:=IF('C:\2020\[2001.xlsx]Jan.20'!$E$3=0,"",'C:\2020\[2001.xlsx]Jan.20'!$E$3)
to:=IF('C:\2021\[2101.xlsx]Jan.21'!$E$3=0,"",'C:\2021\[2101.xlsx]Jan.21'!$E$3)
Three changes need to be made, twice each, directory, file and date.
Every time I try using Find and Replace, there are formula reference errors. Of course, there will be until all changes are made. Excel will not allow a change until the reference is resolved.
So, my question is this: how can I make these changes (a total of 3168) other than one-by-one manually?
Thank you.
Bill Bilusx2@gmail.com
2 Replies
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.
try to replace 2020 instead of 20
