Using Vlookup or match for certain criteria to return a drop down list

[fordieuk]

Hi,

Hopefully someone can help, I know what I want to achieve but I dont know how to achieve it. Basically I have a spreadsheet with several coumns, date, supplier, description, quantity, order number and cost etc; all the information in the columns are entered apart from the 'cost' column I want the cost column to search the other tabs 'A,B,C... -Z' for the cost using the data from the other columns i.e mainly supplier, description and order number but because the descriptions do not fully link and not exactly the same I wouldnt mind a drop down box listing all the items under that order number so I can select the exact cost.

I have tried to attached an example spreadsheet

Hope that helps and someone can help me...

[benishiryo]

hi fordieuk. i'm assuming your Sheets names are based on the 1st letter of the Supplier? and my formula here wont be dynamic if your rows in Sheets A-Z exceed 100. you have to use the OFFSET formula or convert your data into Tables. but the hassle is that you have to do it 26 times. anyway, try if this works for you & extend the range if you need to:
=VLOOKUP(I2,INDIRECT(LEFT(E2,1)&"!$B$3:$F$100"),5,0)

[MarvinP]

Hi fordieuk,

This problem would much easier if all the single letter sheets were on the same sheet. Why do you have the separate by first letter of Supplier? I'd suggest you combine all of them to a single sheet and use Auto Filter instead of multiple sheets to deal with them. Then a single VLookup could be used to find your prices.

You are just making it too complicated for yourself.

[fordieuk]

Thanks, that does work but the problem I have is that there are several items under one order number and I was hoping to have a list to select from which obviously displays the description and cost and I chose the correct item. Also I would need to go over 100 cells.

Let me know if you can help anymore.

[fordieuk]

Thanks that works but the problem is I have loads of items under the same order number and I was hoping to select which decription and cost is linked to that delivered item, I thought a drop down list, is that possible? Also would have to go over 100 rows need more like 1000.

let me know if you can help anymore appriciate it.

[fordieuk]

Marvin P,

Thanks and good suggestions but I have a list of orders which have been setup and a list of deliveries so I need to put the cost to the actual delivered items. in some cases may order 200 of something but only ever have 180 delivered.

can you help anymore?

regards

[MarvinP]

Create a field to tag ordered/delivered, then filter on this too.

Attach a bigger example and I'll show you.

[fordieuk]

Delivery & Orders example 2.xls

Marvin P please see attached. Thanks for your help

[MarvinP]

Hi fordienuk

See the attached where I've put all the info on a single sheet in a single table. It is much better this way. Hope this helps.

[fordieuk]

MarvinP,

Thanks for your help, the problem I have is the delivered sheet needs to stay the same as this will get forwarded on and my main objective is to find a quicker way to insert the cost of what has been deliveried through searching the orders that have been place, I am happy to put all the orders on one page.

I was hoping that it was possible to search for the order number and return the descriptions in a drop down list enabling me to select which description it is and once selected the cost of that item that was on the order will be inserted in the delivered sheet.

I have attached my latest excel example and if you can help with my requirements I would appreciate it.

Thanks

[MarvinP]

You should keep orders and deliveries on the same sheet. Then use filters to show rows by date or product or ordered but not delivered yet.

[fordieuk]

Marvin,

Thanks for that I appriciate your resaponse, I have to keep the deliveries sheet as that template because it gets sent off each week.

I have my deliveries sheet and my goal is to have a drop down list under the cost per unit row which when selected searches the orders sheet for the order number of that item and then I select the item under that order and once selected the cost per unit is returned. Is that possible to acheive?

regards

[TMS]

Looks like I was wasting a lot of time and effort over here ...

http://www.excelforum.com/excel-form...-down-box.html

Arlette has now closed that thread but I'm out.

OP clearly has an approach in mind which may not be achievable.

Regards, TMS

[fordieuk]

Thanks for your help TMS seems a simply query doesn't have a simply answer.

All the best.

[MarvinP]

Hi fordieuk,

I've been troubled by your question for a few days now. Your question would be easy if you set up the data to work with the filters and sorts that are allowed within Excel. We get sheet and workbook designs that stretch the capabilities of Excel. We can normally find solutions using advanced functions or VBA.

Many times my answer should be - just redesign your data so it can work better with Excel.

You are stuck with what you are given or what you already have. That is multiple sheets of data instead of a single one. My answer is - WHY???

If you want a tool to work for you, you give it stuff it can deal with easily. Having multiple pages hurts, instead of helps this problem.

I'm sorry I wasn't much help with the problem. It looks like TM wassn't much help either. Sorry we didn't have the answer you were looking for.

[TMS]

@MarvinP:

It looks like TM wasn't much help either.

OK, turn the knife

[fordieuk]

Marvin,

Sorry for the late reply, I appriciate your input as well as TMS. I am happy to have all the 'Orders' data on one sheet its just the deliveries sheet that need to stay the same.

I have attached example 4 incase you can help anymore as I am still searching for a solution to the problem.

Kind regards

[MarvinP]

Hi,

I'd suggest you look to see what others have done and learn more about how Excel works. Start looking at:
http://office.microsoft.com/en-us/te...ex=2&av=zxl150

Download some of these templates and see if they will work for you.