I'm trying to copy formulas down a column of many rows and I get the exact
formula in each copied row. I want the copied rows to refer to sequential
rows as F1, F2, F3, etc. What I get is every row refers to F1 which is the
original copy row.
Is this possible. TYIA
Rich
Hi!
What's the formula look like?
I'll bet there's a reference to F1 that looks like this: F$1 or $F$1. Remove
the $ before the 1 so that you have either: F1 or $F1.
Biff
"arja" <arja@discussions.microsoft.com> wrote in message
news:B1CA4776-D796-4E14-BC96-3E00CA9564CF@microsoft.com...
> I'm trying to copy formulas down a column of many rows and I get the exact
> formula in each copied row. I want the copied rows to refer to sequential
> rows as F1, F2, F3, etc. What I get is every row refers to F1 which is
> the
> original copy row.
> Is this possible. TYIA
>
> Rich
Rich
Sounds like Manual Calculation is set.
Go to Tools>Options>Calculation and set to "Automatic".
Alternative..........the cell refs have been made Absolute by placing $ signs
like
=$F$1
Gord Dibben MS Excel MVP
On Thu, 2 Mar 2006 13:38:27 -0800, "arja" <arja@discussions.microsoft.com>
wrote:
>I'm trying to copy formulas down a column of many rows and I get the exact
>formula in each copied row. I want the copied rows to refer to sequential
>rows as F1, F2, F3, etc. What I get is every row refers to F1 which is the
>original copy row.
>Is this possible. TYIA
>
>Rich
Hi Biff,
Thank you very much. Hit the nail on the head and everything works fine.
Spent hours on that stupid thing. Do you know of a way to get rid of the $
sign in the first place as Gord mentioned? See his reply to my question in
my original posting. I am set on automatic in the calculation tab. I'm also
going to ask him. Thanks again for a great response.
Rich
"Biff" wrote:
> Hi!
>
> What's the formula look like?
>
> I'll bet there's a reference to F1 that looks like this: F$1 or $F$1. Remove
> the $ before the 1 so that you have either: F1 or $F1.
>
> Biff
>
> "arja" <arja@discussions.microsoft.com> wrote in message
> news:B1CA4776-D796-4E14-BC96-3E00CA9564CF@microsoft.com...
> > I'm trying to copy formulas down a column of many rows and I get the exact
> > formula in each copied row. I want the copied rows to refer to sequential
> > rows as F1, F2, F3, etc. What I get is every row refers to F1 which is
> > the
> > original copy row.
> > Is this possible. TYIA
> >
> > Rich
>
>
>
Hi Gord,
Thank you very much for your help. The $ sign was the answer previously
provided by Biff was the answer. You hit on it too. I am set on
"automatic". Do you know how I can get rid of the $? I don't know how to
change the cell refs from "absolute"
Thank you again for your fast response to my problem.
Rich
"Gord Dibben" wrote:
> Rich
>
> Sounds like Manual Calculation is set.
>
> Go to Tools>Options>Calculation and set to "Automatic".
>
> Alternative..........the cell refs have been made Absolute by placing $ signs
> like
>
> =$F$1
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 2 Mar 2006 13:38:27 -0800, "arja" <arja@discussions.microsoft.com>
> wrote:
>
> >I'm trying to copy formulas down a column of many rows and I get the exact
> >formula in each copied row. I want the copied rows to refer to sequential
> >rows as F1, F2, F3, etc. What I get is every row refers to F1 which is the
> >original copy row.
> >Is this possible. TYIA
> >
> >Rich
>
>
Rich
Manually you can select each cell, select the cell refs and cycle through the
combinations using the F4 function key.
OR use a macro to do all cells at the click of a button. Below are four macros.
The Sub Relative() is the one you want for your particular problem. Just select
the range and run the macro.
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)
End If
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)
End If
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)
End If
Next
End Sub
Gord
On Fri, 3 Mar 2006 08:36:10 -0800, arja <arja@discussions.microsoft.com> wrote:
>Hi Gord,
>
>Thank you very much for your help. The $ sign was the answer previously
>provided by Biff was the answer. You hit on it too. I am set on
>"automatic". Do you know how I can get rid of the $? I don't know how to
>change the cell refs from "absolute"
>
>Thank you again for your fast response to my problem.
>
>Rich
>
>"Gord Dibben" wrote:
>
>> Rich
>>
>> Sounds like Manual Calculation is set.
>>
>> Go to Tools>Options>Calculation and set to "Automatic".
>>
>> Alternative..........the cell refs have been made Absolute by placing $ signs
>> like
>>
>> =$F$1
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Thu, 2 Mar 2006 13:38:27 -0800, "arja" <arja@discussions.microsoft.com>
>> wrote:
>>
>> >I'm trying to copy formulas down a column of many rows and I get the exact
>> >formula in each copied row. I want the copied rows to refer to sequential
>> >rows as F1, F2, F3, etc. What I get is every row refers to F1 which is the
>> >original copy row.
>> >Is this possible. TYIA
>> >
>> >Rich
>>
>>
Gord Dibben MS Excel MVP
Gord,
Thanks again. Don't know if I can handle this but I'll give it a try.
You've been very kind with your time.
All the best,
Rich
"Gord Dibben" wrote:
> Rich
>
> Manually you can select each cell, select the cell refs and cycle through the
> combinations using the F4 function key.
>
> OR use a macro to do all cells at the click of a button. Below are four macros.
>
> The Sub Relative() is the one you want for your particular problem. Just select
> the range and run the macro.
>
> 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)
> End If
> 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)
> End If
> 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)
> End If
> Next
> End Sub
>
>
> Gord
>
> On Fri, 3 Mar 2006 08:36:10 -0800, arja <arja@discussions.microsoft.com> wrote:
>
> >Hi Gord,
> >
> >Thank you very much for your help. The $ sign was the answer previously
> >provided by Biff was the answer. You hit on it too. I am set on
> >"automatic". Do you know how I can get rid of the $? I don't know how to
> >change the cell refs from "absolute"
> >
> >Thank you again for your fast response to my problem.
> >
> >Rich
> >
> >"Gord Dibben" wrote:
> >
> >> Rich
> >>
> >> Sounds like Manual Calculation is set.
> >>
> >> Go to Tools>Options>Calculation and set to "Automatic".
> >>
> >> Alternative..........the cell refs have been made Absolute by placing $ signs
> >> like
> >>
> >> =$F$1
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >> On Thu, 2 Mar 2006 13:38:27 -0800, "arja" <arja@discussions.microsoft.com>
> >> wrote:
> >>
> >> >I'm trying to copy formulas down a column of many rows and I get the exact
> >> >formula in each copied row. I want the copied rows to refer to sequential
> >> >rows as F1, F2, F3, etc. What I get is every row refers to F1 which is the
> >> >original copy row.
> >> >Is this possible. TYIA
> >> >
> >> >Rich
> >>
> >>
>
> Gord Dibben MS Excel MVP
>
Gord, wouldn't it work to simply select the cells with the $ to be eliminated
and then Edit - Replace the $ with nothing?
--
Sincerely, Michael Colvin
"Gord Dibben" wrote:
> Rich
>
> Manually you can select each cell, select the cell refs and cycle through the
> combinations using the F4 function key.
>
> OR use a macro to do all cells at the click of a button. Below are four macros.
>
> The Sub Relative() is the one you want for your particular problem. Just select
> the range and run the macro.
>
> 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)
> End If
> 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)
> End If
> 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)
> End If
> Next
> End Sub
>
>
> Gord
>
> On Fri, 3 Mar 2006 08:36:10 -0800, arja <arja@discussions.microsoft.com> wrote:
>
> >Hi Gord,
> >
> >Thank you very much for your help. The $ sign was the answer previously
> >provided by Biff was the answer. You hit on it too. I am set on
> >"automatic". Do you know how I can get rid of the $? I don't know how to
> >change the cell refs from "absolute"
> >
> >Thank you again for your fast response to my problem.
> >
> >Rich
> >
> >"Gord Dibben" wrote:
> >
> >> Rich
> >>
> >> Sounds like Manual Calculation is set.
> >>
> >> Go to Tools>Options>Calculation and set to "Automatic".
> >>
> >> Alternative..........the cell refs have been made Absolute by placing $ signs
> >> like
> >>
> >> =$F$1
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >> On Thu, 2 Mar 2006 13:38:27 -0800, "arja" <arja@discussions.microsoft.com>
> >> wrote:
> >>
> >> >I'm trying to copy formulas down a column of many rows and I get the exact
> >> >formula in each copied row. I want the copied rows to refer to sequential
> >> >rows as F1, F2, F3, etc. What I get is every row refers to F1 which is the
> >> >original copy row.
> >> >Is this possible. TYIA
> >> >
> >> >Rich
> >>
> >>
>
> Gord Dibben MS Excel MVP
>
What if the formulas have absolute references: $F$1, $AA$100
And you only want to make the row reference relative?
Depending on how "good" you are with Edit/Replace, you may end up replacing
ALL the $ when you only wanted to replace the $ for the rows.
I guess it boils down to ones personal preferences and experience level.
Personally, I use the F4 key.
Biff
"Michael" <Michael@discussions.microsoft.com> wrote in message
news:AD1A0AE1-3630-4973-A3B2-8FA82B01152C@microsoft.com...
> Gord, wouldn't it work to simply select the cells with the $ to be
> eliminated
> and then Edit - Replace the $ with nothing?
> --
> Sincerely, Michael Colvin
>
>
> "Gord Dibben" wrote:
>
>> Rich
>>
>> Manually you can select each cell, select the cell refs and cycle through
>> the
>> combinations using the F4 function key.
>>
>> OR use a macro to do all cells at the click of a button. Below are four
>> macros.
>>
>> The Sub Relative() is the one you want for your particular problem. Just
>> select
>> the range and run the macro.
>>
>> 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)
>> End If
>> 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)
>> End If
>> 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)
>> End If
>> Next
>> End Sub
>>
>>
>> Gord
>>
>> On Fri, 3 Mar 2006 08:36:10 -0800, arja <arja@discussions.microsoft.com>
>> wrote:
>>
>> >Hi Gord,
>> >
>> >Thank you very much for your help. The $ sign was the answer previously
>> >provided by Biff was the answer. You hit on it too. I am set on
>> >"automatic". Do you know how I can get rid of the $? I don't know how
>> >to
>> >change the cell refs from "absolute"
>> >
>> >Thank you again for your fast response to my problem.
>> >
>> >Rich
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> Rich
>> >>
>> >> Sounds like Manual Calculation is set.
>> >>
>> >> Go to Tools>Options>Calculation and set to "Automatic".
>> >>
>> >> Alternative..........the cell refs have been made Absolute by placing
>> >> $ signs
>> >> like
>> >>
>> >> =$F$1
>> >>
>> >>
>> >> Gord Dibben MS Excel MVP
>> >>
>> >> On Thu, 2 Mar 2006 13:38:27 -0800, "arja"
>> >> <arja@discussions.microsoft.com>
>> >> wrote:
>> >>
>> >> >I'm trying to copy formulas down a column of many rows and I get the
>> >> >exact
>> >> >formula in each copied row. I want the copied rows to refer to
>> >> >sequential
>> >> >rows as F1, F2, F3, etc. What I get is every row refers to F1 which
>> >> >is the
>> >> >original copy row.
>> >> >Is this possible. TYIA
>> >> >
>> >> >Rich
>> >>
>> >>
>>
>> Gord Dibben MS Excel MVP
>>
The easiest way (if you already haven't figured it out) is to do ctrl H (find
and replace $ with nothing)
"arja" wrote:
> Hi Biff,
>
> Thank you very much. Hit the nail on the head and everything works fine.
> Spent hours on that stupid thing. Do you know of a way to get rid of the $
> sign in the first place as Gord mentioned? See his reply to my question in
> my original posting. I am set on automatic in the calculation tab. I'm also
> going to ask him. Thanks again for a great response.
>
> Rich
>
>
> "Biff" wrote:
>
> > Hi!
> >
> > What's the formula look like?
> >
> > I'll bet there's a reference to F1 that looks like this: F$1 or $F$1. Remove
> > the $ before the 1 so that you have either: F1 or $F1.
> >
> > Biff
> >
> > "arja" <arja@discussions.microsoft.com> wrote in message
> > news:B1CA4776-D796-4E14-BC96-3E00CA9564CF@microsoft.com...
> > > I'm trying to copy formulas down a column of many rows and I get the exact
> > > formula in each copied row. I want the copied rows to refer to sequential
> > > rows as F1, F2, F3, etc. What I get is every row refers to F1 which is
> > > the
> > > original copy row.
> > > Is this possible. TYIA
> > >
> > > Rich
> >
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks