| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Eliminate the #VALUE! response.
I have a vlookup formula where I am looking at three different warehouses to
locate the amount of material that I have on order. 1 or 2 of the spreadsheets will sometimes not have the same sku in the warehouse. I assume this is why I am getting the #VAULE! Response. I have copied the formula that I am using. How can I correct it so that I can get the quantities when there are some, but not have the #VALUE response show at all. I would prefer that a blank shows instead. =IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)) Thanks in advance -- inthestands |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Eliminate the #VALUE! response.
If you change your ISNA to ISERROR, that traps all errors
-- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog (non tech): www.nickhodge.co.uk/blog "inthestands" wrote in message ... I have a vlookup formula where I am looking at three different warehouses to locate the amount of material that I have on order. 1 or 2 of the spreadsheets will sometimes not have the same sku in the warehouse. I assume this is why I am getting the #VAULE! Response. I have copied the formula that I am using. How can I correct it so that I can get the quantities when there are some, but not have the #VALUE response show at all. I would prefer that a blank shows instead. =IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)) Thanks in advance -- inthestands |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Eliminate the #VALUE! response.
use IsErr like below, IsErr ignores the ISNA so that you can use both ISNA
and ISERR, just in case you want to keep the ISNA. =if(iserr(IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)),"",IF(ISNA(VLOOKUP(B2,'da ta sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0))) "inthestands" wrote: I have a vlookup formula where I am looking at three different warehouses to locate the amount of material that I have on order. 1 or 2 of the spreadsheets will sometimes not have the same sku in the warehouse. I assume this is why I am getting the #VAULE! Response. I have copied the formula that I am using. How can I correct it so that I can get the quantities when there are some, but not have the #VALUE response show at all. I would prefer that a blank shows instead. =IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)) Thanks in advance -- inthestands |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Eliminate the #VALUE! response.
The problem is that you're adding the results of all the lookups together.
If any return "" that causes the #VALUE! error. You could return 0 instead of "" and that'll correct the #VALUE! error. Then, if you don't want to see the result as 0 you could use conditional formatting to hide the 0. -- Biff Microsoft Excel MVP "inthestands" wrote in message ... I have a vlookup formula where I am looking at three different warehouses to locate the amount of material that I have on order. 1 or 2 of the spreadsheets will sometimes not have the same sku in the warehouse. I assume this is why I am getting the #VAULE! Response. I have copied the formula that I am using. How can I correct it so that I can get the quantities when there are some, but not have the #VALUE response show at all. I would prefer that a blank shows instead. =IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)) Thanks in advance -- inthestands |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Eliminate the #VALUE! response.
Good Point T Valko.
"T. Valko" wrote: The problem is that you're adding the results of all the lookups together. If any return "" that causes the #VALUE! error. You could return 0 instead of "" and that'll correct the #VALUE! error. Then, if you don't want to see the result as 0 you could use conditional formatting to hide the 0. -- Biff Microsoft Excel MVP "inthestands" wrote in message ... I have a vlookup formula where I am looking at three different warehouses to locate the amount of material that I have on order. 1 or 2 of the spreadsheets will sometimes not have the same sku in the warehouse. I assume this is why I am getting the #VAULE! Response. I have copied the formula that I am using. How can I correct it so that I can get the quantities when there are some, but not have the #VALUE response show at all. I would prefer that a blank shows instead. =IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)) Thanks in advance -- inthestands |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Eliminate the #VALUE! response.
Good Point T Valko.
"T. Valko" wrote: The problem is that you're adding the results of all the lookups together. If any return "" that causes the #VALUE! error. You could return 0 instead of "" and that'll correct the #VALUE! error. Then, if you don't want to see the result as 0 you could use conditional formatting to hide the 0. -- Biff Microsoft Excel MVP "inthestands" wrote in message ... I have a vlookup formula where I am looking at three different warehouses to locate the amount of material that I have on order. 1 or 2 of the spreadsheets will sometimes not have the same sku in the warehouse. I assume this is why I am getting the #VAULE! Response. I have copied the formula that I am using. How can I correct it so that I can get the quantities when there are some, but not have the #VALUE response show at all. I would prefer that a blank shows instead. =IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)) Thanks in advance -- inthestands |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Eliminate the #VALUE! response.
If you change your ISNA to ISERROR, that traps all errors
-- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog (non tech): www.nickhodge.co.uk/blog "inthestands" wrote in message ... I have a vlookup formula where I am looking at three different warehouses to locate the amount of material that I have on order. 1 or 2 of the spreadsheets will sometimes not have the same sku in the warehouse. I assume this is why I am getting the #VAULE! Response. I have copied the formula that I am using. How can I correct it so that I can get the quantities when there are some, but not have the #VALUE response show at all. I would prefer that a blank shows instead. =IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)) Thanks in advance -- inthestands |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Eliminate the #VALUE! response.
use IsErr like below, IsErr ignores the ISNA so that you can use both ISNA
and ISERR, just in case you want to keep the ISNA. =if(iserr(IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)),"",IF(ISNA(VLOOKUP(B2,'da ta sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0))) "inthestands" wrote: I have a vlookup formula where I am looking at three different warehouses to locate the amount of material that I have on order. 1 or 2 of the spreadsheets will sometimes not have the same sku in the warehouse. I assume this is why I am getting the #VAULE! Response. I have copied the formula that I am using. How can I correct it so that I can get the quantities when there are some, but not have the #VALUE response show at all. I would prefer that a blank shows instead. =IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)) Thanks in advance -- inthestands |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Eliminate the #VALUE! response.
The problem is that you're adding the results of all the lookups together.
If any return "" that causes the #VALUE! error. You could return 0 instead of "" and that'll correct the #VALUE! error. Then, if you don't want to see the result as 0 you could use conditional formatting to hide the 0. -- Biff Microsoft Excel MVP "inthestands" wrote in message ... I have a vlookup formula where I am looking at three different warehouses to locate the amount of material that I have on order. 1 or 2 of the spreadsheets will sometimes not have the same sku in the warehouse. I assume this is why I am getting the #VAULE! Response. I have copied the formula that I am using. How can I correct it so that I can get the quantities when there are some, but not have the #VALUE response show at all. I would prefer that a blank shows instead. =IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)) Thanks in advance -- inthestands |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Eliminate the #VALUE! response.
Thank you for the 0 vs the "". It did the trick. And thanks to the other
responders for their advice. It is very appreciated. -- inthestands "inthestands" wrote: I have a vlookup formula where I am looking at three different warehouses to locate the amount of material that I have on order. 1 or 2 of the spreadsheets will sometimes not have the same sku in the warehouse. I assume this is why I am getting the #VAULE! Response. I have copied the formula that I am using. How can I correct it so that I can get the quantities when there are some, but not have the #VALUE response show at all. I would prefer that a blank shows instead. =IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)) Thanks in advance -- inthestands |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Eliminate the #VALUE! response.
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "inthestands" wrote in message ... Thank you for the 0 vs the "". It did the trick. And thanks to the other responders for their advice. It is very appreciated. -- inthestands "inthestands" wrote: I have a vlookup formula where I am looking at three different warehouses to locate the amount of material that I have on order. 1 or 2 of the spreadsheets will sometimes not have the same sku in the warehouse. I assume this is why I am getting the #VAULE! Response. I have copied the formula that I am using. How can I correct it so that I can get the quantities when there are some, but not have the #VALUE response show at all. I would prefer that a blank shows instead. =IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)) Thanks in advance -- inthestands |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| value response | Excel Discussion (Misc queries) | |||
| Formula so that response is value | Excel Discussion (Misc queries) | |||
| polar response | Excel Discussion (Misc queries) | |||
| Convert a Yes/No/NA response to a # value or % | Excel Worksheet Functions | |||
| can I get some help here? No response yet :) | Excel Worksheet Functions | |||
Hybrid Mode
