Solved reg. data validation

August 4, 2011 at 01:29:39
Specs: Windows XP
I am trying to do an if statement to include a data validation list. So if cell A1 says "Bill not submitted " then in cell B1 show a drop down list of the reason's (3-4 reason) for not submission. If A1 says anything else then B1 should blank. Can anyone help?



See More: reg. data validation

Report •


✔ Best Answer
August 5, 2011 at 03:39:03
If I understand you correctly, you are trying to do this for each cell in Column A.

Your first post only mentioned A1, so it was only written to work in A1.

This version should work for all of Column A.

The Data Validation ...List...Source should be:

=INDIRECT($C$1)

It always has to refer to C1 so that it picks up the name of the Named Range. The $ ensure that it always uses C1.

The code should be:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells.Count = 1 Then
  If Target.Column = 1 Then
   If Target <> "Bill not submitted" Then _
     Target.Offset(0, 1) = ""
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 4, 2011 at 04:57:26
Within Excel itself, you can set it up so that the Drop Down list will be blank (no choices) if A1 does not contain "Bill not submitted" but once a value is chosen from the list, changing the value in A1 will not clear the value already showing in B1.

In other words, if you have "Bill not submitted" in A1 and you choose "Reason 1" with the drop down, "Reason 1" will still be displayed even if you delete or change the value in A1.

To clear the value in B1 if A1 is changed, you are going to have to add some VBA (a Macro).

If that is acceptable, let me know and I'll put something together.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 4, 2011 at 07:05:15
pls help, how to coded in VBA

Report •

#3
August 4, 2011 at 08:11:15
Here's a method that combines VBA and Dependent Drop Down lists:

I'll Use A1, B1 and C1 in this example.

1 - In C1 enter the formula shown below.
2 - In C2:C5 enter your "reasons"
3 - Select C2:C5.
4 - Click in the Name box above Column A, type in Reasons and then press Enter. This will create a Named Range for C2:C5. (You must press Enter for the Name to take effect. Do not just click out of the Name box)
5 - Select B1.
6 - Data...Validation....List...Source: =INDIRECT(C1)
When A1 contains "Bill not submitted", Reasons will appear in C1. The Data Validation feature will pick up the string Reasons and use it as the Named Range to populate the List for the Drop Down.
When A1 doesn't contain "Bill not submitted", C1 will be blank as will the choices for the Drop Down list.
7 - To clear B1 when A1 doesn't contain "Bill not submitted" we need a macro
8 - Right click the Sheet Tab for the sheet you want this to happen in and choose View Code.
9 - Paste this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$1" Then
   If Target <> "Bill not submitted" Then _
     Target.Offset(0, 1) = ""
 End If
End Sub

           A             B                   C
1   Data Entry Cell   Drop Down    =IF(A1="Bill not submitted", "Reasons", "")
2                                              Reason 1
3                                              Reason 2
4                                              Reason 3
5                                              Reason 4

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 4, 2011 at 08:29:27
Thank you sir,

I want one more help that I want to learn VBA in excel. Can you provide any book or CD for the same


Report •

#5
August 4, 2011 at 12:03:48
I learned by doing so I can't recommend any books. I know that they are out there, but I don't know which ones are worth reading and which ones aren't.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
August 4, 2011 at 20:54:19
Sir,

this formula can applicable in A2, A3 and Continuously?


Report •

#7
August 4, 2011 at 21:25:41
sir,

when i try in same forumla in B2 data validation. the formula shown "=indirect(C2)" same in b3 "=indirect(C3)" also and Continuous.


Report •

#8
August 4, 2011 at 21:36:02
and its working in A1 only

Report •

#9
August 5, 2011 at 03:39:03
✔ Best Answer
If I understand you correctly, you are trying to do this for each cell in Column A.

Your first post only mentioned A1, so it was only written to work in A1.

This version should work for all of Column A.

The Data Validation ...List...Source should be:

=INDIRECT($C$1)

It always has to refer to C1 so that it picks up the name of the Named Range. The $ ensure that it always uses C1.

The code should be:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells.Count = 1 Then
  If Target.Column = 1 Then
   If Target <> "Bill not submitted" Then _
     Target.Offset(0, 1) = ""
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
August 5, 2011 at 05:45:37
Thanking you

suppose if cell A1 drop down says "Bill not submitted " or "bill submitted then" in cell B1 show a drop down list of the reason's (3-4 reason) for not submission & for Bill submitted for (2 reasons'). .
A1 B1
Bill submitted Payment recvd or payment not recvd (required on drop down)
A2 B2
Bill not submitted 2-3 reasons

A3 Continuous


Report •

#11
August 5, 2011 at 19:35:27
Don't take this the wrong way, but let me explain what's happening here.

First you asked for a solution to a question related to A1.

I offered a solution, which required me to set up a workbook with sample data, write some code, test it and then post my suggestion.

Then you came back and asked how to make it work for more cells than just A1.

I offered a solution, which required me to set up a workbook with sample data, write some code, test it and then post my suggestion.

Now you've come back asking for a solution that deals with more than one entry and more than one drop down.

Guess what I will have to do before I can offer a solution?

I'll have to set up a workbook with sample data, write some code, test it and then post my suggestion.

Do you see how it would have been easier (for me) if you had given us all of the requirements in your first post? I'm reluctant to work on a solution to your latest question because I have a feeling that you'll just come back and add more requirements making me repeat everything once again.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
August 7, 2011 at 01:23:52
Sir,

when i try your entire process in my excel sheet, I am unable to generte data as i required. for expample

condition 1

when i try A1 with bill not submitted, B1 showing the reasons as i required, again when i try in A2 with bill paid, B2 showing same reasons but I don't required any reasons for bill paid

A B C
bill not submitted a Reason (reasons showing)
Bill paid a a (reasons showing but i don't want)
b
c
d


Condition 2
When i try A1 with bill paid, bill showing no reasons as I required but when i try in A2 with Bill not submitted, B2 showing nothing

A B C
Bill paid
bill not submitted a (no reasons showing)
b
c
d

Please help


Report •

#13
August 7, 2011 at 02:11:13
Click on the blue statement at the bottom of this post and read the instructions given there.

Then repost your data so that it lines up in columns so we can see the proper layout.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
August 7, 2011 at 06:45:53
Please stop posting your question over and over again. We've read it.

Please do as I asked earlier:

Click on the blue statement at the bottom of this post and read the instructions given there.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question