Nested IF absolute referencing macro
 

I should have been more specific with my last post about absolut
macro's, below is the formula that i would like all cell reference
within this formula to become absolute using a macro as i have abou
2000 cells that will use this or a similar formula...any help would b
great...sorry for not being more specific damn forum noobies :eek:

Thanks Snail

=IF(LEFT('Master Roster'!$C$13,2)="RN",VLOOKUP('Maste
Roster'!$E$13,RN_shiftcode_table,3,FALSE),IF(LEFT(
'Master Roster'!$C$13,2)="EN",VLOOKUP('Maste
Roster'!$E$13,EN_shiftcode_table,3,FALSE),IF(LEFT(
'Master Roster'!$C$13,2)="PC",VLOOKUP('Maste
Roster'!$E$13,PCW_shiftcode_table,3,FALSE),IF(LEFT
('Master Roster'!$C$13,1)="D",VLOOKUP('Maste
Roster'!$E$13,DON_shiftcode_table,3,FALSE),IF(LEFT
('Master Roster'!$C$13,2)="TH",VLOOKUP('Maste
Roster'!$E$13,TH_shiftcode_table,3,FALSE),"USC"))) )

--
Message posted from http://www.ExcelForum.com

Nested IF absolute referencing macro
 

Snail,

Whilst I must admit to being confused as to why you would want to change an
absolute reference to an absolute reference, this code answers the question.
Just select all the cells and run the code

Dim cell
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, xlA1,
xlA1, xlAbsolute)
End If
Next cell


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Renegade Snail " wrote in
message ...
I should have been more specific with my last post about absolute
macro's, below is the formula that i would like all cell references
within this formula to become absolute using a macro as i have about
2000 cells that will use this or a similar formula...any help would be
great...sorry for not being more specific damn forum noobies :eek:

Thanks Snail

=IF(LEFT('Master Roster'!$C$13,2)="RN",VLOOKUP('Master
Roster'!$E$13,RN_shiftcode_table,3,FALSE),IF(LEFT(
'Master Roster'!$C$13,2)="EN",VLOOKUP('Master
Roster'!$E$13,EN_shiftcode_table,3,FALSE),IF(LEFT(
'Master Roster'!$C$13,2)="PC",VLOOKUP('Master
Roster'!$E$13,PCW_shiftcode_table,3,FALSE),IF(LEFT
('Master Roster'!$C$13,1)="D",VLOOKUP('Master
Roster'!$E$13,DON_shiftcode_table,3,FALSE),IF(LEFT
('Master Roster'!$C$13,2)="TH",VLOOKUP('Master
Roster'!$E$13,TH_shiftcode_table,3,FALSE),"USC"))) ))


---
Message posted from http://www.ExcelForum.com/

Nested IF absolute referencing macro
 

Sorry the code i was meant to post looks like this maybe that would mak
a difference to the formula, althought i have tried modifying the code
all i seem to get is a "#VALUE" insert into the cell/s i run the macr
on...:mad:

=IF(LEFT('Master Roster'!$C3,2)="RN",VLOOKUP('Maste
Roster'!$E3,RN_shiftcode_table,3,FALSE),IF(LEFT('M aste
Roster'!$C3,2)="EN",VLOOKUP('Maste
Roster'!$E3,EN_shiftcode_table,3,FALSE),IF(LEFT('M aste
Roster'!$C3,2)="PC",VLOOKUP('Maste
Roster'!$E3,PCW_shiftcode_table,3,FALSE),IF(LEFT(' Maste
Roster'!$C3,1)="D",VLOOKUP('Maste
Roster'!$E3,DON_shiftcode_table,3,FALSE),IF(LEFT(' Maste
Roster'!$C3,2)="TH",VLOOKUP('Maste
Roster'!$E3,TH_shiftcode_table,3,FALSE),"USC"))))

--
Message posted from http://www.ExcelForum.com

Nested IF absolute referencing macro
 

No the code should be irrelevant. If it was all already absolute, my code
should not change anything.

Post back what the formula looked like after running the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Renegade Snail " wrote in
message ...
Sorry the code i was meant to post looks like this maybe that would make
a difference to the formula, althought i have tried modifying the code,
all i seem to get is a "#VALUE" insert into the cell/s i run the macro
on...:mad:

=IF(LEFT('Master Roster'!$C3,2)="RN",VLOOKUP('Master
Roster'!$E3,RN_shiftcode_table,3,FALSE),IF(LEFT('M aster
Roster'!$C3,2)="EN",VLOOKUP('Master
Roster'!$E3,EN_shiftcode_table,3,FALSE),IF(LEFT('M aster
Roster'!$C3,2)="PC",VLOOKUP('Master
Roster'!$E3,PCW_shiftcode_table,3,FALSE),IF(LEFT(' Master
Roster'!$C3,1)="D",VLOOKUP('Master
Roster'!$E3,DON_shiftcode_table,3,FALSE),IF(LEFT(' Master
Roster'!$C3,2)="TH",VLOOKUP('Master
Roster'!$E3,TH_shiftcode_table,3,FALSE),"USC")))))


---
Message posted from http://www.ExcelForum.com/

Nested IF absolute referencing macro
 

When i run the macro the formula is deleted and the cell contents ar
replaced with this text:

"#VALUE!"

hope that help

--
Message posted from http://www.ExcelForum.com

Nested IF absolute referencing macro
 

Confused? I am. Why not send me a copy of the workbook directly.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Renegade Snail " wrote in
message ...
When i run the macro the formula is deleted and the cell contents are
replaced with this text:

"#VALUE!"

hope that helps


---
Message posted from http://www.ExcelForum.com/