Getting #VALUE

[Don]

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

 

[Guest]

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

Dave

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
|
|

 

[Don]

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.

Dave
--
Brevity is the soul of wit.

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

 

[Guest]

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

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