Drop-down box lookup?
 

Here's what I want to do:

This is for a record of sales. Each time a sale is made, next to the
invoice number and all of that I want to have a pull-down box where I can
choose from my Inventory List and pick which item was sold. That's my first
problem. I can't seem to get the "Pick From Drop-down List" feature to work
with my already populated list.

Second problem is that if I ever get it to where I can select from the list
which item has been sold, I want the very next cell to automatically pull the
wholesale cost from the inventory list.

I thought I had a clever IF THEN formula written which would have absolutely
worked except for the limit on the number of nested formulas.

I don't quite get the explanation in the Help section for HLOOKUP & all
that. Can anyone make it simple for me? I know I could do this in about 5
seconds in Access but unfortunately I don't have it.

Thanks to anyone who helps!!!

Brenna

Drop-down box lookup?
 

Whether to use HLOOKUP or VLOOKUP depends on how your data is laid out
in your inventory list. I would expect you to have columns for some ID
code, Description, number available, price etc and then a list of the
items going down the sheet. If this is the case then you would use
VLOOKUP. If you can confirm your layout and the names of the sheets
involved I can provide you with a formula to do what you want.

Hope this helps.

Pete

On Sep 21, 11:04 pm, femme-a-9
wrote:
Here's what I want to do:

This is for a record of sales. Each time a sale is made, next to the
invoice number and all of that I want to have a pull-down box where I can
choose from my Inventory List and pick which item was sold. That's my first
problem. I can't seem to get the "Pick From Drop-down List" feature to work
with my already populated list.

Second problem is that if I ever get it to where I can select from the list
which item has been sold, I want the very next cell to automatically pull the
wholesale cost from the inventory list.

I thought I had a clever IF THEN formula written which would have absolutely
worked except for the limit on the number of nested formulas.

I don't quite get the explanation in the Help section for HLOOKUP & all
that. Can anyone make it simple for me? I know I could do this in about 5
seconds in Access but unfortunately I don't have it.

Thanks to anyone who helps!!!

Brenna

Drop-down box lookup?
 

Hi Pete,

thanks for helping. For right now I have things super-simple. My inventory
list was created in the same sheet as my Sales record, off to the side, say
cells AA1 & AB1 thru AA44 & AB44 . My inventory tracking is a whole other
system so all my inventory list has is the Description (AA) & the Wholesale
Cost (AB) of that item.

Do I need to move my inventory list to another sheet or will it work if it's
just off out of the main working area of the Sales sheet?



"Pete_UK" wrote:

Whether to use HLOOKUP or VLOOKUP depends on how your data is laid out
in your inventory list. I would expect you to have columns for some ID
code, Description, number available, price etc and then a list of the
items going down the sheet. If this is the case then you would use
VLOOKUP. If you can confirm your layout and the names of the sheets
involved I can provide you with a formula to do what you want.

Hope this helps.

Pete

Drop-down box lookup?
 

Assuming that you get your drop-down box selection working, and that
this is in cell D1, then you can put this formula in E1:

=IF(D1="","",VLOOKUP(D1,$AA$1:$AB$44,2,0))

You can if you wish copy this down the column if you have other items
in D2, D3 etc.

Now, as regards getting your drop-down box working, select cell D1 and
then Data | Validation and in the Settings tab under Allow, select
List and in the Source panel enter $AA$1:$AA$44 (or just point to this
range) then click OK.

When you select D1 now you will see a drop-down arrow on the right
side of the cell - click on this and you will see your descriptions,
from which you can select one, and if you do then the formula in E1
will return the wholesale price. You can copy D1 into D2:D3, for
example, if you are likely to have other items on the same order. Of
course, you should change the cell references to suit your data.

Hope this helps.

Pete

On Sep 22, 5:30 pm, femme-a-9
wrote:
Hi Pete,

thanks for helping. For right now I have things super-simple. My inventory
list was created in the same sheet as my Sales record, off to the side, say
cells AA1 & AB1 thru AA44 & AB44 . My inventory tracking is a whole other
system so all my inventory list has is the Description (AA) & the Wholesale
Cost (AB) of that item.

Do I need to move my inventory list to another sheet or will it work if it's
just off out of the main working area of the Sales sheet?



"Pete_UK" wrote:
Whether to use HLOOKUP or VLOOKUP depends on how your data is laid out
in your inventory list. I would expect you to have columns for some ID
code, Description, number available, price etc and then a list of the
items going down the sheet. If this is the case then you would use
VLOOKUP. If you can confirm your layout and the names of the sheets
involved I can provide you with a formula to do what you want.

Hope this helps.

Pete- Hide quoted text -

- Show quoted text -