seemingly simple dropdown menu/vlookup question

  • Thread starter Thread starter Ratedr
  • Start date Start date
R

Ratedr

I have a question that seems to be something that is probably simple
for an experienced user, but I cant seem to figure it out:
in one worksheet (lets say called sheet "customers" I have

column a column b
a 1
b 2
c 3
d 4
e 5
f 6

I highlighted all of column a and named it "custlist". now in another
worksheet I have created a dropdown box in cell a1 including all of
the items in column a shown above in "custlist". What I want, is when
I select, for example, "c" from the dropdown menu...I want the #3 to
be displayed in cell b2 of that sheet, when I select "f", 6 comes up,
and so on. Any help would be greatly appreciated
 
In B2 in the sheet with the validation in A1 put


=IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,0))


Replace Sheet1 if necessary with whatever sheet name you are using

--


Regards,


Peo Sjoblom
 
ok just for my own knowledge so I dont have to ask again (I like to
try and understand to do it myself in the future). In that vlookup
formula, what do each of the parts mean? The reason I ask is that in
the same example above, I realized I also had information in columns c
and d that I wanted to utilize in the same fashion, so I have these
formulas


=IF(A1="","",VLOOKUP(A1,Sheet2!A:C,3,0))
=IF(A1="","",VLOOKUP(A1,Sheet1!A:D,4,0))

and it is working perfectly...I just dont know WHY (I used trial and
error to get those). What does the a:c mean in there? what is the 3
for?
 
The A:C is the size of the table. If you open a new workbook and go to the
namebox (above column A) and type in A:C and then press enter then A1:C65536
(Excel 97-2003) will be selected. So the formula will take the value in A1
and try to find an exact match in column A (always the leftmost column when
using VLOOKUP), once it finds the value it will go 3 columns to the right
counted and including the first column (in this case A) so it will return
what's in column C A = 1, B = 2 and C = 3 that is what the 3 means after the
range so if the value is found in A20 C20 will be returned. In the other
formula you are using A:D and 4 which means it will find the value in A and
return the value from D same row as in A. The FALSE or 0 at the end of the
formula tells it to look for an exact value, if you omit it or use TRUE or 1
and your values in column A are sorted in an ascending order and there is no
match it will lookup the nearest lower value to the lookup value.

--


Regards,


Peo Sjoblom


ok just for my own knowledge so I dont have to ask again (I like to
try and understand to do it myself in the future). In that vlookup
formula, what do each of the parts mean? The reason I ask is that in
the same example above, I realized I also had information in columns c
and d that I wanted to utilize in the same fashion, so I have these
formulas


=IF(A1="","",VLOOKUP(A1,Sheet2!A:C,3,0))
=IF(A1="","",VLOOKUP(A1,Sheet1!A:D,4,0))

and it is working perfectly...I just dont know WHY (I used trial and
error to get those). What does the a:c mean in there? what is the 3
for?
 
thank you so much..this is working great. I know Im going to sound
like a pain, but an interesting thing came up when I was writing
this. Is there a way to conditionally display a formula or
something? I dont even know if thats the way to describe it..but
heres what I mean


column a column b c
a =((c1*3)+41)
100 (this is cell c1)
b =((c1*3)+4)*1.2
c =((c1*3)+4)-16
d =((c1*3)+4)*1.15
e =((c1*3)+8)


Now I have my drop down menu containing a-->e from column a above. I
have a cell already that is telling it, when the user selects "d" from
the dropdown menu, it will actually DO the formula (c1*3)+4)*1.15 and
it will display the result of 349.6....but What I want is to put
something in ANOTHER cell stating, when the dropdown menu selects the
d, it displays the formula, doesnt do the calculations, just displays
"(c1*3)+4)*1.15"
Is this possible?
 
Back
Top