Find/Replace with references

**DFrank231** · 08-15-2012, 03:04 PM

I have a column of about a hundred referenced cells that currently look like this:

=Actual2012!J7+Actual2012!T7+Actual2012!Z7

and i want to find/replace the first two 'Actual2012' cells to just one, giving the formula as follows:

=Actual2012!W7+Actual2012!Z7

Is it possible to use find and replace to change the J and T column references to a single W column reference while staying with the row references of 7 through 120 (i cannot just drag down because there are random formulas in the column that would be overwritten).

I am just wondering if this can be accomplished strictly through Find/Replace.

Thanks.

**outofthehat** · 08-16-2012, 01:07 AM

Hi, there's only one way I can think of doing this.

Firstly, do a find/replace finding Actual202!J?+Actual2012!T? (the question marks are placeholders for single characters), and replacing it with a range name, i.e. just a bit of text such as Column_W. you'll have to do the same with ?? and ??? in both references, to cover the two-digit and 3-digit row numbers. You'll end up with a load of #NAME? errors.

Now, did you know that named ranges can refer to RELATIVE cell references? All we have to do, is select the formula in row 7 that's giving the first error, and then go to Formulas tab > Defined Names Group > Define name. Create a name definition for Column_W, but in the 'refers to' box, type =Actual2012!W7 (notice no dollar signs!).

You should now find that those errors magically turn to the answer you want...