Drop down menu worksheet that returns values to worksheet?

[Regulluz]

I have this workbook - that should have been a DataBase - in this new job I have, where every item properties are store in a Master worksheet. There are 3 additional worksheets in the same workbook (formatted as a big grid, crosswords type of thing so that it fits as many items as possible on a single printable sheet)that draw data from Master for calculation of price, total boxes, weight, etc. It is pretty straight forward as item quantity in Master gets it's value from the item's qty cell in either one of the other 3 sheets.

What I want is having Invoice type of worksheet, rather than the crosswords that draws data from the same Master table but in a drop down menu fashion. Then, whatever item is selected in the new Invoice get's its quantity value sent back to Master's quantity for weight and boxes calculations as it is done now.

I have attached an info graphic with an attempt to explain a bit more clear what this is about.

I know I should move to a DB - and I am working on it as well - but it takes more time to have it working properly (or so I think) I believe there is a way to have this thing done quicker and have my boss happy while I work on the DB with patience. Am I believing too much?

Million thanks for your time!
Regards,
Regulluz

 

[SkipVought]

hi,

Many of us cannot download due to company security.

So you want a sheet where you can select an InvoiceID from a drop down and have the item detail displayed?

If so, I'd use the Data > Validation -- LIST feature for the drop down and MS Query as a parameter query, to return the detail for the selected value.

faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Regulluz]

Hello Skip!

Thanks for the quick reply!
The file is a link to a picture. It should open in your browser.

I will check into that and let you know. I would like to select an ItemID and have the item details displayed and fill up an Invoice row by row like this. Then, whatever quantity for each item I input on this worksheet gets returned to the sheet where the drop down menu items came from.

Lets see if this code works so that you can see the graph:

 

[SkipVought]

I am prevented from viewing your .jpg or any other file you post.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Regulluz]

Oh wow! I am cursed! :-( I don't see a privacy setting on my profile. Oh well!

I'm currently checking your suggestion of MS Query and hopefully that's my answer. Will report back! Thank you very much for this, man.

In the meantime, will a Flickr link work?

http://www.flickr.com/photos/23967880@N06/6358600535/

 

[SkipVought]

Nope!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Regulluz]

Hi!

I'm still reading but... when I finish the query, I get all of my data in the Sheet, not a drop down menu. How do I make it to display a drop down menu in each row and then fill extra cells in the same row with the info of the item selected on the first row drop down?

Thanks again and will keep u posted with my "findings".

 

[SkipVought]

first, on a separate sheet, create a querytable that returns a distinct list of invoices. Or you can use the Advances Filter to return a unique list. Use THIS LIST as the source of your Data > Validation -- LIST.

When you format the query on the display sheet with your drop down, make it a PARAMETER query where the criteria for your invoice looks like [highlight][What Invoice?][/highlight]. When you return data to excel, you will have an opportunity to decide how to assign this parameter. Choose the CELL containing your drop down, that will UPDATE when your change that value.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Regulluz]

Thanks. On lunch now. I'll try that later.
I was thinking that, maybe this invoice could be created from the masters sheet, by using the value on the qty colum of the MAsters table. I use that to generate a print report of the ordered items but the code only works on Print Preview.

U think it could work for generating a new worksheet?
Thanks.

 

[SkipVought]

I am proposing a NEW sheet, on which you can return the detail for any InvoiceID reflected on your Master sheet.

Each time your select another InvoiceID, that invoice's detail is returned to that sheet in the existing QueryTable that you will have add initially.
[tt]
1) Select InvoiceID
2) Observe that invoice's detail
[/tt]
...that simple!


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]