Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... =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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... =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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Absolute referencing | Excel Discussion (Misc queries) | |||
excel chart - absolute referencing????! | Charts and Charting in Excel | |||
Relative vs Absolute referencing of Workbooks | Excel Worksheet Functions | |||
Relative vs Absolute referencing of Workbooks | Charts and Charting in Excel | |||
Creating a formula using absolute referencing - I think!! | Excel Worksheet Functions |