How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4480
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

How do you create a single drop down box use a cell range on

Resolved Question:

How do you create a single drop down box use a cell range on a different worksheet
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Jess M. replied 1 year ago.
Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

If you are using Excel 2007 or 2010, you just need to go to Developer tab, click on Insert then click on Combo Box Form Control. Here is my screen shot:

Then you draw your combo box and then right click on it then click on Format Control.


Then in the Input Range, click on the small icon with the red arrow to minimize the box, then click on the other sheet where the data are stored.



After selecting the sheet and the data, click on the icon with the red arrow again, or simply press Enter. Then click OK.



Then when you go to the combo box, the data selected will be shown as pull down values.

Click on this link to download the sample file I made for you:
https://dl.dropboxusercontent.com/u/22865503/SampleComboBox.xlsx

I hope that helped. If you have time to rate my service, that is highly appreciated!

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4480
Experience: Computer Software Specialist for more than 10 years
Jess M. and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.


I very sorry that I wasted your time by not providing enough information.


I am using Excel 2003


And to restate the question:


How do you create a single drop down box to fill multiple cells using a range of entries on a different worksheet

Expert:  Jess M. replied 1 year ago.
Dennis,

Can you elaborate the problem? Or give me a dummy file?
Customer: replied 1 year ago.

Worksheet #2















Pkg Typelbs/unitCost/unit
standards!l69:l811,800#$18.00

The cell below package type needs to be a drop down box from Worksheet #1


 


Worksheet #1

























































































Packaging
Packaging TypePounds/unitCost/unit
Skids1,350.00$18.00
Power Packs1,100.00$34.00
Cartons35.00$1.50

based on the selection in the drop down box, the next two cells need to be filled in by using the related columns


 


I hope this makes it clearer

Expert:  Jess M. replied 1 year ago.
Dennis,

Are you saying that the options in the dropdown box are Skids, Power Packs, and Cartons? And when a selection is made, the corresponding values will be shown like:
1,350.00 $18.00
1,100.00 $34.00
35.00 $1.50

So for instance, when the seleciton is Cartons, the values 35 and 1.5 are shown?

Please confirm.
Jess
Customer: replied 1 year ago.

correct


 

Expert:  Jess M. replied 1 year ago.
Dennis,

Thank you for that confirmation. In this case, here is what you can do:
  1. In worksheet 1, you need to add a Column to hold the selection done in ComboBox. That means that your columns A, B, C, and D will be
    • Pkg Type
    • Added Column, can be labeled as "Selection"
    • lbs/unit
    • Cost/unit
  2. Point the Combobox Cell value in this selection cell. That is, if item 1 is selected in the box, 1 will be displayed. If 2 is selected, 2 is shown and so with 3. Now, you have only 3 options so the values can be 1, 2 or 3.
  3. Now in the Sheet 2, add a column before the 3 original columns and name it as Item No or whatever you choose. The values should be 1, 2, or 3. This is used as the index value for the Vlookup function.
  4. Now, create the combobox and the input range shall be the data in column B
  5. Point the Cell link to B2 in sheet 1
  6. You can Hide this added Selection column if you want
  7. Now, when the combobox is working, add the following formula for the lbs/unit and Cost/unit columns.
  8. For lbs/unit column:
    =VLOOKUP(B2,Sheet2!A4:D6,3,FALSE)

  9. For Cost/unit column:
    =VLOOKUP(B2,Sheet2!A4:D6,4,FALSE)
  10. This simply means that if the selection is 1, the vlookup function will pull up the corresponding value for Skid. It will display the third column (3 in the formula) if it is for Pounds and column 4 if it is Cost columns respectively.

Here is the link for the file I made for you.

https://dl.dropboxusercontent.com/u/22865503/SampleComboBox.xls

 

For more information on how to add combobox, since you do not have the Developer tab in Excel 2003, click on View then point to Toolbars then click Control Toolbox. That is where you can add the ComboBox.

 

I hope that helped. If you have time to rate my service, that is highly appreciated!

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess

Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4480
Experience: Computer Software Specialist for more than 10 years
Jess M. and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

Not sure what you mean by "Point the Combobox Cell value in this selection cell"

Expert:  Jess M. replied 1 year ago.
Dennis,

Thank you for writing back. Have you downloaded the sample file I made for you? What I meant by ""Point the Combobox Cell value in this selection cell" means that you create a new column, B in my example to hold the selection. Here is my screen shot:


That "3" shown in the screen shot represents the "selection" made in the combo box. If it is Skids, it shows 1, if Power Packs, it shows 2 and 3 for Cartons.

Now, to make cell B2 in Sheet 1 display these 1,2 or 3 values, you need to set it as a Cell link of the combo box. Right click on the combo box, click Format Control. Click the box for Cell Link link to insert the cursor there:


Then click on cell B2 to select the cell. The cell reference shall be shown in Cell Link as shown:


Now click OK. That is the time where 1, 2 or 3 is shown in B2 when you select the options in the combobox.

I hope that helped. If you have time to rate my service, that is highly appreciated!

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4480
Experience: Computer Software Specialist for more than 10 years
Jess M. and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

Sorry it has taken a while to get back to you. The screen shots were a big help in finding out what is wrong.


My main problem now is that your screen shot of Format Control is not the same as mine.


You have 5 selections


Size


Protection


Properties


Web


Control


Mine only has the first 4 tabs, the Control tab is missing


Is there anyway to add that tab?

Expert:  Jess M. replied 1 year ago.
Hi Dennis,

Thank you for writing back. I am sorry for the confusion. The combo box needs to be a form control. Click on View menu then point to Toolbars then click on FORMS (not Control Toolbox). All the tabs are there where you can follow the steps I gave you. That is where you can add the Combobox, the one that I used in my sample.

I hope that helped. If you have time to rate my service, that is highly appreciated!

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4480
Experience: Computer Software Specialist for more than 10 years
Jess M. and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

It has taken a bunch of time to grasp the concepts, but it finally looks like I have gotten it to work


Two more question regarding the combo box


1) Is it possible to have the dropdown arrow hidden until you click on the cell?


The issue here is that changing column widths is problematic due to the fact that the column names for a single column change back and forth between 4 different column names with the maximum cell entry width being different under each column name. So to try to keep the spreadsheet from getting to wide, I have to juggle where the various entries are placed.


2) Is it possible to make the combo box with a height smaller than .22?


I am able to make it bigger, but if I try to make it smaller it simply reverts back to .22. If not, then I would have to increase the row height to match the combo box height.


 


Thanks for all the help you have been giving me, It is very much appreciated and you will be getting an excellent rating

Expert:  Jess M. replied 1 year ago.
Dennis,

I am very sorry but you cannot hide the combobox, and you also cannot make the height smaller than the minimum size of .22 since that is the minimum size or height that a combobox is designed to display. You can only make it bigger but you cannot make it smaller than .22" height.

I hope that clarifies.

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4480
Experience: Computer Software Specialist for more than 10 years
Jess M. and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

Thank you very much

Expert:  Jess M. replied 1 year ago.
You're always welcome! I am glad to be of help. If you need assistance in the future, you can request me or ask a question "For jessmagz" so that I can help you immediately.

Best regards,
Jess
Expert:  Jess M. replied 1 year ago.
Hi Dennis,


I'm just following up with you to see how everything is going. Did my answer help?


Let me know,
Jess
Customer: replied 1 year ago.

I was a great help, it took a while to get there and I learn a lot on the way.


Thanks for you for your patience with me.

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 
Chat Now With A Microsoft Office Technician
Jess M.
Jess M.
330 Satisfied Customers
Computer Software Specialist for more than 10 years