| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting #VALUE
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting #VALUE
#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. "Don" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting #VALUE
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 ToolsFormula AuditingEvaluate formula -- Kind regards, Niek Otten Microsoft MVP - Excel "Don" wrote in message ... | 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 | | |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting #VALUE
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 "Dave F" wrote in message ... #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. "Don" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting #VALUE
If you multiply a "formula blank" by a number you'll get an error, perhaps
change D7 formula to =IF(C7="","",C7*$B$3) "Don" wrote: 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
