how to replace all relative references to fixed in a sheet

[przemke]

Hi there
I have like thousands of cells in a sheet with reference or formula with reference that need to be changes to fixed reference (or how do we call it in english - reference in say C1 has to be kept to C1 in the next sheet although I add new rows there

I quickly need to do it today, I was told before there should be a macro for that but it didn't work.
So perhaps there's another way or someone could post/refer me to a working macro for that with instructions how to run it ?

[DonkeyOte]

reference in say C1 has to be kept to C1 in the next sheet although I add new rows there

You would need to rewrite your functions I'm afraid... ie instead of:

=C1

you would need to use either

=INDEX(C:C,1)

or

=INDIRECT("C1")
(Indirect is Volatile)

As rows are added both of the above will continue to reference row 1... INDEX would generate a #REF error if column C were deleted... if that were likely you would need to reference the entire sheet (INDEX(1:65536,1,3) etc...)

As I see it you have no shortcut available other than perhaps running an Edit Replace and replacing C1 with INDIRECT("C1") but if you have thousands of formulas performance will be affected given Volatile nature of INDIRECT function.

[przemke]

Thanks that helps. I did not notice that a fixed reference will change too if a row is added

How would I replace the rows as you suggest as most of them contain this formula, so the reference is there twice:
=IF(ISNA(calculations!E11),"",calculations!E11)
(each for each cell in the "calculations" sheet)
I mean I was thinking I could do it by find&replace for all cells. I could do that manually for all columns i.e 60 at the moment

There is about 1/4 of 20000 cells currently relevant with that formula, the rest is blank for now. Would it really slow it down much with INDIRECT?

But removing certain column would take place too so I think that it's better to just add or remove the column manually at the end :/ ? It will not take place often but I wanted to avoid that for the less skilled people...

[przemke]

Just to add to actual topic question: I attach a macro that does convert all selected columns from fixed to relative and vice versa.
PS why I cannot edit previous posts ? (I wanted to mark the thread as solved, or do moderator's do it?)