Drop down menu using info from a different sheet.

[Kalvinsmith18]

Difficult to explain fully what I meant in the title.

I am trying to make this spreadsheet to help a colleague but came into trouble trying to get the correct formula.

I have 2 sheets, the first is the one that will be getting used and the second is full with suppliers costing sheets.
The first sheet ideally will have a table with the drop down menu allowing me to select items/parts from various tables in sheet 2. (That I have done using data validation).
However, I need the prices to be brought over from sheet 2 to sheet 1.

Ideally, the table in sheet 1 will look like this:

Quantity - Item/Part - Rate
0 - -
0 - -
etc - etc - etc

In the item/part column I select an item/part from one of the options from the tables of parts in sheet 2 and the rate and other costing info will follow into sheet 1, I select a quantity and excel calcs the total per item.

Wouldn't allow me to attach or upload the a file or pic.

Hope this makes sense and is possible.
Thanks

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Kalvinsmith18]

Thanks Glenn,

I've made a brief example and attached it.

Thanks

[WideBoyDixon]

Formula for H3:

Please Login or Register  to view this content.

Then copy down the sheet.

WBD

[Kalvinsmith18]

Nice one WideBoyDixon,

If I was to add another column next to the rate from sheet 2 that needed copied over to sheet 1, How would I do that?

Thanks

[WideBoyDixon]

That would be:

Please Login or Register  to view this content.

The ",0," part is the default value if B3 is empty.

WBD

[Kalvinsmith18]

Hi WideBoyDixon,

Thanks for the help man, it worked a treat.

I made an error though when designing it. The list on the example was tiny but the actual list is going to be pretty big so I have another question to ask.
How would I add a "sub-category" that when I select the option in the "description" it will only allow me to select from the options under that title?

I got the previous formulas working so I can sort that but just hit a bit of a dead end with this again.

I've attached a copy of a new example to explain what I meant above.

Thanks again