Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with converting a block of cells with Absolute and mixed references to relative references
Hoping that someone can offer some advice on this....
I'm creating a complex excel sheets with lots of relative, mixed, and absolute references. Once I'm done, I'd like to copy a block within the sheet to create a new block. The trouble is that all the absolute references within the block are pointing to reference within the old block. I can fix them manually, but I want to copy the block several times, and it's a lot of work to change each one What I'd like to to do is, once things are as I want them, convert all of the Absolute references in the completed block to relative so that when I past a new block, the addressing will be correct. One trick I've found is that I can convert cells formulas from Absolute to relative one by one using the F4 key, but that's not ideal either (although, it's easier than editing each cell). Anyone know if there is a way to convert a block of cells to relative mode in one shot? Thanks for any advice. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with converting a block of cells with Absolute and mixed references to relative references
Sorry, forgot to mention:
Office 2007 on Vista Business "Vulcan" wrote in message ... Hoping that someone can offer some advice on this.... I'm creating a complex excel sheets with lots of relative, mixed, and absolute references. Once I'm done, I'd like to copy a block within the sheet to create a new block. The trouble is that all the absolute references within the block are pointing to reference within the old block. I can fix them manually, but I want to copy the block several times, and it's a lot of work to change each one What I'd like to to do is, once things are as I want them, convert all of the Absolute references in the completed block to relative so that when I past a new block, the addressing will be correct. One trick I've found is that I can convert cells formulas from Absolute to relative one by one using the F4 key, but that's not ideal either (although, it's easier than editing each cell). Anyone know if there is a way to convert a block of cells to relative mode in one shot? Thanks for any advice. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with converting a block of cells with Absolute and mixed references to relative references
Don't have 2007 but these should work.
Sounds like you need the last one for all rleative. Sub Absolute() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn) Next End Sub Sub AbsoluteCol() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn) Next End Sub Sub Relative() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlRelative) Next End Sub Gord Dibben MS Excel MVP On Thu, 13 Dec 2007 12:43:40 -0800, "Vulcan" wrote: Sorry, forgot to mention: Office 2007 on Vista Business "Vulcan" wrote in message ... Hoping that someone can offer some advice on this.... I'm creating a complex excel sheets with lots of relative, mixed, and absolute references. Once I'm done, I'd like to copy a block within the sheet to create a new block. The trouble is that all the absolute references within the block are pointing to reference within the old block. I can fix them manually, but I want to copy the block several times, and it's a lot of work to change each one What I'd like to to do is, once things are as I want them, convert all of the Absolute references in the completed block to relative so that when I past a new block, the addressing will be correct. One trick I've found is that I can convert cells formulas from Absolute to relative one by one using the F4 key, but that's not ideal either (although, it's easier than editing each cell). Anyone know if there is a way to convert a block of cells to relative mode in one shot? Thanks for any advice. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with converting a block of cells with Absolute and mixedreferences to relative references
One way you could do it is to highlight the cells you want to change
and then use Edit | Replace (or CTRL-H) to: Find What: $ Replace With: leave blank click Replace All I don't have XL2007, so the menu options might be a bit different. Hope this helps. Pete On Dec 13, 8:43 pm, "Vulcan" wrote: Sorry, forgot to mention: Office 2007 on Vista Business "Vulcan" wrote in message ... Hoping that someone can offer some advice on this.... I'm creating a complex excel sheets with lots of relative, mixed, and absolute references. Once I'm done, I'd like to copy a block within the sheet to create a new block. The trouble is that all the absolute references within the block are pointing to reference within the old block. I can fix them manually, but I want to copy the block several times, and it's a lot of work to change each one What I'd like to to do is, once things are as I want them, convert all of the Absolute references in the completed block to relative so that when I past a new block, the addressing will be correct. One trick I've found is that I can convert cells formulas from Absolute to relative one by one using the F4 key, but that's not ideal either (although, it's easier than editing each cell). Anyone know if there is a way to convert a block of cells to relative mode in one shot? Thanks for any advice.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change a long column of references from relative to absolute | New Users to Excel | |||
Relative (vs. Absolute) Cell References with macros | Excel Discussion (Misc queries) | |||
Relative, absolute & mixed references in many cells | Excel Worksheet Functions | |||
how to switch between relative and absolute references | New Users to Excel | |||
F4 key does not toggle relative/absolute cell references. | Excel Worksheet Functions |