If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Zero values in a log chart | Joelle_Smith | Charts and Charting | 1 | December 23rd, 2004 07:58 PM |
Average 10 lowest values in range | JACKPOT | Worksheet Functions | 9 | May 24th, 2004 06:58 PM |
average, eliminating zero values | Chris | Worksheet Functions | 6 | May 14th, 2004 11:36 PM |
Look up values in one column based on values in another without repeating | fbarbie | Worksheet Functions | 0 | April 8th, 2004 09:38 PM |
Conditional sum based on values in one or more columns | Mario | Worksheet Functions | 13 | December 23rd, 2003 09:38 PM |