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

somanyqs

Board Regular
Joined
Oct 2, 2007
Messages
76
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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-
 
Upvote 0
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!
icon_smile.gif

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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top