change formula reference in bulk?

[diaare]

I need the help of all you experts out there. I have the following formula
throughout my spreadsheet.

=IF(INDEX(Summary!I$3:I$80,MATCH(VendorCommodities_Detail!$C3,Summary!$C$3:$C$80,0))="","",INDEX(Summary!$I$3:$I$80,MATCH(VendorCommodities_Detail!$C3,Summary!$C$3:$C$80,0)))

Basically, Column I on sheet2 pulls its data from column I on sheet1 and in
a seperate but similar formula, column L on sheet2 pulls its data from column
J on sheet1. With the exception of rows throughout the columns that give
totals, averages etc (these rows are protected)

Now I want to add a new column into sheet1 (for a new months data) and have
the formulas reference that column instead. But, due to the various rows of
protected totals, I cannot use the autofill option to change all of the
formulas in the column at once. Right now I pretty much have to manually
chnage the formulas in each cell (some are in bunches of 5-10 rows, but still
very time consuming)

Surely there is an easier way to go about this. Any ideas?

thanks
diane

 

[Duke Carey]

One thing you can do to minimize this pain in the future is use some named
ranges. So, for instance, if you gave the name Idx to Summary!I$3:I$80, and
gave the name MatchRange to Summary!$C$3:$C$80, then you'd need only to edit
the range assigned to those names. Assuming that you modified your formula
to be

=IF(INDEX(Idx,MATCH(VendorCommodities_Detail!$C3,MatchRange,0))="","",INDEX(Idx,MATCH(VendorCommodities_Detail!$C3,MatchRange,0)))

Meanwhile, you can press Ctrl-H to call up the Search & Replace dialog, and
search for

Summary!$C$3:$C$80

and replace it with

Summary!$D$3:$D$80

or whatever is appropriate - assuming that this range doesn't appear in your
subtotal formulas, etc

 

[diaare]

Perfect!

I named the ranges and then used the Find and Replace function to change the
formulas to include the range names. Worked beautifully. I can't believe I
had never named ranges before...I think I will go out and edit all of my
spreadsheets!

Thanks so much...

Diane