| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modify to eliminate #NUM! error
The following formula works fine however it returns #NUM! when there are no more records to display. I do not want the error to return. =INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C $500,ROWS($IV$1:IV1)),$C$1:$C$500,0)) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Modify to eliminate #NUM! error
=IF(ISERROR(INDEX(INDIRECT("$a$1:$a$500"),MATCH(SM ALL($C$1:$C$500,ROWS($IV$1:IV1)),$C$1:$C$500,0))), "",INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1: $C$500,ROWS($IV$1:IV1)),$C$1:$C$500,0)))
Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Gotroots" wrote: The following formula works fine however it returns #NUM! when there are no more records to display. I do not want the error to return. =INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C $500,ROWS($IV$1:IV1)),$C$1:$C$500,0)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Modify to eliminate #NUM! error
That is one heck of a formula! It does what it says on the tin :) Thank you (Ms-Exl-Learner) "Ms-Exl-Learner" wrote: =IF(ISERROR(INDEX(INDIRECT("$a$1:$a$500"),MATCH(SM ALL($C$1:$C$500,ROWS($IV$1:IV1)),$C$1:$C$500,0))), "",INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1: $C$500,ROWS($IV$1:IV1)),$C$1:$C$500,0))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Gotroots" wrote: The following formula works fine however it returns #NUM! when there are no more records to display. I do not want the error to return. =INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C $500,ROWS($IV$1:IV1)),$C$1:$C$500,0)) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Modify to eliminate #NUM! error
it returns #NUM! when there are no more records to display.
If the formula returns #NUM! it can only come from this: SMALL(C1:C500,ROWS(IV$1:IV1)) So, you only need to trap that portion of the formula. -- Biff Microsoft Excel MVP "Ms-Exl-Learner" wrote in message ... =IF(ISERROR(INDEX(INDIRECT("$a$1:$a$500"),MATCH(SM ALL($C$1:$C$500,ROWS($IV$1:IV1)),$C$1:$C$500,0))), "",INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1: $C$500,ROWS($IV$1:IV1)),$C$1:$C$500,0))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Gotroots" wrote: The following formula works fine however it returns #NUM! when there are no more records to display. I do not want the error to return. =INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C $500,ROWS($IV$1:IV1)),$C$1:$C$500,0)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Modify to eliminate #NUM! error
The usual way of avoiding such errors being displayed is
=IF(ISERROR(yourformula),"",yourformula), hence try =IF(ISERROR(INDEX(INDIRECT("$a$1:$a$500"),MATCH(SM ALL($C$1:$C$500,ROWS($IV$1:IV1)),$C$1:$C$500,0))), "",INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1: $C$500,ROWS($IV$1:IV1)),$C$1:$C$500,0))) -- David Biddulph "Gotroots" wrote in message ... The following formula works fine however it returns #NUM! when there are no more records to display. I do not want the error to return. =INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C $500,ROWS($IV$1:IV1)),$C$1:$C$500,0)) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Modify to eliminate #NUM! error
Depends on what you have in ColA and colC
If ColC is having numerics...try =IF(COUNT($C$1:$C$500)<ROWS($IV$1:IV1),"",(INDEX(I NDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C$500,ROW S($IV$1:IV1)),$C$1:$C$500,0)))) 'try if colC will have both text and numerics =IF(COUNTA($C$1:$C$500)<ROWS($IV$1:IV1),"",(INDEX( INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C$500,RO WS($IV$1:IV1)),$C$1:$C$500,0)))) -- Jacob "Gotroots" wrote: The following formula works fine however it returns #NUM! when there are no more records to display. I do not want the error to return. =INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C $500,ROWS($IV$1:IV1)),$C$1:$C$500,0)) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Modify to eliminate #NUM! error
Incase you are using XL2007 checkout help on the function IFERROR()
-- Jacob "Jacob Skaria" wrote: Depends on what you have in ColA and colC If ColC is having numerics...try =IF(COUNT($C$1:$C$500)<ROWS($IV$1:IV1),"",(INDEX(I NDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C$500,ROW S($IV$1:IV1)),$C$1:$C$500,0)))) 'try if colC will have both text and numerics =IF(COUNTA($C$1:$C$500)<ROWS($IV$1:IV1),"",(INDEX( INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C$500,RO WS($IV$1:IV1)),$C$1:$C$500,0)))) -- Jacob "Gotroots" wrote: The following formula works fine however it returns #NUM! when there are no more records to display. I do not want the error to return. =INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C $500,ROWS($IV$1:IV1)),$C$1:$C$500,0)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to eliminate 0's | Excel Discussion (Misc queries) | |||
| when copying from one spreadsheet to another I get "#VALUE!" error,how can I eliminate it? | Excel Discussion (Misc queries) | |||
| eliminate zero | Excel Worksheet Functions | |||
| How to eliminate #REF | Excel Discussion (Misc queries) | |||
| How do I eliminate, in Excel XP, "too many cell formats" error? | Excel Discussion (Misc queries) | |||
Hybrid Mode
