|
| 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 |
|
#21
September 11th, 2009, 10:24 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I remove the #div or N/A and make it 0 when using VLook
Post the formula that's giving you the #N/A.
-- Biff Microsoft Excel MVP "Flcnmech" wrote in message news  Woof, okay... I see where my current equation goes, but what goes in the parenthesis behind the first MATCH? and the quote marks? "T. Valko" wrote: One way... =IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...))) -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and obviously if there is no data for it to pull I get the #N/A. How do I hide it if there is no data with this type of equation? Thanks, 
|
|
#22
September 15th, 2009, 09:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I remove the #div or N/A and make it 0 when using VLook
It is as follows:
{=INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'FIELD'$E $31,CEMS!$B$2;$B$600&CEMS!$D$2:$D$600,0))} Sorry it took me a while to get back to this. "T. Valko" wrote: Post the formula that's giving you the #N/A. -- Biff Microsoft Excel MVP "Flcnmech" wrote in message news Woof, okay... I see where my current equation goes, but what goes in the parenthesis behind the first MATCH? and the quote marks? "T. Valko" wrote: One way... =IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...))) -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and obviously if there is no data for it to pull I get the #N/A. How do I hide it if there is no data with this type of equation? Thanks,
|
|
#23
September 15th, 2009, 09:42 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I remove the #div or N/A and make it 0 when using VLook
Try one of these (both still array entered):
=IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0))) If you're using Excel 2007: =IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ") -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... It is as follows: {=INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'FIELD'$E $31,CEMS!$B$2;$B$600&CEMS!$D$2:$D$600,0))} Sorry it took me a while to get back to this. "T. Valko" wrote: Post the formula that's giving you the #N/A. -- Biff Microsoft Excel MVP "Flcnmech" wrote in message news Woof, okay... I see where my current equation goes, but what goes in the parenthesis behind the first MATCH? and the quote marks? "T. Valko" wrote: One way... =IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...))) -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and obviously if there is no data for it to pull I get the #N/A. How do I hide it if there is no data with this type of equation? Thanks,
|
|
#24
September 19th, 2009, 10:43 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I remove the #div or N/A and make it 0 when using VLook
Both worked superbly... Think I'll stick with the second one though.
Thank you very much. "T. Valko" wrote: Try one of these (both still array entered): =IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0))) If you're using Excel 2007: =IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ") -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... It is as follows: {=INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'FIELD'$E $31,CEMS!$B$2;$B$600&CEMS!$D$2:$D$600,0))} Sorry it took me a while to get back to this. "T. Valko" wrote: Post the formula that's giving you the #N/A. -- Biff Microsoft Excel MVP "Flcnmech" wrote in message news
Woof, okay... I see where my current equation goes, but what goes in the parenthesis behind the first MATCH? and the quote marks? "T. Valko" wrote: One way... =IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...))) -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and obviously if there is no data for it to pull I get the #N/A. How do I hide it if there is no data with this type of equation? Thanks,
|
|
#25
September 19th, 2009, 11:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I remove the #div or N/A and make it 0 when using VLook
The second one masks all errors, not just the NA
So be careful. Gord Dibben MS Excel MVP On Sat, 19 Sep 2009 14:43:01 -0700, Flcnmech wrote: Both worked superbly... Think I'll stick with the second one though. Thank you very much. "T. Valko" wrote: Try one of these (both still array entered): =IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0))) If you're using Excel 2007: =IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ") -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... It is as follows: {=INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'FIELD'$E $31,CEMS!$B$2;$B$600&CEMS!$D$2:$D$600,0))} Sorry it took me a while to get back to this. "T. Valko" wrote: Post the formula that's giving you the #N/A. -- Biff Microsoft Excel MVP "Flcnmech" wrote in message news
Woof, okay... I see where my current equation goes, but what goes in the parenthesis behind the first MATCH? and the quote marks? "T. Valko" wrote: One way... =IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...))) -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and obviously if there is no data for it to pull I get the #N/A. How do I hide it if there is no data with this type of equation? Thanks,
|
|
#26
September 20th, 2009, 02:19 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I remove the #div or N/A and make it 0 when using VLook
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... Both worked superbly... Think I'll stick with the second one though. Thank you very much. "T. Valko" wrote: Try one of these (both still array entered): =IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0))) If you're using Excel 2007: =IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ") -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... It is as follows: {=INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'FIELD'$E $31,CEMS!$B$2;$B$600&CEMS!$D$2:$D$600,0))} Sorry it took me a while to get back to this. "T. Valko" wrote: Post the formula that's giving you the #N/A. -- Biff Microsoft Excel MVP "Flcnmech" wrote in message news
Woof, okay... I see where my current equation goes, but what goes inthe parenthesis behind the first MATCH? and the quote marks? "T. Valko" wrote: One way... =IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...))) -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and obviously if there is no data for it to pull I get the #N/A. How do I hide it if there is no data with this type of equation? Thanks,
|
|
#27
February 10th, 2010, 02:16 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I remove the #div or N/A and make it 0 when using VLook
Okay... now I am in trouble... Migrated to the new office and due to the
data pull program had to save my working spreadsheet as a xlsm now the arrays don't work... What do I need to do? If I 'reset' the equation as an array equation then it pulls the data in, but it will not do it on it's own when I open the spreadsheet. Thanks in advance. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... Both worked superbly... Think I'll stick with the second one though. Thank you very much. "T. Valko" wrote: Try one of these (both still array entered): =IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0))) If you're using Excel 2007: =IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ") -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... It is as follows: {=INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'FIELD'$E $31,CEMS!$B$2;$B$600&CEMS!$D$2:$D$600,0))} Sorry it took me a while to get back to this. "T. Valko" wrote: Post the formula that's giving you the #N/A. -- Biff Microsoft Excel MVP "Flcnmech" wrote in message news
Woof, okay... I see where my current equation goes, but what goes inthe parenthesis behind the first MATCH? and the quote marks? "T. Valko" wrote: One way... =IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...))) -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and obviously if there is no data for it to pull I get the #N/A. How do I hide it if there is no data with this type of equation? Thanks,
|
|
#28
February 10th, 2010, 05:15 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I remove the #div or N/A and make it 0 when using VLook
Sorry, i was incorrect... it is now in an xlsx format not an xlsm.
"Flcnmech" wrote: Okay... now I am in trouble... Migrated to the new office and due to the data pull program had to save my working spreadsheet as a xlsm now the arrays don't work... What do I need to do? If I 'reset' the equation as an array equation then it pulls the data in, but it will not do it on it's own when I open the spreadsheet. Thanks in advance. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... Both worked superbly... Think I'll stick with the second one though. Thank you very much. "T. Valko" wrote: Try one of these (both still array entered): =IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0))) If you're using Excel 2007: =IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ") -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... It is as follows: {=INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'FIELD'$E $31,CEMS!$B$2;$B$600&CEMS!$D$2:$D$600,0))} Sorry it took me a while to get back to this. "T. Valko" wrote: Post the formula that's giving you the #N/A. -- Biff Microsoft Excel MVP "Flcnmech" wrote in message news
Woof, okay... I see where my current equation goes, but what goes inthe parenthesis behind the first MATCH? and the quote marks? "T. Valko" wrote: One way... =IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...))) -- Biff Microsoft Excel MVP "Flcnmech" wrote in message ... I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and obviously if there is no data for it to pull I get the #N/A. How do I hide it if there is no data with this type of equation? Thanks,
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Can you make a cell with a diagonal, so that you can edit the upper and lower part of the cell? | g wills | New Users | 3 | February 17th, 2005 10:23 PM |
| Make a relationship table ID Field, a drop-down? | Aaron | Database Design | 5 | September 10th, 2004 07:33 PM |
| MAKE QUICK CASH RIGHT NOW!!! 100% LEGAL, INSTRUCTIONS IN THIS POST!! | MAKE CASH NOW!!! | Setting up and Configuration | 0 | December 12th, 2003 06:36 PM |
Linear Mode
