|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 06:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com