Dealing with empty cells

How about:

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

Vtype contingency

Forgot the vtype contingency....

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

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

Re: Vtype contingency

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

Cheers
tee

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

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

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

=======

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

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

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

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