Adding the $ AFTER the data was collated?

Hi all

I have a spreadsheet which links to loads of others.

As all spreadsheets are the same format it was fine for the first link to be 'autofilled'. Now as i want to copy down the data it obvisouly changes the cell references...

Is there any way - short of doing it manually! - of inserting the $ for letter and number automatically?

Many thanks

J

Hi there,

Not sure I'm totally understanding your problem - but it seems you're saying you want to make the cell values absolute after you've entered them. If this is the case, you can use the following bit of VBA to 'lock' everything up after you've entered it. Just select the cells and run the macro.

Sub SetAllToAbsolute()
Dim c As Range
For Each cell In Selection
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

HTH,

SamuelT

Hi Petitboeuf,

There is a semi automatic way.
Click on the cell reference in the formula bar and hit F4,
keep hitting F4 and you will see that it cycles through
the four options for a cell reference i.e.
B2, $B2, B$2 and $B$2

If you want more automated than that then I suppose
there may be some sort of VBA solution that could be
tailored to your particular needs but you would need
to supply a lot more detailed information on your needs
and post it in the programming newsgroup.

HTH
Martin

Originally Posted by SamuelT

Hi there,

Not sure I'm totally understanding your problem - but it seems you're saying you want to make the cell values absolute after you've entered them. If this is the case, you can use the following bit of VBA to 'lock' everything up after you've entered it. Just select the cells and run the macro.

Sub SetAllToAbsolute()
Dim c As Range
For Each cell In Selection
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

HTH,

SamuelT

SamuelT,

Many thanks! It worked a treat!

Now for the next challenge!

My formula is something like "=Sheet1!a9/Sheet1!a9" which will give me a % (100% in that case).
I want to copy/paste down the formula and in order to do so would need the formula to be:
"=Sheet1!a9/Sheet1!$a$9"

What would be the tweak on the code to make this happen? I tried to play with it but my VB abilities do not allow for any results

Many thanks

Originally Posted by SamuelT

Hi there,

Not sure I'm totally understanding your problem - but it seems you're saying you want to make the cell values absolute after you've entered them. If this is the case, you can use the following bit of VBA to 'lock' everything up after you've entered it. Just select the cells and run the macro.

Sub SetAllToAbsolute()
Dim c As Range
For Each c In Selection
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

HTH,

SamuelT

SamuelT,

Many thanks! It worked a treat!

Now for the next challenge!

The formula(s) I am working on are as follow:

=IF(ISERROR('D:\[Dashboards - Accounts - SF2.xls]Dashboard - TOTAL (Inc. Bridge)'!AJ7/'D:\[Dashboards - Accounts - SF2.xls]Dashboard - TOTAL (Inc. Bridge)'!B7*100),0,'D:\[Dashboards - Accounts - SF2.xls]Dashboard - TOTAL (Inc. Bridge)'!AJ7/'D:\[Dashboards - Accounts - SF2.xls]Dashboard - TOTAL (Inc. Bridge)'!B7*100)

When I try to apply the macro to this.... I get a #Value! error...

Please help! I have a lot of sheets and cells to apply these formulas to!

Many thanks