Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
absolute reference
I have entered a formula in a cell and have copied it to several other
adjacent cells. Now I want to change all the formulas to absolute references so I can sort the list. How can I change a group of cells all at once rather than editing each cell and adding the $'s. Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
absolute reference
Sorting has nothing to do with relative vs absolute addresses.
Tell us what you're trying to do. There has to be a better way than manually entering the dollar signs. -- Regards, Fred "Mike" wrote in message ... I have entered a formula in a cell and have copied it to several other adjacent cells. Now I want to change all the formulas to absolute references so I can sort the list. How can I change a group of cells all at once rather than editing each cell and adding the $'s. Mike |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
absolute reference
You can sort without changing to absolute references but if you want to do it
then right click your sheet tab, view code and paste this in. Select all the cells you want to change and run this:- Sub stance() Dim MyRange As Range Set MyRange = Selection For Each cell In MyRange If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Mike "Mike" wrote: I have entered a formula in a cell and have copied it to several other adjacent cells. Now I want to change all the formulas to absolute references so I can sort the list. How can I change a group of cells all at once rather than editing each cell and adding the $'s. Mike |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
absolute reference
This basically answers my question that 2007 does not have something built in
to do this. For 2003 I was using a third party add-in that did this. Here's what I'm trying to do. Col A has a list of text words Col b has number values Col C has number values Col A is copied to Col D Col E has Col C divided by col B with an average added at the bottom. I Now want to sort Col D and E, sorting on col E and including the average. The add-in I was using let me select all the formulas in Col E and change all the cell references in those cells to absolute reference. I was hoping that Excel 2007 had this ability. So if not, I'll use the macro or the add-in. Mike "Mike H" wrote: You can sort without changing to absolute references but if you want to do it then right click your sheet tab, view code and paste this in. Select all the cells you want to change and run this:- Sub stance() Dim MyRange As Range Set MyRange = Selection For Each cell In MyRange If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Mike "Mike" wrote: I have entered a formula in a cell and have copied it to several other adjacent cells. Now I want to change all the formulas to absolute references so I can sort the list. How can I change a group of cells all at once rather than editing each cell and adding the $'s. Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
absolute reference | Excel Discussion (Misc queries) | |||
absolute reference | Excel Discussion (Misc queries) | |||
what is absolute reference | Excel Discussion (Misc queries) | |||
absolute reference | Excel Worksheet Functions | |||
Absolute Reference | Excel Worksheet Functions |