Excel formulas?

[jf-08]

I am writing a program in Excel that links one spreadsheet to another.

In one sheet, I have basically an ordering form. For example's sake, let's say that the data I am referencing is shoes.

I have several hundred types of shoes I am referencing. Each pair has the following data:

Type - Shoe, slipper, boot, etc - there are say 50 types

Size - shoe size

Gender - M / F

Price - $ per pair

Is there any way I can plug into a formula the quantity, type, gender and size and the price would automatically pop up without too much effort?

The data is one spread sheet and the order form is on another and I don't want to resort to the AutoFilter pull down arrows and manually look up the price.

Thanks.

 

[Linderbee]

shoes

(some language)

 

[dreamcastrocks]

shoes

(some language)

that link killed my browser.

 

[abomb]

I am writing a program in Excel that links one spreadsheet to another.

In one sheet, I have basically an ordering form. For example's sake, let's say that the data I am referencing is shoes.

I have several hundred types of shoes I am referencing. Each pair has the following data:

Type - Shoe, slipper, boot, etc - there are say 50 types

Size - shoe size

Gender - M / F

Price - $ per pair

Is there any way I can plug into a formula the quantity, type, gender and size and the price would automatically pop up without too much effort?

The data is one spread sheet and the order form is on another and I don't want to resort to the AutoFilter pull down arrows and manually look up the price.

Thanks.

I can do it, but it is hard to explain. Let me give it a shot.

I would concatenate type,size,gender,price into a single cell (could be hidden) on the order form. The formula is =concatenate(cell1, cell2, cell3, cell4). That would give you something like this "Sneaker10M120". Let's call that a key.

I am assuming your "inventory" spreadsheet has the same fields and looks something like this;

quantity	type	gender	size	price
25		sneaker	M	10	120
2		pump	F	4	300
43		clogs	M	11	89

In the first column of that sheet, I would concatenate the same fields so I had a master list of all the possible keys and the next column would be price.

Back in the order form sheet, in the cell where you want the price to display, I would type =vlookup(key,columns in other sheet where keys and price are stored, 2, FALSE). This will take the lookup value and return the price.

It doesnt matter if they are in seperate sheets. If you like, I can trade emails with you tonight if you dont get it working, but I am sure that will work.

 

[Gizmo Williams]

You could probably do something with the VLOOKUP function. The VLOOKUP function basically looks for a value (your input) from the first column of a table and then returns the value from another column (in this case price) from that row based on how many columns you code it to pull the value from.


Also, you may want to concatenate all the input fields into one field to make the VLOOKUP function easier to code. Otherwise you will have to imbed a ton of VLOOKUPs to get it to work.

 

[Kel Varnsen]

You can also use the & symbol instead of the concatenate function.

 

[jf-08]

thanks!

 

[Russ Smith]

Who do you think you are Imelda Marcos?