MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dealing with empty cells


Posted by tee on January 14, 2002 2:55 PM

Hi Everyone

How can I stop an error message when one of my cells is empty with this function.

=SUMPRODUCT((YEAR(VDate)=2002)*(MONTH(VDate)=1)*(VType="Victim"))

If the user does not put a value in the cell - there is an error. #REF I think from memory.

Many thanks
tee


Posted by Nate Oliver on January 14, 2002 3:04 PM

How about:

=if(or(vtype,vdate=""),"Complete Input Cells",SUMPRODUCT((YEAR(VDate)=2002)*(MONTH(VDate)=1)*(VType="Victim")))

Posted by Nate Oliver on January 14, 2002 3:05 PM

Vtype contingency

Forgot the vtype contingency....

=if(or(vtype="",vdate=""),"Complete Input Cells",SUMPRODUCT((YEAR(VDate)=2002)*(MONTH(VDate)=1)*(VType="Victim")))

Posted by Aladin Akyurek on January 14, 2002 4:02 PM


tee --

Do you have formulas in range VDate that return blanks? If so, a #VALUE! will result.

If my guess is right, either you have to change the formulas in VDate to return zero instead of "" or you have to swich to an explicitly array formula that filters VDate:

{=SUM(IF(LEN(VDate),(YEAR(VDate)=2002)*(MONTH(VDate)=1)*(VType="Victim")))}

Aladin

========== Hi Everyone

Posted by tee on January 14, 2002 9:43 PM

Re: Vtype contingency

Many thanks but I still get a #NA error in my cell that is empty. Any more suggestions.

Cheers
tee

Posted by Aladin Akyurek on January 15, 2002 1:00 AM

Re: Vtype contingency

One reason for #N/A is that VDate and VType are NOT the same size: e.g., if VDate is A1:A10 and VType B1:B9 (irrespective of the presence of blanks cells).

If VDate and VType are dynamic named ranges whose last cells are computed independent of each other, unequal number of blanks cells in used ranges would lead to unequal sizes, and cause #N/A when used in SUMPRODUCT.

====== but I still get a #NA error in my cell that is empty. Any more suggestions. Cheers

Posted by tee on January 15, 2002 1:11 AM

Re: Vtype contingency

Many thanks Aladin

Yes they are dynamic named ranges - any suggestions in how I can work around this.

I have data validation on my cells for the user to select set values. Is there any way I can also add a messagebox if the cell is left empty in the row.

This has to very foolproof for my users.

Cheers
tee

Posted by Aladin Akyurek on January 15, 2002 1:27 AM

Re: Vtype contingency

tee --

> Yes they are dynamic named ranges - any suggestions in how I can work around this.

How are they computed? That is, What is the formula that you use?

> I have data validation on my cells for the user to select set values. Is there any way I can also add a messagebox if the cell is left empty in the row.

I believe it's possible to add a messagebox (VBA folks can help with that). However, if the user still fails to select a value, the problem of unequally sized ranges is stll there. You need to compute equally sized VDate and VType also in case of adverse conditions.

Aladin

=======

Posted by tee on January 15, 2002 2:26 PM

Re: Vtype contingency

Aladin

Many thanks again > Yes they are dynamic named ranges - formula below: How are they computed? That is, What is the formula that you use?

************
=DB!$D$2:OFFSET(DB!$D$2,COUNTA(DB!$D$2:$D$65536)-1,0)
**********

Cheers
tee

Posted by Aladin Akyurek on January 15, 2002 2:54 PM

Range contingency

tee --

I propose that you use the column with dates as the base for defining required dynamic ranges.

Define EndRow as

=MATCH(9.99999999999999E+307,DB!$C:$C)

Replace $C:$C by the column where you have VDate (Dates).

Define VDate as

=OFFSET(DB!$C$2,0,0,Endrow-1,1)

Define VType (assuming that VTypes in D:D, otherwise change the ref)

=OFFSET(DB!$D$2,0,0,Endrow-1,1)

These are more efficient computationally, and more importantly, the size of VType will be set equal to the size of VDate, a fact that SUMPRODUCT needs. There will be no adverse effects.

And, I'd suggest to start another thread to investigate the possibility of using VBA to incite users to complete their input. As I said earlier, there will be no #N/A's, however, if they fail to do so.

Regards,

Aladin

========= Aladin again : > Yes they are dynamic named ranges - formula below

Posted by Nate Oliver on January 15, 2002 4:19 PM

Re: Range contingency

The input box isn't too bad:

Sub Input_Prompt2()
InputDialog = Application.InputBox(prompt:="Enter Information", Title:="Please Enter Information", Type:=1)
If InputDialog <> False Then Range("A1") = InputDialog
End Sub

Notice with the 'type' you can validate data to a certain extent:

Value Meaning
0 A formula
1 A number
2 Text (a string)
4 A logical value (True or False)
8 A cell reference, as a Range object
16 An error value, such as #N/A
64 An array of values


You can use the sum of the allowable values for Type. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.

Not sure how this will work with your data validation settings, probably not well....

If you want a list box, but my experience is drawing dialogs and embedding controls, etc....

tee -- I propose that you use the column with dates as the base for defining required dynamic ranges. Define EndRow as =MATCH(9.99999999999999E+307,DB!$C:$C) Replace $C:$C by the column where you have VDate (Dates). Define VDate as =OFFSET(DB!$C$2,0,0,Endrow-1,1) Define VType (assuming that VTypes in D:D, otherwise change the ref) =OFFSET(DB!$D$2,0,0,Endrow-1,1) These are more efficient computationally, and more importantly, the size of VType will be set equal to the size of VDate, a fact that SUMPRODUCT needs. There will be no adverse effects. And, I'd suggest to start another thread to investigate the possibility of using VBA to incite users to complete their input. As I said earlier, there will be no #N/A's, however, if they fail to do so. Regards, Aladin ========= : Aladin : Many thanks again

Posted by tee on January 15, 2002 6:26 PM

Many thanks to both of you

Many thanks to both of you.

It's getting abit complicated and beyond my limited knowledge.

I will try and work around it somehow.

Tee