Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE!
Wonder why no responce, entered in wrong grouping !
Hi In cell A1 I have a vlookup formula and =if(sheet1A1="","",sheet1a1) which returns the correct result when blank. In M1, I have the formula =if(a1="","",networkdays(k1,k3))-1 This also returns the required info when sheet1A1 has something in the cell, but when sheet1A1 is empty, M1 returns #VALUE! I have tried in M1 =if(iserror(networkdays(k1,k3))-1 etc, but get the same result (#VALUE!) any help appriciated to tidy up the spreadsheet by removing #VALUE! regards Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200805/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE!
Try the following nested IF:
=IF(ISERROR(if(a1="","",networkdays(k1,k3))-1),"",if(a1="","",networkdays(k1,k3))-1) The generic formula would be =IF(ISERROR(YourFormula),"",YourFormula) Hope this helps -- Kevin Backmann "BNT1 via OfficeKB.com" wrote: Wonder why no responce, entered in wrong grouping ! Hi In cell A1 I have a vlookup formula and =if(sheet1A1="","",sheet1a1) which returns the correct result when blank. In M1, I have the formula =if(a1="","",networkdays(k1,k3))-1 This also returns the required info when sheet1A1 has something in the cell, but when sheet1A1 is empty, M1 returns #VALUE! I have tried in M1 =if(iserror(networkdays(k1,k3))-1 etc, but get the same result (#VALUE!) any help appriciated to tidy up the spreadsheet by removing #VALUE! regards Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200805/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE!
Maybe you meant:
=if(a1="","",networkdays(k1,k3)-1) "BNT1 via OfficeKB.com" wrote: Wonder why no responce, entered in wrong grouping ! Hi In cell A1 I have a vlookup formula and =if(sheet1A1="","",sheet1a1) which returns the correct result when blank. In M1, I have the formula =if(a1="","",networkdays(k1,k3))-1 This also returns the required info when sheet1A1 has something in the cell, but when sheet1A1 is empty, M1 returns #VALUE! I have tried in M1 =if(iserror(networkdays(k1,k3))-1 etc, but get the same result (#VALUE!) any help appriciated to tidy up the spreadsheet by removing #VALUE! regards Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200805/1 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE!
Hi, thanks for the response
I still get #VALUE! error formula reads in M53 = if(iserror(networkdays(C53,K53))-1,"",networkdays(C53, K53))-1 If I enter in M53 = if(a53="","",networkdays(c53,k53)0-1 I still get #VALUE! error incidentally, cells a53 :D53 contain if statement =if (="","" etc Hope I explained so you can understand Any idea how to fix this #VALUE! regards Brian Dave Peterson wrote: Maybe you meant: =if(a1="","",networkdays(k1,k3)-1) Wonder why no responce, entered in wrong grouping ! [quoted text clipped - 19 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200805/1 -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE!
What's in C53 and k53?
You may want to post the exact formulas--copied from the formulabar--into your posts. Sometimes typos exist in the real formula, sometimes typos are just caused by typing the message. "BNT1 via OfficeKB.com" wrote: Hi, thanks for the response I still get #VALUE! error formula reads in M53 = if(iserror(networkdays(C53,K53))-1,"",networkdays(C53, K53))-1 If I enter in M53 = if(a53="","",networkdays(c53,k53)0-1 I still get #VALUE! error incidentally, cells a53 :D53 contain if statement =if (="","" etc Hope I explained so you can understand Any idea how to fix this #VALUE! regards Brian Dave Peterson wrote: Maybe you meant: =if(a1="","",networkdays(k1,k3)-1) Wonder why no responce, entered in wrong grouping ! [quoted text clipped - 19 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200805/1 -- Message posted via http://www.officekb.com -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE!
Hi Dave
All below are in the summary sheet In A53 =IF('Walon Notification'!A53="","",'Walon Notification'!A53) In C53 =IF('Walon Notification'!A53="","",'Walon Notification'!H53) For some reason unknown to me ! when i copy and paste (formula bar) K53 , what is pasted, is cell c53 formula.....strange. Have done it several times and comes the same result every time I have meticulously typed what is in K53. below =if(A53="","",VLOOKUP(A53,'From Woodside'!$A$2:$J$700,5,0)) hope this helps regards Dave Peterson wrote: What's in C53 and k53? You may want to post the exact formulas--copied from the formulabar--into your posts. Sometimes typos exist in the real formula, sometimes typos are just caused by typing the message. Hi, thanks for the response [quoted text clipped - 27 lines] -- Message posted via http://www.officekb.com -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200805/1 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE!
And these formulas evaluate to real dates -- not just strings that look like
dates, right? How about sharing what the dates are in those two cells? If you put: =networkdays(c53,k53) in a different cell, what happens? If you reformat C53 and K53 as General and type in a couple of real dates, what happens? ====== If the values being returned are coming from a web page, maybe you got some of those HTML non-breaking spaces in there with what looks like a date. Try reentering those dates (you'll have to find the cells ('walon notification'!a53 and the cell from the =vlookup() formula) that are sending the values, too. If you have lots of these strings that look like dates that need to be corrected, then maybe... David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") And if you're new to macros, you may want to read David's intro: http://www.mvps.org/dmcritchie/excel/getstarted.htm "BNT1 via OfficeKB.com" wrote: Hi Dave All below are in the summary sheet In A53 =IF('Walon Notification'!A53="","",'Walon Notification'!A53) In C53 =IF('Walon Notification'!A53="","",'Walon Notification'!H53) For some reason unknown to me ! when i copy and paste (formula bar) K53 , what is pasted, is cell c53 formula.....strange. Have done it several times and comes the same result every time I have meticulously typed what is in K53. below =if(A53="","",VLOOKUP(A53,'From Woodside'!$A$2:$J$700,5,0)) hope this helps regards Dave Peterson wrote: What's in C53 and k53? You may want to post the exact formulas--copied from the formulabar--into your posts. Sometimes typos exist in the real formula, sometimes typos are just caused by typing the message. Hi, thanks for the response [quoted text clipped - 27 lines] -- Message posted via http://www.officekb.com -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200805/1 -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE!
thanks dave, this could be the problem as the are reports generated by what
ever system they use. will have a look at your suggestions regards Dave Peterson wrote: And these formulas evaluate to real dates -- not just strings that look like dates, right? How about sharing what the dates are in those two cells? If you put: =networkdays(c53,k53) in a different cell, what happens? If you reformat C53 and K53 as General and type in a couple of real dates, what happens? ====== If the values being returned are coming from a web page, maybe you got some of those HTML non-breaking spaces in there with what looks like a date. Try reentering those dates (you'll have to find the cells ('walon notification'!a53 and the cell from the =vlookup() formula) that are sending the values, too. If you have lots of these strings that look like dates that need to be corrected, then maybe... David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") And if you're new to macros, you may want to read David's intro: http://www.mvps.org/dmcritchie/excel/getstarted.htm Hi Dave [quoted text clipped - 29 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200805/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200805/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|