Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
change a long column of references from relative to absolute ams228 New Users to Excel 1 January 17th 07 11:17 PM
Relative (vs. Absolute) Cell References with macros Claude S. Weiller Excel Discussion (Misc queries) 1 August 3rd 06 08:56 PM
Relative, absolute & mixed references in many cells FTM Excel Worksheet Functions 11 June 19th 06 09:38 AM
how to switch between relative and absolute references KeKe New Users to Excel 2 June 9th 06 05:23 PM
F4 key does not toggle relative/absolute cell references. java Excel Worksheet Functions 2 July 12th 05 09:53 PM


All times are GMT +1. The time now is 12:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"