|
|
I have a large block of cells set up that I now find I need to move. The
cells were originally set up as relative since I needed to do things like
fill down & right, etc. But now I find I need to move that entire block of
cells to another location, and of course, now the cells are referencing to
cells that don't exist. I need to change the entire block before moving and
I don't see any way to do that for a large set of cells.
Thanks again for any help
Conf.
|
|
If planned properly, both relative and absolute reference normally move
without problem. Possibly you are making the whole cell reference absolute
rather than just a row or column (eg using $A$1 when you only need $A1). The
follow reference may help you understand how to make absolute reference to a
row or column only.
http://www.techonthenet.com/excel/questions/referencing.php
If this doesn't help, ask your question again giving examples of the formula
giving you trouble, how and where you are moving the formula and a
description of what happens when you move it.
HTH
--
Steve
"Confused" <nobody[ at ]nowhere.com> wrote in message
news:eV0vuSfaJHA.4520[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > I have a large block of cells set up that I now find I need to move. The
> cells were originally set up as relative since I needed to do things like
> fill down & right, etc. But now I find I need to move that entire block of
> cells to another location, and of course, now the cells are referencing to
> cells that don't exist. I need to change the entire block before moving
> and I don't see any way to do that for a large set of cells.
>
> Thanks again for any help
> Conf.
|
|
Select the block of cells and run the Absolute macro.
I threw the others in just in case you need any other combination.
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 Mon, 29 Dec 2008 15:33:12 -0500, "Confused" <nobody[ at ]nowhere.com> wrote:
[Quoted Text] >I have a large block of cells set up that I now find I need to move. The
>cells were originally set up as relative since I needed to do things like
>fill down & right, etc. But now I find I need to move that entire block of
>cells to another location, and of course, now the cells are referencing to
>cells that don't exist. I need to change the entire block before moving and
>I don't see any way to do that for a large set of cells.
>
>Thanks again for any help
>Conf.
|
|
That's just the problem, I didn't plan properly :-( actually, I thought I
had everything where it was supposed to be, but found later when I was
writing my lookups that I had to move entire blocks of cells from where I
thought they needed to be to where the actually needed to be for the lookup
to work. They are simple "copy to" formula that just take the scattered
information from another file and organize it into a useful grid in the new
file so that lookups can be done on them:
='[Pricing.xls]Sheet1'!AX3
='[Pricing.xls]Sheet1'!AX4
='[Pricing.xls]Sheet1'!AX5
etc
Of course, I just placed the first cell, with the AX3 reference, then did a
fill down to make the rest to a certain point, skipped a few cells from the
original file, then made a new reference and so on. The problem occurred
when I tried my lookups and found that I needed some of those blocks of
cells to be next to the last column of the first block, and tried to move
them, and the references to the first file changed to
='[Pricing.xls]Sheet1'!EX3
Just a misunderstanding of how the lookup worked and bad planning on my
part.
Conf
"AltaEgo" <Somewhere[ at ]NotHere> wrote in message
news:uQh2sCgaJHA.1336[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > If planned properly, both relative and absolute reference normally move > without problem. Possibly you are making the whole cell reference absolute > rather than just a row or column (eg using $A$1 when you only need $A1). > The follow reference may help you understand how to make absolute > reference to a row or column only. > > http://www.techonthenet.com/excel/questions/referencing.php> > If this doesn't help, ask your question again giving examples of the > formula giving you trouble, how and where you are moving the formula and a > description of what happens when you move it. > > HTH > -- > Steve > > "Confused" <nobody[ at ]nowhere.com> wrote in message > news:eV0vuSfaJHA.4520[ at ]TK2MSFTNGP06.phx.gbl... >> I have a large block of cells set up that I now find I need to move. The >> cells were originally set up as relative since I needed to do things like >> fill down & right, etc. But now I find I need to move that entire block >> of cells to another location, and of course, now the cells are >> referencing to cells that don't exist. I need to change the entire block >> before moving and I don't see any way to do that for a large set of >> cells. >> >> Thanks again for any help >> Conf. > |
|
Excellent, I thought it might need a macro, that will come in handy, thanks
a bunch.
Conf.
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:hfvil4lti8vmlacb24hb18hecvgdamv04d[ at ]4ax.com...
[Quoted Text] > Select the block of cells and run the Absolute macro.
>
> I threw the others in just in case you need any other combination.
>
> 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 Mon, 29 Dec 2008 15:33:12 -0500, "Confused" <nobody[ at ]nowhere.com> wrote:
>
>>I have a large block of cells set up that I now find I need to move. The
>>cells were originally set up as relative since I needed to do things like
>>fill down & right, etc. But now I find I need to move that entire block of
>>cells to another location, and of course, now the cells are referencing to
>>cells that don't exist. I need to change the entire block before moving
>>and
>>I don't see any way to do that for a large set of cells.
>>
>>Thanks again for any help
>>Conf.
>
|
|
You've done nothing we haven't all done at some time or other ... learned a
valuable lesson that sometimes hastier is slower!
--
Steve
"Confused" <nobody[ at ]nowhere.com> wrote in message
news:#CBGyIpaJHA.1328[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > That's just the problem, I didn't plan properly
|
|
|