help with Microsoft Excel?

Question

I have created several drop down boxes in excel and would like the selected items from each dropdown box displayed on a new worksheet. Can anyone tell me how to do this please.

Thank you

Answer 1

I may be oversimplifying what you want to do, but if your results are in Sheet 1 in cells B2 and C2, then to display these on another sheet (say Sheet 2), you would have these formulas in Sheet 2:

='Sheet 1'!B2
='Sheet 1'!C2

Instead of typing these, you can click in Sheet 2 where you want the results to appear, type =, then click on Sheet 1's cell B2 to show those results.

Add more detail, or e-mail, if you're not getting what you need.
Cheers.
.

Answer 2

For people to be able to answer that question you really need to tell people what kind of drop down box that you are using. There are three different kinds of drop down boxes in Excel. There are ones created from the Forms Toolbar, which I don't recommend using. There are ones created from the Control Toolbox toolbar, which are very flexible and can be a good tool. Then there are ones that can be created from a list using validation data.

The validation data is the easiest one to reference because all you have to do is reference the cell that the drop down is in just like you would a regular cell.

To create a validation dropdown list, create the list somewhere in your workbook. Then name that cell range by typing a name in the Name Box (top left corner where the cell number appears). Then go to Data > Validation. Select the Settings tab. Under Allow select List. Then under source, type an equals sign then your range name.

If you are going to use the Control Toolbox dropdown, it's easiest to either add code to the dropdown to change the cell value that you want it to change, or to have it change a cell then have your others cells that you want to reference that value reference the cell that the dropdown selection changed.

The reason why I say not to use a dropdown from the forms toolbar is because it is really only pretty much included to accomodate really fairly old Excel files before VBA was incorporated into Excel. So that's pre- Excel 97. In a few those form controls may no longer be supported by Excel. Plus they do not give you the versatility that the ones created by using the Control Toolbox toolbar give you.

Answer 3

How are you creating the drop down lists? If you are using combo boxes from the Forms toolbar, then each combo box will be linked to a cell where it places the number of the selected item. You can use one of Excel's lookup functions (Lookup(), VLookup(), Index(), etc) to find the value in the list referenced by your combo box.

For example, let's say that your combo box gets the strings for the list from D1:D10, and its cell link is in E1. Then after selecting an item from the list its number will be placed in E1. You could then use:

=Index(D1:D10,E1,1)

To get the string that was selected. Obviously, you would put the Index() function on the other sheet, and it would reference the cells from the first sheet. So, on Sheet2 you might have:

=Index(Sheet1!D1:D10, Sheet1!E1, 1)

You would have a similar formula for each combo box.

Tim
http://www.tvmcalcs.com/