Getting #VALUE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don - 06 Nov 2006 18:42 GMT

In the cells below, shows the formulas with resulting value below
it.......What would cause the #value?

A1 Gretchun

B3 0.65

A7 Forumla =IF('[Cleaning Schedule.xls]Cleaning
History'!$A2=$A$1,'[Cleaning Schedule.xls]Cleaning History'!$B2,"")
=if ( Erik = Gretchun, 11/2/2006,"")
A7 Value ""

B7 Formula =IF(A7<>"",'[Cleaning Schedule.xls]Cleaning
History'!$C2,"")
Boat
Name
B7 Value ""

C7 Formula =IF(B7<>"",VLOOKUP(B7,'[Boats and Marinas.xls]Active
Boats'!$B$3:$C$203,2,FALSE),"")

Boat Footage
C7 Value ""

D7 Formula =C7*$B$3
D7 Value #Value

E7 Formula =IF(MONTH(A7)=11,D7,"")
E7 Value #Value

However, where C7 is not blank, D7 evaluates correctly

Date Boat Footage Pay
7 #VALUE!
8 #VALUE!
9 #VALUE!
10 #VALUE!
11 #VALUE!
12 #VALUE!
13 11/2/2006 PERSHING 53 $ 34.45

Thank you for any help,
Don

Reply to this Message

Dave F - 06 Nov 2006 19:06 GMT

#VALUE is returned when XL can't calculate a formula. Perhaps one of the
cells that your formula references is formatted as text.

Dave

Signature

Brevity is the soul of wit.

> In the cells below, shows the formulas with resulting value below
> it.......What would cause the #value?
[quoted text clipped - 39 lines]
> Thank you for any help,
> Don

Reply to this Message

Don - 06 Nov 2006 19:18 GMT

I've ensured that all numeric cells are formatted as numeric (including
referenced cells) and date cells as date (including referenced cells). Still
doesn't help.
Any other suggestions?

Don

> #VALUE is returned when XL can't calculate a formula. Perhaps one of the
> cells that your formula references is formatted as text.
[quoted text clipped - 45 lines]
>> Thank you for any help,
>> Don

Reply to this Message

Niek Otten - 06 Nov 2006 19:14 GMT

It is difficult to read your formulas because they wrap in my newsreader.
Try renaming your sheets S1, S2, etc, and post your formulas again.
Or test the formulas with all data on one sheet first, to keep the formulas simple
Or try Tools>Formula Auditing>Evaluate formula

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| In the cells below, shows the formulas with resulting value below
| it.......What would cause the #value?
[quoted text clipped - 39 lines]
| Thank you for any help,
| Don

Reply to this Message

daddylonglegs - 06 Nov 2006 19:20 GMT

If you multiply a "formula blank" by a number you'll get an error, perhaps
change D7 formula to

=IF(C7="","",C7*$B$3)

> In the cells below, shows the formulas with resulting value below
> it.......What would cause the #value?
[quoted text clipped - 39 lines]
> Thank you for any help,
> Don

Reply to this Message