Multiple cell drop down list

I'm a bit of a basic Excel user and need some help creating drop down list that will populate multiple cells. Attached is a example of what I have. On the first page, I need to populate the 3 cells in the middle with info from the second tab. I then need every row on the first tab to have this drop down. Any help would be appreciated.

Thanks

Hello & Welcome to the Board,

Maybe this will help...

http://www.contextures.com/xldataval01.html

Hi aojohnson
I'd think you'd want to use Item Code for your dropdown since those codes appear to be unique. So, the attached is based on that assumption. Included in the workbook is a dynamic named range titled "Item_Code" along with this code

Please Login or Register  to view this content.

Let me know of issues.

Hi aojohnson
You don't need to do anything special. Open the book I posted and allow macros. Select an item from the dropdown in column B, columns C and D will be populated.

Please explain this

I do need to have the item description be the drop down list

You've 8 items labeled "ANGLE WALL CABINET 1-DRAWERS". How is the user to know which they're selecting? Based on your explanation, a different approach may be required.

Hi aojohnson
With 1300 items, what would be your ideal solution? What have you considered? What have your users considered?

Seems to me, Item Code is the way to go...it's unique...and should be part of your pick list (or whatever you're using). There's a lot of things we can do with user forms including Auto Complete such that when a user types in a part number (or a description) their choices narrow.

You (and your users) think through the process and figure out what would make things simpler. I'll help you if I can.

Originally Posted by jaslake

Hi aojohnson
With 1300 items, what would be your ideal solution? What have you considered? What have your users considered?

Seems to me, Item Code is the way to go...it's unique...and should be part of your pick list (or whatever you're using). There's a lot of things we can do with user forms including Auto Complete such that when a user types in a part number (or a description) their choices narrow.

You (and your users) think through the process and figure out what would make things simpler. I'll help you if I can.

John,

Thanks for your help on this so far. I've spent some time today working out what I need to accomplish with this. I am going to stick with the Item code like you have it. The Auto Complete would be very helpful though with the number of items involved. The only other items I need to have work are the four colored areas at the bottom. I've created additional worksheets with the data to go in these fields. The text field would be the ideal drop down cell. Is this doable?

Hi aojohnson
I'm at a loss where you're going with this. Try the attached. If it's in the same country perhaps we can work with it to make it work for you.

Hi aojohnson

Hi aojohnson
The attached has a modified type ahead feature. Instructions are included. I still don't understand the markup for door style being marked up on the entire project, but, if it works for you, works for me.

I see your profile is Excel 2007. The procedures perform as expected in 2007. Excel 2000 DOES NOT like it.

Let me know of issues.

Hi aojohnson
Glad it works for you. If that satisfies your need, please mark your thread as "Solved".

To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

John,

Spreadsheet has been working great until recently i switched from Excel '07 on Windows to Excel '11 on Office for Mac 2011. When I try to double click on the cell to do the auto-fill search, I get error messages. I've attached a screen shot of what happens as well as a copy of the excel file that is not corrupted. It seems once i've gotten this error, it corrupts the file and will not even let me double click to auto-fill the cell in MS Office Excel. Any ideas??

Thanks

Hi aojohnson
It would seem the issue is related to your new environment. I actively use a Mac but run Excel on a virtual machine using VMWare Fusion software so, I have no way to duplicate your environment. Sorry.

Hi aojohnson
Yes it does. The CombBox you see on double click in a Column B cell is an ActiveX Control.

Hi aojohnson
According to this link, that's not likely to happen. http://www.officeformac.com/ms/ProductForums/Excel/7772

Sorry.

Hi aojohnson

So, you want just a drop down list to choose from in the Column B cells? Sure, that's fairly simple. I'll get rid of the ComboBox and initiate Data Validation for Column B. Post something later.

Hi aojohnson
Try the attached. Let me know of issues.

My pleasure...happy to be of help. Best of luck on the Mac side. Haven't worked with it much at all. Have a Mac 'cause my children are enamored with them...so, I bought one. But, also bought VMWare Fusion for me and for the children...sorta' defeated the purpose I suppose...it's working for me...not certain about the children.

Hi aojohnson
On the PC side, Item_Code is a named dynamic range

Please Login or Register  to view this content.

I would assume the same applies on the Mac side but honestly don't know. You can check this by looking at the formula. Assuming there are no Mac specific limitations, the results should be the same.

I'm really sorry I can't be more specific but I have no knowledge in Excel for the Mac.

Hi aojohnson
See if this works for you.

Hi Aaron
Regarding this

if the bottom section would function just like the top section

I assume you're referring to columns B & C, rows 47 through 54. If so, you can do that by simply copying Columns B & C, Row 40 then paste to rows 47 through 54. It'll paste the validation to those new rows.

Regarding the "Clear Button", I'll need to look at an old version to see what we were doing. Get back to you.

Hi Aaron
Sorry, didn't notice you'd moved the "Clear Button"...thought you'd eliminated it. Replace the "Clear Button" with this

Please Login or Register  to view this content.

Let me know of issues.

You're welcome...glad to be of help.

Hi Aaron
I can pretty well guess what you did but I'd like you to detail it for me. If you did as I guess, there's a way to "fix" it.

The procedure is built around Dynamic Named Ranges...you can add or modify items within those Named Ranges...you can even delete items within the Named Range. However, you can't DELETE the ENTIRE range unless you redefine it.

So, detail what you did and I'll explain how to fix it (case you do it again). Also, try as I suggested...modify and/or add items to other categories...satisfy yourself that it works.

Hi Aaron
Look at your "Named Ranges" (in Excel 2007...Formulas --> Name Manager...don't know where it is in Mac Excel...look for it). You'll see that Modifications has #Ref next to it. Basically, you've deleted the reference. I'll explain how to recapture the Reference. In the meantime, try this

try as I suggested...modify and/or add items to other categories...satisfy yourself that it works

I want you to be comfortable that the Dynamic Named Ranges do work. Let me know your reaction. In the meantime, I'll detail how to fix the issue you've described.

Hi Aaron

Let me know if you found your "Named Ranges" in your Mac Version of Excel...it's key to fixing the problem should you have it again.

Bingo, Fixed. Had no idea we were using "Named Ranges". Have had some experience with them in the past. Fixed the REF issue and everything is working correctly. Now I know how to fix in the future.

Thanks a lot!

Have added a couple screen shots showing where the Name list is. Figured from the others what the column letter needed to be.

Good work Aaron...you're traveling the road we all do...fun and gratifying isn't it?

Aaron, I believe you know what powerful tools you have available to you to make your work life and those that work with/for you easier and more productive.

This old guy will help you however he can. Good hunting!!

I am trying something along the same lines, but slightly different. I have to admit I am a noob at programming in Excel. What I would like is the person (in this case me) to select the area the work is being performed in, and the system fills in labor, #of operators and overhead in the cells following it, pulling the data from the Info tab.

Hi RFox

Welcome to the Forum.

Unfortunately, you've posted a question in another OP's thread...this doesn't sit well with Forum Rule #2

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

In addition to that, this thread has been marked as solved and will probably not get much traffic. I'd suggest you start a new thread for your issue.

Hi Eisebbeiss

Welcome to the Forum!!

Unfortunately, you've posted a question in another OP's thread...this doesn't sit well with Forum Rule #2
2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

In addition to that, this thread has been marked as solved and will probably not get much traffic. I'd suggest you start a new thread for your issue.

Start your own Thread...one of us will help/

clintpaulsen,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.