Prompting an automatic entry using drop-down lists

  • Thread starter Thread starter JeffJ
  • Start date Start date
J

JeffJ

If cells A1 and A2 contain drop-down lists, and the choices in A1 are R
S, and T; and the choices in A2 are 2, 3 and 4; If I select S or T i
A1, I want 2 to automatically appear in A2 (since S and T can only b
2). But if I choose R in A1, I want to be able to manually choose 2,
or 4 from A2 (since R can be either 2, 3 or 4).
Since A1 and A2 both contain drop-down lists, I'm guessing they canno
at the same time contain formulas; therefore I would have to use
third cell for the formula, i.e., maybe B1. So the formula would hav
to be in B1, but the result would have to show up in A2 (actually
since B1 will be hidden, it's OK if the result shows up both in B1 an
in A2). I'm guessing the solution might be sort of similar to this:
IF(OR(A1="S",A1="T"),A2,IF(A2<0,A2=2)) In this example, the "A2<0" i
probably the main problem area. Thank you for any help. -Jef
Jenkins, (e-mail address removed), http://lightningfingers.tripod.co
 
Hi
this is not possible without using an event procedure in VBA. Formulas
can't change other cells.
So would VBA/event procedures be a feasible way?
 
Thanks for your reply, Frank.

However, I did find a workaround using VLOOKUP.
Of course, I still can't enter a formula into the cell containing th
drop-down list, but I inserted another row below that, and entered th
VLOOKUP formula in that empty cell.

So, the VLOOKUP cell will be automatically populated if you choose
certain option from the drop-down list; but if you choose a differen
option, you can still manually select an option from the secon
drop-down list.

In other words (for the benefit of anyone who is not familiar wit
VLOOKUP), if A1 has a drop-down list with the choices R,S,T; and A2 ha
a drop-down list with the choices 2,3,4; I entered the VLOOKUP formul
in A3.

For my purposes, if you choose R from the drop-down list, you ca
choose 2,3, or 4 from the other drop-down list; but if you choose S o
T from the first drop-down list, you can only choose 3 from the secon
drop-down list. So, as I said in my previous post, if you choose S o
T from the first drop-down list, I want it to automatically populat
the second cell with 3.

So, if the origin of my A1 drop-down list (R,S and T) comes from B1,B
and B3 (and were linked to A1 via Data>Validation>List); and the origi
of my A2 drop-down list comes from C1,C2 and C3 (which then have 2,3
and 4 entered in those cells, respectively); A3 would contain th
formula =VLOOKUP(A1,B1:C3,2)

A1 defines the value the VLOOKUP function searches for; the VLOOKU
function searches for a value in the leftmost column of a table (i
this case, the data in cells B1:C3) and then returns a value in th
same row from a column you specify in the table (basically, it finds
match in cells B1:B3 and finds the corresponding entry in that sam
row, which would be in the range of C1:C3).

(I'm using a simplified example here instead of the actual cells an
info I used, so I hope I didn't miss anything or say it wrong.)

So now, if I choose S or T in A1, A3 will return 3. But if I choose
in A1, I can now manually choose 2, 3 or 4 in A2. I placed a titl
which spans across A2 and A3 so that you know they are to be treated a
one entry; and I color-coded A2 and A3 differently, so that you know A
is a manual entry, but A3 is an automatic entry (so you don'
accidently type over and erase the formula in A3; I may also lock A
and protect the sheet in order to prevent overwriting cells).

One problem is this: If you choose R in A1, A3 returns an error o
#N/A. I resolved this by going to Format>Conditional Formatting
choosing "Formula Is" and typing in =ISNA(A3), then selecting a fon
color to match the cell color.

This solved my problem.

-Jeff
(e-mail address removed)
 
Back
Top