In excel, how can I use a dropdown list to associate common inputed info with its respective worksheet equat?

Question

This may sound confusing so I will explain it the best I can.
I have an excel input table that reflects a work schedule. I have another sheet that is populated from this table, to create a work schedule graph. There is a different worksheet for each supplier's work graph.
What I am trying to figure out how I can have a dropdown box for suppliers on the input page that, depending on the supplier chosen, will connect the following information, ie tons pipe, with its designated supplier worksheet.

Project Supplier Tons Pipe
1 Red DDR 50

Here is an example, the cell under the supplier would contain a dropbox for a few different suppliers. I choose the DDR supplier so the 50 tons of pipe would automatically be connected from an equation on DDR supplier worksheet. Can this connection change based upon the supplier dropdown box selection. The actual equation would remain on the respective supplier worksheet. Is this simple or am I thinking about it too much?

Answer 1

If you want a pull-down menu on Sheet1!A1 (for example) and based on the selection you want cell Sheet1!B1 to look up and fill in info from cell D14 on another sheet (DDR!D14 say). If that's right, then the easiest way to do this is:



Name the supplier sheets with the EXACT same names you use in the pull-down menu.



Create the pull-down menu: highlight cell A1, go to:

>Data >Validation >Allow: List >Source: type in the sheet/supplier names: DDR, SupplierX, etc



In cell B1, type in:

=INDIRECT("'"&$A$1&"'!D14")



If that isn't what you need, let us know with some additional information.

Answer 2

Electron orbitals and their shape are actual wave purposes that fulfill the Schrodinger equation. the type is a complicated function expression of the wave function, and the quantum numbers (n, l, m) that pop out of the function take place by means of boundary situations placed on the electron around the atom and different electrons. i think that for the time of basic terms the answer to the hydrogen atom is accepted precisely, for different atoms the interplay of electrons is particularly complicated so the orbitals are in basic terms solved numerically or approximately. when you consider that for electrons the Pauli exclusion concept applies each and every pair of electrons desires a distinctive set of quantum numbers and this makes the form of the s and p sub-shells of electrons.

Answer 3

so what you want are nested pick lists.. the second list populates by the first list..



you will have to create the second lists first.. give each one of those lists a unique name in excel for the cells...

then create the first drop down list as the names of the second lists...

Answer 4

Create a drop-down list from a range of cells

Show All

Hide All

To make data entry easier, or to limit entries to certain items that you define, you can create a drop-down list of valid entries that is compiled from cells elsewhere on the worksheet. When you create a drop-down list for a cell, it displays an arrow next to that cell. To enter information in that cell, click the arrow, and then click the entry that you want.



To create a drop-down list from a range of cells, use the Validation command under the Data menu.



To create a list of valid entries for the drop-down list, type the entries in a single column or row without blank cells. For example: A

1 Sales

2 Finance

3 R&D

4 MIS



Note You may want to sort the data in the order that you want it to appear in the drop-down list.



If you want to use another worksheet or another workbook, do one of the following:

Use a different worksheet in the same workbook Type the list on that worksheet, and then define a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) for the list.



How?



Select the cell, range of cells, or nonadjacent selections (nonadjacent selection: A selection of two or more cells or ranges that don't touch each other. When plotting nonadjacent selections in a chart, make sure that the combined selections form a rectangular shape.) that you want to name.

Click the Name box at the left end of the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.).





Name box



Type the name for the cells, for example, ValidDepts.

Press ENTER.

Note You cannot name a cell while you are changing the contents of the cell.



Use a different worksheet in a different workbook Type the list on that worksheet, and then define a name with an external reference to the list.



How?



Open the workbook that contains the list of drop-down entries.

Open the workbook where you want to validate cells, point to Name on the Insert menu, and then click Define.

In the Names in workbook box, type the name, for example, ValidDepts.

Accept the default value in the Refers to: box, and then click OK.

In the Refers to box, delete the contents, and keep the insertion pointer in the box.

On the Window menu, click the name of the workbook that contains the list of drop-down entries, and then click the worksheet that contains the list.

Select the cells containing the list.

In the Define Name dialog box, click Add, and then click Close.

Notes



If several users need to open the workbook simultaneously, set the workbook to read-only recommended when you save it. For more information, see Prompt to open a file as read-only.

The workbook must be open for users to use the validation list. You can record a macro to open it automatically whenever the workbook with the data validation is opened. For more information about creating and using macros, see About macros in Excel.



Select the cell where you want the drop-down list.

On the Data menu, click Validation, and then click the Settings tab.

In the Allow box, click List.

To specify the location of the list of valid entries, do one of the following:

If the list is in the current worksheet, enter a reference to your list in the Source box.

If the list is on a different worksheet in the same workbook or a different workbook, enter the name that you defined for your list in the Source box.

In both cases, make sure that the reference or name is preceded with an equal sign (=). For example, enter =ValidDepts.



Make sure that the In-cell drop-down check box is selected.

To specify whether the cell can be left blank, select or clear the Ignore blank check box.

Optionally, display an input message when the cell is clicked.



How?



Click the Input Message tab.

Make sure that the Show input message when cell is selected check box is selected.

Type the title and text for the message (up to 225 characters).

Specify how you want Microsoft Office Excel to respond when invalid data is entered.



How?



Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.

Select one of the following options for the Style box:

To display an information message that does not prevent entry of invalid data, click Information.

To display a warning message that does not prevent entry of invalid data, click Warning.

To prevent entry of invalid data, click Stop.

Type the title and text for the message (up to 225 characters).

Note If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."



Notes



The width of the drop-down list is determined by the width of the cell that has the data validation. You may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.

The maximum number of entries that you can have in a drop-down list is 32,767.

If the validation list is on another worksheet in the same workbook or another workbook and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information, see Display or hide a workbook or sheet and Overview of security and protection in Excel.



Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.



--------------------------------------------------------------------------------

How can I make a drop down box visible within a worksheet?

How do I chg the text size in my validation drop down box?

How do you format data that is part of a drop down menu?

Ho Do I create drop down menu?

Increase number of items shown in drop down list?

Can you make a drop down list in a drop down list?

Is it possible to create a dropdown list with pictures/icons ?

How do I enter a list in a Combo box?





See Also

Buy Microsoft Office 2007

Cannot use external references with data validation

Customize your toolbars and menus

Free trial of the 2007 Microsoft Office system

Overview and samples of data validation

Prevent invalid data entry in a worksheet

Troubleshoot data validation







Was this information helpful?







Please tell Microsoft how this information was helpful (optional): Please tell Microsoft how we can make this information more helpful (optional):

Please tell Microsoft what you were trying to do (optional):











Your feedback is being submitted.



Thank you for submitting your feedback to Microsoft.

Change my feedback

Although Microsoft cannot respond to every comment, we do use your feedback to improve the information we provide.