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


--
Petitboeuf
------------------------------------------------------------------------
Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602
View this thread: http://www.excelforum.com/showthread...hreadid=562366

Adding the $ AFTER the data was collated?
 

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
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=562366

Adding the $ AFTER the data was collated?
 

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

Adding the $ AFTER the data was collated?
 

SamuelT Wrote:
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! :rolleyes:

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


--
Petitboeuf
------------------------------------------------------------------------
Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602
View this thread: http://www.excelforum.com/showthread...hreadid=562366

Adding the $ AFTER the data was collated?
 

SamuelT Wrote:
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! :rolleyes:

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


--
Petitboeuf
------------------------------------------------------------------------
Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602
View this thread: http://www.excelforum.com/showthread...hreadid=562366

Adding the $ AFTER the data was collated?
 

Petitboeuf Wrote:
SamuelT,

Many thanks! It worked a treat! :)

Now for the next challenge! :rolleyes:

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

try this, it worked for me:

Sub SetPartToAbsolute()
Dim c As Range
Dim arSplit As Variant
Dim Numer, Denom As String
For Each c In Selection
If c.HasFormula Then
arSplit = Split(c.Formula, "/")
c.Formula = arSplit(0) & "/" & Application.ConvertFormula(arSplit(1),
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub


--
FrankB
------------------------------------------------------------------------
FrankB's Profile: http://www.excelforum.com/member.php...o&userid=18952
View this thread: http://www.excelforum.com/showthread...hreadid=562366