Quickest way to fix cell references

[brad999]

Morning,

I have a row of cells A1:NN1, with each cell having a long formula with non fixed cell references ie no dollar signs in the formula. Is there a way to automatically, possibly with macro, fix every cell references in this row?

Thanks!

[bebo021999]

try find/replace (ctrl-H)

[brad999]

Sorry I forgot to mention that every cell reference is different, so couldn't really use find replace

[XOR LX]

Hi,

It still might be possible using Find & Replace, though it would be helpful to see an example of the formulas in question.

Are all the formulas in that range at least consistent, i.e. they are of the same form and function, though differ only in the cells which they reference?

Regards

[brad999]

The formulas are consistent yes, only the cell references that differ,

[XOR LX]

And would it be possible to see just one of these formulas? Obviously replace any confidential/sensitive information with dummy data if necessary, though as long as everything else remains unchanged.

Regards

[brad999]

Attached file has the formulas, don't worry about the n/a, just need to fix all cell references automatically! thanks

[brad999]

Would it be possible with some kind of macro having I = a-nn, find I replace with $I$

[XOR LX]

Hi,

You simply want every single range reference in those formulas to remain the same, but to be made absolute, with respect to both rows and columns?

Regards

[brad999]

Yes exactly

[Tony Valko]

There's a macro here:

http://tinyurl.com/ChangeCellRefs

[XOR LX]

Perform the following four Find & Replaces (with Replace All) on that range:

Find what: 'Sheet 1'!
Replace with: Sheet 1'!$

Find what: 10
Replace with: $10

Find what: :
Replace with: :$

Find what: 9000
Replace with: $9000

Regards

[brad999]

Excellent, thanks to you both!!

[Tony Valko]

You're welcome. We appreciate the feedback!