change formula reference in bulk?
 

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(Sum mary!$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

change formula reference in bulk?
 

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,M atchRange,0))="","",INDEX(Idx,MATCH(VendorCommodit ies_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" wrote:

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(Sum mary!$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

change formula reference in bulk?
 

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

"Duke Carey" wrote:

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,M atchRange,0))="","",INDEX(Idx,MATCH(VendorCommodit ies_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" wrote:

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(Sum mary!$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