excel transpose and references to cells in other sheets

[isabel.hay]

Hi, I'm trying to transpose a spreadsheet table (Paste Special/
Transpose). The local formula (=SUM(B1:B2)) transpose fine, but the
formulae that link to other sheets don't:

=SUMIF('Sheet A'!C:C,B5,'Sheet A'!O:O)

becomes

=SUMIF('Sheet A'!#REF!,C90,'Sheet A'!#REF!)

- effectively not keeping the references to the other sheet (Sheet A).

What I want is the formula to keep the same place in the other sheet,
ie:

=SUMIF('Sheet A'!C:C!,C90,'Sheet A'!O:O)

Anyone know how I can make this happen? As it's this sheet I'm
transposing, not the other one.

Many thanks.

 

[Tim879]

If you use $ signs in the formulas, the transpose should work.

 

[isabel.hay]

Yes, "absoluting" the other-sheet references does work.

Is there a way of changing a range of formulas? F4 only seems to work
on one cell - or am I going to have to write code to do it? Thanks for
your help.