#VALUE!

[BNT1 via OfficeKB.com]

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

 

[Kevin B]

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

 

[Dave Peterson]

Maybe you meant:

=if(a1="","",networkdays(k1,k3)-1)

 

[BNT1 via OfficeKB.com]

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 53 contain if statement =if (="","" etc

Hope I explained so you can understand

Any idea how to fix this #VALUE!

regards

Brian

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.aspx/ms-excel/200805/1

 

[Dave Peterson]

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

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 53 contain if statement =if (="","" etc

Hope I explained so you can understand

Any idea how to fix this #VALUE!

regards

Brian

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.aspx/ms-excel/200805/1

 

[BNT1 via OfficeKB.com]

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

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]

 

[Dave Peterson]

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

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

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]

 

[BNT1 via OfficeKB.com]

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

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.aspx/ms-excel/200805/1