excel 97 vba - need to associate values with drop down list choice

[chief]

cell C19 = product code drop down list
cells N1:O449 = description of prod associated w/ dropdown list
cell E19 = product description column
cell I19 = price column
cell P1:Q449 = list associated with product code drop down list

Right now there is this code in cell E19:

IF(C19<>""(C19,$N$1:$O$449,2,0),"")

This code allows me to pick a code # from the drop down list in C1
(which is referenced to column N and O) and once selected, th
corresponding product will appear in the product description cel
(E19). I now am trying to set up a code in cell I19 so that once tha
specific product code OR description (whichever is easier to use
reference) is selected it will put the corresponding price into cel
I19. This way the user can click on a product code and have the prope
name and price be input into the appropriate cells, thus saving time.
I tried to play around with the code and i put it into cell I19, bu
once i click the product code drop down button and select a code #, th
product description is fine, but the associated price comes up as #N/A
any ideas as to what can be done?

here was my code modification for the price cell

IF(C19<>""(C19,$P$1:$Q$449,2,0),"") i also tried playing with the 2,
at the end but had no luck. what does that 2,0 at the end reference t
anyways?

thanks to Frank for helping me out earlier with those code

 

[Dave Peterson]

I bet your formula looks more like this:

=IF(C19<>"",vlookup(C19,$N$1:$O$449,2,0),"")

So maybe the second one should look like:

=IF(C19<>"",vlookup(C19,$N$1:$P$449,3,0),"")

The range got changed to include 3 columns (N) and we can still match c19 with
column N. (but this time, you can return the 3rd column--P)

 

[chief]

you're right dave,

it does actually look like that, i scribbled the code down before
left work today and i guess i must have missed that. Thanks

as for the code, i can only access it from work, so i will go i
tomorrow and let you know how it pans out. although i'm prett
confident it will because i don't think that anything you compute
whizzes have given me has failed so far...i am very appreciative of al
the knowledge i have been given.

thanks for respondin

 

[chief]

That worked out great!

Thanks again.

I have stumbled upon another problem which I am trying to solve bu
have been unsuccesful. Since we are dealing with TWO different price
for our invoicing I have to have it so that my product code cells wil
distinguish the proper price for the same product. So rather than g
through and putting an R or T behind the code #, is there a way I coul
put a code in so that ex.
IF cell A1 = Retail Then
'take prices from P11500'
Else IF cell A1 = Trade Then
'take prices from Q1:Q1500'

I don't know, it seems doable but I haven't had any luck.

would this kind of thing be possible with the already formula I have i
the cell I19

 

[Dave Peterson]

I changed your original formula slighty from:
=IF(C19<>"",vlookup(C19,$N$1:$P$1500,3,0),"")
to
=if(c19="","",vlookup(c19,$n$1:$P$1500,3,false))

It's mostly personal preference, but I find I forget the final "" and I end up
with a bunch of Falses.

(and I changed the 0 to false--just because!)

But you could increase the lookup range to include 4 columns ($N$1:$Q$1500) and
that portion of the formula that says to return what column (3 in your formula)
can be a formula that evaluates to what you need).

So one way is:
=IF(C19="","",VLOOKUP(C19,$N$1:$Q$1500,IF($A$1="retail",3,4),FALSE))

If you're not using columns N:Q for anything else, I'd write it this way:
=IF(C19="","",VLOOKUP(C19,$N:$Q,IF($A$1="retail",3,4),FALSE))

So I wouldn't have to ever worry about extending that range (insertion of more
rows).

And (you didn't ask for this much, huh?). I'd move that table to a different
worksheet. Keeping it out of the way makes things easier.

If I have to insert a new row, I don't have to worry about my table getting
fouled up!

=IF(C19="","",VLOOKUP(C19,prices'!$a:$d,IF($A$1="retail",3,4),FALSE))
(and put it in A)

 

[Dave Peterson]

And this:

IF($A$1="retail",3,4)

will return 4 when you have anything besides "retail" in A1. (Is that ok?)

 

[chief]

I cannot thank you enough Dave, you probably saved me about 10 hours o
work. Incredible job!

thanks again.

Spence

 

[Dave Peterson]

Does that mean you get to leave early on Friday? <vbg>

 

[chief]

yeah, hopefully I'll be able to leave to grab some pints before th
working day is done

 

[chief]

I have another problem with the invoice program. I have tried an
tested about 10 different modifications based on the somewhat sam
formulas as stated earlier in the original question. As of right now
have the K34 cell set up just as SUM(K19:K33), which works.
However, now I must reference this back to some type of lookup. I hav
a toggle button on the page which when selected will bring up
validation lookup box in cell D38 with one of three choces available.
I have to somehow set it up so that basically

when one of the choices is selected,

If cell D38 = "name1" then K34 = SUM of K19:K33 * 2%
if cell D38 = "name2" then K34 = SUM of K19:K33 * 4%
if cell D38 = "name3" then K34 = SUM of K19:K33 * 6%

also to make it more interesting, if the toggle button is not selecte
then cell K34 will simply return a SUM of K19:K33

I tried to play with different functions and formulas but obviously t
no success.

the lookup for these three choices are in cells R1:R3

Any ideas

 

[Dave Peterson]

I'm not too sure I understand, but I think:

=if(d38="name1",2%,if(d38="name2",4%,if(d38="name3",6%,100%)))*sum(k19:k33)

But this'll get pretty hairy after just a few.

Did you use data|validation with a range for that list of names?

If yes with that data|validation on sheet2!a1:a3 (and values in Sheet2!b1:b3):

=if(d38="",1,vlookup(d38,sheet2!$a$1:$b$3,2,false))*sum(k19:k33)

Debra Dalgleish has some instructions for using a range from a different
worksheet with data|validation at:
http://www.contextures.com/xlDataVal01.html#Name

 

[chief]

I'll give it a try tomorrow when i get to work, thanks fo
replying...agai

 

[chief]

that worked like a charm!

i had to modify it a bit, but it runs perfectly, once again great wor
dave

i can't thank you eneough for all your help

 

[Dave Peterson]

Glad it all worked out.

that worked like a charm!

i had to modify it a bit, but it runs perfectly, once again great work
dave

i can't thank you eneough for all your help.

 

[chief]

I tried to protect certain cells which have codes in them so that th
user only uses the drop down list in order to filter that informatio
into the other cells. My concern is that a user might enter info int
a cell that has those =IF(....) codes in the cell, and will thereb
eliminate its use of the code. It doesn't seem to work though. I
there a way I can have it so that the VLOOKUP coded cell will b
protected from outside manual input, yet still function when the use
clicks on an item from the dropdown list?

thank

 

[Dave Peterson]

You can lock the cells with formulas to prevent overwriting. (You'll have to
protect the worksheet for this to take effect, though.)

And you could probably do some code that changes that cell if you choose
something from the dropdown or plops in the formula if the dropdown.

First, I used the combobox from the control toolbox toolbar. I removed the
linked cell (under properties).

Then I went into design mode (top left icon on the control toolbox toolbar) and
double clicked on my combobox (it was called combobox1.)

I used A1 as my cell to populate with either the value or the formula.

This was the code I used:

Option Explicit
Private Sub ComboBox1_Change()
With Me.ComboBox1
If .Value = "" _
Or LCase(.Value) = "(none)" Then
Me.Range("a1").Formula = "=vlookup(b1,sheet2!$a:$b,2,false)"
Else
Me.Range("a1").Value = .Value
End If
End With
End Sub

I used a listfillrange to fill my combobox. My first entry in that range was
"(none)"--easy way for the user to "blank" out the combobox.

=========

But I wouldn't do it this way. I'd use 3 cells.

The first cell would hold the formula.
The second cell would be the linked cell for the combobox.
The third cell would be a formula that decided which value I should really use:

Say A1 was the formula, B1 the linked cell, and C1 the "decider" formula:

=if(or(b1="",b1="(none)"),a1,b1)
or even
=if(or(b1={"","(none)"}),a1,b1)

If b1 is empty (or (none), then use A1, else use B1.

Then use C1 for all your subsequent formulas.