Excel query about Lists
 

Hi,

Can you help me out to find out a solution about the following..

I have 4 columns in my worksheet,each of them is validated with List of
certain range.I have to check the left cell is blank or not,if the
previous cell(row wise) is not blank then the next cell will allow the
list,otherwise thoe cells will not accept any value.For the 3rd
column's cell ,previous 2 cells has to be checked and follows.

How can I implement that?

Thanks

Regards,
Nil

Excel query about Lists
 

wrote in message
oups.com...
Hi,

Can you help me out to find out a solution about the following..

I have 4 columns in my worksheet,each of them is validated with List of
certain range.I have to check the left cell is blank or not,if the
previous cell(row wise) is not blank then the next cell will allow the
list,otherwise thoe cells will not accept any value.For the 3rd
column's cell ,previous 2 cells has to be checked and follows.

How can I implement that?

To do this you'll need some event code in the worksheet module, which you
can access by rightclicking the sheet tab & selecting "View Code" from the
pop-up menu.

Assuming the 4 columns are A to D, try something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 5 And Target.Column < 1 And Target < "" Then

x = Target.Column - 1

If WorksheetFunction.CountA(Target.Offset(0, 0 - x).Resize(1, x)) < x Then
y = Target.Address
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
MsgBox x & " entries to the left are required."
Range(y).Select
Else
MsgBox "OK"
End If

End If
End Sub

Excel query about Lists
 

Hi Nil,

If your list comes from some other sheet than you can do it in my way.

Say your one list is at Sheet1 and you have named the list as "MyList".
Now at the other sheet where you want to display your list. Make your
list the way you already did. But, at the list box, type the formula

=IF(A1="",MyList,"")

Something like this.

So, when A1 is blank, you can have the list, otherwise not.

Hope that helps.

Thankyou,

Shail


wrote:
Hi,

Can you help me out to find out a solution about the following..

I have 4 columns in my worksheet,each of them is validated with List of
certain range.I have to check the left cell is blank or not,if the
previous cell(row wise) is not blank then the next cell will allow the
list,otherwise thoe cells will not accept any value.For the 3rd
column's cell ,previous 2 cells has to be checked and follows.

How can I implement that?

Thanks

Regards,
Nil

Excel query about Lists
 

"shail" wrote in message
ups.com...
Hi Nil,

If your list comes from some other sheet than you can do it in my way.

Say your one list is at Sheet1 and you have named the list as "MyList".
Now at the other sheet where you want to display your list. Make your
list the way you already did. But, at the list box, type the formula

=IF(A1="",MyList,"")

Something like this.

So, when A1 is blank, you can have the list, otherwise not.

That will only return the first value from MyList. :(

Excel query about Lists
 

No Andy,

It is works fine. I just tried it.

Shail


Andy Brown wrote:
"shail" wrote in message
ups.com...
Hi Nil,

If your list comes from some other sheet than you can do it in my way.

Say your one list is at Sheet1 and you have named the list as "MyList".
Now at the other sheet where you want to display your list. Make your
list the way you already did. But, at the list box, type the formula

=IF(A1="",MyList,"")

Something like this.

So, when A1 is blank, you can have the list, otherwise not.

That will only return the first value from MyList. :(

Excel query about Lists
 

No Andy,

It is works fine. I just tried it.

Shail


Andy Brown wrote:
"shail" wrote in message
ups.com...
Hi Nil,

If your list comes from some other sheet than you can do it in my way.

Say your one list is at Sheet1 and you have named the list as "MyList".
Now at the other sheet where you want to display your list. Make your
list the way you already did. But, at the list box, type the formula

=IF(A1="",MyList,"")

Something like this.

So, when A1 is blank, you can have the list, otherwise not.

That will only return the first value from MyList. :(

Excel query about Lists
 

If MyList contains 3 values (1,2,3), then the formula =IF(A1="",MyList,"")
in B1 of the "other" sheet returns 1 (first value of MyList) if A1 = "". But
it's not possible to choose a value from MyList. Besides, the OP wants to
input actual values in the cells which if s/he does would overwrite any
formulas. :)

Excel query about Lists
 

Hi Andy,

I don't know how you are writing this formula, but it is working just
fine with me. I have a list at sheet1 of 1,2,3,4,5 at A1 till A5 and
have the range as MyList. Then I went to the sheet2. Created a
Validation Dropdown List by writing the formula as =IF(A1="",MyList,"")
at B1.

But somehow I understand what you might be doing. You are just entering
the formula over the cell, not creating the dropdown list. Am I right?

Thankyou,

Shail


Andy Brown wrote:
If MyList contains 3 values (1,2,3), then the formula =IF(A1="",MyList,"")
in B1 of the "other" sheet returns 1 (first value of MyList) if A1 = "". But
it's not possible to choose a value from MyList. Besides, the OP wants to
input actual values in the cells which if s/he does would overwrite any
formulas. :)

Excel query about Lists
 

"shail" wrote in message
oups.com...
Hi Andy,

I don't know how you are writing this formula, but it is working just
fine with me. I have a list at sheet1 of 1,2,3,4,5 at A1 till A5 and
have the range as MyList. Then I went to the sheet2. Created a
Validation Dropdown List by writing the formula as =IF(A1="",MyList,"")
at B1.

But somehow I understand what you might be doing. You are just entering
the formula over the cell, not creating the dropdown list. Am I right?

I see. It wasn't clear that =IF(A1="",MyList,"") should be the Data
Validation formula (with "Custom" for "Allow", presumably). But it still
doesn't work at all well that way -- with A1 blank, there's no dropdown
*and* it lets me enter 6. Which kind of defeats the object. :(

Excel query about Lists
 

Hi again Andy,

The formula should be written in the box of the Data Validation--List,
not at the Formula.

Thankyou,

Shail


I see. It wasn't clear that =IF(A1="",MyList,"") should be the Data
Validation formula (with "Custom" for "Allow", presumably). But it still
doesn't work at all well that way -- with A1 blank, there's no dropdown
*and* it lets me enter 6. Which kind of defeats the object. :(

Excel query about Lists
 

"shail" wrote in message
ups.com...
Hi again Andy,

The formula should be written in the box of the Data Validation--List,
not at the Formula.

Ah (again), the formula should be in the "Source" box -- I see. But ...

If you use =IF(A1<"",MyList,"") for B1 validation (<"" because that's what
the OP actually wants), then sure enough the dropdown doesn't function if
A1="", but then ANY value can be entered in B1. :(

TBH the code option seems much less problematic to me. :)

Excel query about Lists
 

Hi Andy,

Yes, exactly. I just wanted to give OP a picture of how he could do
this. I am not intending to do it the same way I did. If he wants A1 to
be blank, which makes the list not to show or A1 not blank and the list
appear. It will be his choice. I just wanted to give him an idea.

Thankyou again Andy.

Shail


Andy Brown wrote:
"shail" wrote in message
ups.com...
Hi again Andy,

The formula should be written in the box of the Data Validation--List,
not at the Formula.

Ah (again), the formula should be in the "Source" box -- I see. But ...

If you use =IF(A1<"",MyList,"") for B1 validation (<"" because that's what
the OP actually wants), then sure enough the dropdown doesn't function if
A1="", but then ANY value can be entered in B1. :(

TBH the code option seems much less problematic to me. :)