Dollar SIgns in Formulas

I have formulas in multiple cells that have dollar signs in the formula. So
when I copy and paste the formulas won't change. However I want them to
change. So is there a way I can clear the dollar signs in multiple Cells at
once So I don't have to do one by on?

Hi
Select your range. Hit F2 and then click, in the formula bar, on the
reference you want to change. Each time you hit F4, the reference will
change through each relative and absolute option. When you see the one you
want, hit Ctrl+Enter.
Make sure you take a copy of the sheet before you start.

--
Andy.


"Mascot" <Mascot@discussions.microsoft.com> wrote in message
news:77C5C90D-C3E9-449E-ACBF-79237F195F9F@microsoft.com...
>I have formulas in multiple cells that have dollar signs in the formula.
>So
> when I copy and paste the formulas won't change. However I want them to
> change. So is there a way I can clear the dollar signs in multiple Cells
> at
> once So I don't have to do one by on?

Select them all and goto Format>Cell>number and set without dollars.

--
HTH

Bob Phillips

"Mascot" <Mascot@discussions.microsoft.com> wrote in message
news:77C5C90D-C3E9-449E-ACBF-79237F195F9F@microsoft.com...
> I have formulas in multiple cells that have dollar signs in the formula.
So
> when I copy and paste the formulas won't change. However I want them to
> change. So is there a way I can clear the dollar signs in multiple Cells
at
> once So I don't have to do one by on?

Mascot,

Select your cells, and run the macro below. And, yes, I know that Intersect
command looks weird, but Excel will select all cells with formulas if you
don't do it that way with a single cell selected.....

HTH,
Bernie
MS Excel MVP

Sub RemoveAbsoluteReferences()
Dim myCell As Range

For Each myCell In Intersect(Selection, Selection. _
SpecialCells(xlCellTypeFormulas))
myCell.Formula = Application.ConvertFormula( _
myCell.Formula, xlA1, xlA1, xlRelative)
Next myCell

End Sub




"Mascot" <Mascot@discussions.microsoft.com> wrote in message
news:77C5C90D-C3E9-449E-ACBF-79237F195F9F@microsoft.com...
> I have formulas in multiple cells that have dollar signs in the formula.
So
> when I copy and paste the formulas won't change. However I want them to
> change. So is there a way I can clear the dollar signs in multiple Cells
at
> once So I don't have to do one by on?

Hi Bernie,

Thanks for the help this macro works perfectly. You saved me a lot of time.

Mascot

"Bernie Deitrick" wrote:

> Mascot,
>
> Select your cells, and run the macro below. And, yes, I know that Intersect
> command looks weird, but Excel will select all cells with formulas if you
> don't do it that way with a single cell selected.....
>
> HTH,
> Bernie
> MS Excel MVP
>
> Sub RemoveAbsoluteReferences()
> Dim myCell As Range
>
> For Each myCell In Intersect(Selection, Selection. _
> SpecialCells(xlCellTypeFormulas))
> myCell.Formula = Application.ConvertFormula( _
> myCell.Formula, xlA1, xlA1, xlRelative)
> Next myCell
>
> End Sub
>
>
>
>
> "Mascot" <Mascot@discussions.microsoft.com> wrote in message
> news:77C5C90D-C3E9-449E-ACBF-79237F195F9F@microsoft.com...
> > I have formulas in multiple cells that have dollar signs in the formula.
> So
> > when I copy and paste the formulas won't change. However I want them to
> > change. So is there a way I can clear the dollar signs in multiple Cells
> at
> > once So I don't have to do one by on?
>
>
>