How change multiple cells (with ranges) from relative to absolute references

[somanyqs]

I have a whole bunch of cells that are like:
A1 = sum(B1:Z1)
A2 = sum (B2:Z2)
...
A100 = sum (B100:Z100)


I can figure out how to change just one cell (like A1) from relative to absolute (highlighting whole formula, then going to the Formulas toolbar and switching references button - so I can get it to change to sum($B$1:$Z$1) as desired), but I can't figure out how to change all of the cells from A1 to A100 at once.

Thanks!

Using Excel for Mac 2011

 

[NeonRedSharpie]

Use find/replace


Find What: B
Replace With: $B$

Find What: Z
Replace With $Z$

 

[somanyqs]

Thanks. I think I simplified my question too much, sorry about that!
Unfortunately it is a little more general a problem, so I have cells that are:

A1= sum(AA1:BA1,CB1:DE1)
A2 = sum(BC2:DB2,EA2:GA2)
A3 = sum(B3:J3, K3:Y3)

so sort of like this, with random ranges within the sum.
Thanks again-

 

[FitnessNerd]

Select all of the cells you want to change.
Press F2 to get into edit mode.
Add the $ symbol where you need it applied.
Press CTRL+Enter (instead of just enter) and every cell highlighted will have the same value.

If your physical formula has the Cell Reference A1, then what I said will not work. You don't have the example written a a formula so I'm not sure if I answered the way you are expecting.

Edit:

Thanks. I think I simplified my question too much, sorry about that!

Unfortunately it is a little more general a problem, so I have cells that are:

A1= sum(AA1:BA1,CB1:DE1)
A2 = sum(BC2:DB2,EA2:GA2)
A3 = sum(B3:J3, K3:Y3)

so sort of like this, with random ranges within the sum.
Thanks again-

You wrote a clarification at the same time I tried to share a solution. Your updated post makes my option void to you.

 

[NeonRedSharpie]

Thanks. I think I simplified my question too much, sorry about that!
Unfortunately it is a little more general a problem, so I have cells that are:

A1= sum(AA1:BA1,CB1:DE1)
A2 = sum(BC2:DB2,EA2:GA2)
A3 = sum(B3:J3, K3:Y3)

so sort of like this, with random ranges within the sum.
Thanks again-

I'm going to tinker around with a macro just for grins. If you find something else, be sure to update this post.

 

[Qimar]

I know this is an old thread. I recently joined and saw this. I havent seen any other inquiries like this one anywhere else with a good response.

If anyone is wondering about this I think this VBA can help. Highlight the cells you want then run the macro

Sub ConvertFormulasXlrelrow()
Dim rngCell As Range
For Each rngCell In Selection
With rngCell
If .HasFormula Then .Formula = Application.ConvertFormula(.Formula, xlA1, , xlRelRowAbsColumn)
End With
Next rngCell
End Sub

When you run it it will convert your formula from this thread into this

=$A2 = SUM($BC2:$DB2,$EA2:$GA2)
=$B1= SUM($AB1:$BB1,$CC1:$DF1)
=$C3 = SUM($D3:$L3, $M3:$AA3)

You can either drag the formula down or use another macro to fill the formula down for you. To use this macro highlight the row


Sub Curr_Fill_In_Formulas() 'fill in formulas'
Dim LR As Long
LR = Sheets("Sheet1").UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("T4").AutoFill Destination:=Range("T4:T" & LR)


End Sub

I have a whole bunch of cells that are like:
A1 = sum(B1:Z1)
A2 = sum (B2:Z2)
...
A100 = sum (B100:Z100)


I can figure out how to change just one cell (like A1) from relative to absolute (highlighting whole formula, then going to the Formulas toolbar and switching references button - so I can get it to change to sum($B$1:$Z$1) as desired), but I can't figure out how to change all of the cells from A1 to A100 at once.

Thanks!

Using Excel for Mac 2011