How To Suppres #N/A Values
I've finally worked out the formula to work, which is below
=IF(ISNA(MATCH(A2,StaffNo,0)),"Crew",INDEX(StaffGr ade,MATCH(A2,StaffNo,0)))" However I am now trying to place the formula into some code so that it will copy down, my formula is now what is below but I'm getting a Epected End Statement ActiveCell.Formula = "=IF(ISNA(MATCH(A2,StaffNo,0)),"Crew",INDEX(StaffG rade,MATCH(A2,StaffNo,0))) " "John" wrote in message ... I have the following formula which returns the value Staff or Mgr, depending on what each persons StaffNo is attributed under StaffGrade =INDEX(StaffGrade,MATCH(A6,StaffNo,0)) My problem is that if the Staff number in my column does not exist ithin StaffNo the formula above returns a #N/A, I want a work around this that if the StaffNo does not exists that the value returned will be Staff - is that possible? Thanks |
John,
Your might Also find this useful: =CALC(INDEX(StaffGrade,MATCH(A2,StaffNo,0)),"Crew" ) ' Short & Fast: CALC(Expected, If_Error) Function Calc(Expected, Optional If_Error) Calc = Expected If IsError(Expected) Then Calc = If_Error End Function Ola |
Presumably the quotes. Try
ActiveCell.Formula = "=IF(ISNA(MATCH(A2,StaffNo,0)),""Crew"",INDEX(Staf fGrade,MATCH(A2,StaffNo,0) ))" -- HTH Bob Phillips "John" wrote in message ... I've finally worked out the formula to work, which is below =IF(ISNA(MATCH(A2,StaffNo,0)),"Crew",INDEX(StaffGr ade,MATCH(A2,StaffNo,0)))" However I am now trying to place the formula into some code so that it will copy down, my formula is now what is below but I'm getting a Epected End Statement ActiveCell.Formula = "=IF(ISNA(MATCH(A2,StaffNo,0)),"Crew",INDEX(StaffG rade,MATCH(A2,StaffNo,0))) " "John" wrote in message ... I have the following formula which returns the value Staff or Mgr, depending on what each persons StaffNo is attributed under StaffGrade =INDEX(StaffGrade,MATCH(A6,StaffNo,0)) My problem is that if the Staff number in my column does not exist ithin StaffNo the formula above returns a #N/A, I want a work around this that if the StaffNo does not exists that the value returned will be Staff - is that possible? Thanks |
All times are GMT +1. The time now is 12:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com