well *that* is annoying
if you don't care about spreadsheets, don't bother reading this.
for all my little Excel gurus out there though...
so, say you've got bunch of formulas like '=SUM(Monthly!B2:D2)/3' and you want to modify them to be '=SUM(INDIRECT("Monthly!B2:D2"))/3'. you would think you could just find/replace with a wildcard, but wildcards only seem replaceable, not transferable. and you can't just do two find/replace operations, because that breaks the formulas in the process and Excel cries a lot.
so i ended up doing:
why did i bother to figure this out at all? because the 'bunch' of formulas i needed to edit was around 800(correction - 1700) and my brain quickly figured out that spending two minutes to think about it was going to save me *a lot* of work by hand...but still, four steps to do something that *should* take one?!?
now, the logical programmer type in me *knows* there has to be an easier way to do that(even though i may never have to do it again) without writing a script for it...i just haven't learned it yet, right? someone please tell me there's a better way to do that...pretty please? it would make my day in a small way.
for all my little Excel gurus out there though...
so, say you've got bunch of formulas like '=SUM(Monthly!B2:D2)/3' and you want to modify them to be '=SUM(INDIRECT("Monthly!B2:D2"))/3'. you would think you could just find/replace with a wildcard, but wildcards only seem replaceable, not transferable. and you can't just do two find/replace operations, because that breaks the formulas in the process and Excel cries a lot.
so i ended up doing:
- find/replace '=SUM' with 'SUM'
- find/replace 'Monthly' with 'INDIRECT("Monthly'
- find/replace ')/3' with '"))/3'
- find/replace 'SUM' with '=SUM'
why did i bother to figure this out at all? because the 'bunch' of formulas i needed to edit was around 800(correction - 1700) and my brain quickly figured out that spending two minutes to think about it was going to save me *a lot* of work by hand...but still, four steps to do something that *should* take one?!?
now, the logical programmer type in me *knows* there has to be an easier way to do that(even though i may never have to do it again) without writing a script for it...i just haven't learned it yet, right? someone please tell me there's a better way to do that...pretty please? it would make my day in a small way.