Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
I have created a simple drop down list. I would like the drop down arrow to
appear in the spread sheet all the time, without it being necessary to select the cell. Is this possible? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
Use a combo box from the Forms toolbar.
-- Biff Microsoft Excel MVP "tracy" wrote in message ... I have created a simple drop down list. I would like the drop down arrow to appear in the spread sheet all the time, without it being necessary to select the cell. Is this possible? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
OK, I got this far. Now the question is, How do I get the text to show that I
have selected in the drop down list, as oppose to a value? (1,2,3...) "T. Valko" wrote: Use a combo box from the Forms toolbar. -- Biff Microsoft Excel MVP "tracy" wrote in message ... I have created a simple drop down list. I would like the drop down arrow to appear in the spread sheet all the time, without it being necessary to select the cell. Is this possible? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
Ok, let's assume the source range for the combo box is I1:I10.
The linked cell is A1. Use this formula to get the actual selection: =IF(A1="","",INDEX(I1:I10,A1)) This is one of the drawbacks of using a combo box. You have to use an extra step to be able to actually use the selection. But.....if you want the drop arrow always visible this is what you have to do. A little trick is to put this formula and/or the linked cell in a cell that's under the combo box. That way it "appears" to work just like a data validation drop down list. All you'll see is the selection in the combo box. Here's a screencap to show how this would work: http://img209.imageshack.us/img209/9852/comboboxjw9.jpg In the screencap I use B3 for the linked cell and C3 contains this formula: =IF(B3="","",INDEX(I1:I10,B3)) As you can see the entries in both of those cells, B3:C3, are hidden because the combo box "sits" on top of them. Then, if you want to use the selection from the combo box in a formula you just need to refer to cell C3. -- Biff Microsoft Excel MVP "tracy" wrote in message ... OK, I got this far. Now the question is, How do I get the text to show that I have selected in the drop down list, as oppose to a value? (1,2,3...) "T. Valko" wrote: Use a combo box from the Forms toolbar. -- Biff Microsoft Excel MVP "tracy" wrote in message ... I have created a simple drop down list. I would like the drop down arrow to appear in the spread sheet all the time, without it being necessary to select the cell. Is this possible? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
This worked perfectly.Thanks for the help! "T. Valko" wrote: Ok, let's assume the source range for the combo box is I1:I10. The linked cell is A1. Use this formula to get the actual selection: =IF(A1="","",INDEX(I1:I10,A1)) This is one of the drawbacks of using a combo box. You have to use an extra step to be able to actually use the selection. But.....if you want the drop arrow always visible this is what you have to do. A little trick is to put this formula and/or the linked cell in a cell that's under the combo box. That way it "appears" to work just like a data validation drop down list. All you'll see is the selection in the combo box. Here's a screencap to show how this would work: http://img209.imageshack.us/img209/9852/comboboxjw9.jpg In the screencap I use B3 for the linked cell and C3 contains this formula: =IF(B3="","",INDEX(I1:I10,B3)) As you can see the entries in both of those cells, B3:C3, are hidden because the combo box "sits" on top of them. Then, if you want to use the selection from the combo box in a formula you just need to refer to cell C3. -- Biff Microsoft Excel MVP "tracy" wrote in message ... OK, I got this far. Now the question is, How do I get the text to show that I have selected in the drop down list, as oppose to a value? (1,2,3...) "T. Valko" wrote: Use a combo box from the Forms toolbar. -- Biff Microsoft Excel MVP "tracy" wrote in message ... I have created a simple drop down list. I would like the drop down arrow to appear in the spread sheet all the time, without it being necessary to select the cell. Is this possible? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "tracy" wrote in message ... This worked perfectly.Thanks for the help! "T. Valko" wrote: Ok, let's assume the source range for the combo box is I1:I10. The linked cell is A1. Use this formula to get the actual selection: =IF(A1="","",INDEX(I1:I10,A1)) This is one of the drawbacks of using a combo box. You have to use an extra step to be able to actually use the selection. But.....if you want the drop arrow always visible this is what you have to do. A little trick is to put this formula and/or the linked cell in a cell that's under the combo box. That way it "appears" to work just like a data validation drop down list. All you'll see is the selection in the combo box. Here's a screencap to show how this would work: http://img209.imageshack.us/img209/9852/comboboxjw9.jpg In the screencap I use B3 for the linked cell and C3 contains this formula: =IF(B3="","",INDEX(I1:I10,B3)) As you can see the entries in both of those cells, B3:C3, are hidden because the combo box "sits" on top of them. Then, if you want to use the selection from the combo box in a formula you just need to refer to cell C3. -- Biff Microsoft Excel MVP "tracy" wrote in message ... OK, I got this far. Now the question is, How do I get the text to show that I have selected in the drop down list, as oppose to a value? (1,2,3...) "T. Valko" wrote: Use a combo box from the Forms toolbar. -- Biff Microsoft Excel MVP "tracy" wrote in message ... I have created a simple drop down list. I would like the drop down arrow to appear in the spread sheet all the time, without it being necessary to select the cell. Is this possible? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
I wonder if someone can help me.....I have inserted a drop down box in my
spreadsheet and I want to have a blank line that allows the user to enter info (info that may not be on the drop down menu).....is this possible? If so, how do I do it? I would appreaciate any help in this matter! Thanks! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
If a Data Validation Dropdown you don't need a blank.
Under most conditions simply turn off the Error alert and user can type anything into the cell. For more info on not "most" conditions see Debra Dalgleish's site. http://www.contextures.on.ca/xlDataVal08.html#Invalid That entry will not become a permanent part of the list. For that you would need VBA. See Debra's site for a sample workbook. http://www.contextures.on.ca/excelfiles.html#DataVal DV0012 - Update Validation List -- type a new value in a cell that contains data validation, and it's automatically added to the source list, and the list is sorted; a macro automates the list updates. DataValListAddSort.zip 11 kb Gord Dibben MS Excel MVP On Tue, 1 Jul 2008 11:44:04 -0700, SGfla wrote: I wonder if someone can help me.....I have inserted a drop down box in my spreadsheet and I want to have a blank line that allows the user to enter info (info that may not be on the drop down menu).....is this possible? If so, how do I do it? I would appreaciate any help in this matter! Thanks! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
Thank you for the info. It helped!
"Gord Dibben" wrote: If a Data Validation Dropdown you don't need a blank. Under most conditions simply turn off the Error alert and user can type anything into the cell. For more info on not "most" conditions see Debra Dalgleish's site. http://www.contextures.on.ca/xlDataVal08.html#Invalid That entry will not become a permanent part of the list. For that you would need VBA. See Debra's site for a sample workbook. http://www.contextures.on.ca/excelfiles.html#DataVal DV0012 - Update Validation List -- type a new value in a cell that contains data validation, and it's automatically added to the source list, and the list is sorted; a macro automates the list updates. DataValListAddSort.zip 11 kb Gord Dibben MS Excel MVP On Tue, 1 Jul 2008 11:44:04 -0700, SGfla wrote: I wonder if someone can help me.....I have inserted a drop down box in my spreadsheet and I want to have a blank line that allows the user to enter info (info that may not be on the drop down menu).....is this possible? If so, how do I do it? I would appreaciate any help in this matter! Thanks! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
Ok, now I have a different problem. I have created a drop down box but the
info I'm using is on the same worksheet, but I want the info for the drop down box to be on a different page in the same worksheet. I tried using the instructions listted: "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.). Type the name for the cells, for example, ValidDepts. Press ENTER. 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." But after following those steps it did not put the list I had selected and named....it only put what i named the list with an equal sign after it (vendors=). Can someone please tell me how to do this? I'm pulling my hair out with this project!!!!! Thanks! SGfla. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
I meant "different page in the same workbook"
"SGfla" wrote: Ok, now I have a different problem. I have created a drop down box but the info I'm using is on the same worksheet, but I want the info for the drop down box to be on a different page in the same worksheet. I tried using the instructions listted: "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.). Type the name for the cells, for example, ValidDepts. Press ENTER. 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." But after following those steps it did not put the list I had selected and named....it only put what i named the list with an equal sign after it (vendors=). Can someone please tell me how to do this? I'm pulling my hair out with this project!!!!! Thanks! SGfla. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
Actually you meant different "worksheet" in the same workbook.
Workbook don'ts have "pages". Gord On Wed, 2 Jul 2008 11:20:01 -0700, SGfla wrote: I meant "different page in the same workbook" "SGfla" wrote: Ok, now I have a different problem. I have created a drop down box but the info I'm using is on the same worksheet, but I want the info for the drop down box to be on a different page in the same worksheet. I tried using the instructions listted: "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.). Type the name for the cells, for example, ValidDepts. Press ENTER. 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." But after following those steps it did not put the list I had selected and named....it only put what i named the list with an equal sign after it (vendors=). Can someone please tell me how to do this? I'm pulling my hair out with this project!!!!! Thanks! SGfla. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
Did you name the listrange on the second sheet as "vendors"(no quotes) referring
to your list of items? In the DV sheet did you enter =vendors in the source dialog? Gord On Wed, 2 Jul 2008 11:18:01 -0700, SGfla wrote: Ok, now I have a different problem. I have created a drop down box but the info I'm using is on the same worksheet, but I want the info for the drop down box to be on a different page in the same worksheet. I tried using the instructions listted: "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.). Type the name for the cells, for example, ValidDepts. Press ENTER. 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." But after following those steps it did not put the list I had selected and named....it only put what i named the list with an equal sign after it (vendors=). Can someone please tell me how to do this? I'm pulling my hair out with this project!!!!! Thanks! SGfla. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
Sheesh!!
On Wed, 02 Jul 2008 14:04:40 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Workbook don'ts have "pages". |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
Gord,
Thank you very much for your help! That is exactly what I needed and it worked! SGfla "Gord Dibben" wrote: Did you name the listrange on the second sheet as "vendors"(no quotes) referring to your list of items? In the DV sheet did you enter =vendors in the source dialog? Gord On Wed, 2 Jul 2008 11:18:01 -0700, SGfla wrote: Ok, now I have a different problem. I have created a drop down box but the info I'm using is on the same worksheet, but I want the info for the drop down box to be on a different page in the same worksheet. I tried using the instructions listted: "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.). Type the name for the cells, for example, ValidDepts. Press ENTER. 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." But after following those steps it did not put the list I had selected and named....it only put what i named the list with an equal sign after it (vendors=). Can someone please tell me how to do this? I'm pulling my hair out with this project!!!!! Thanks! SGfla. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list help
Good to hear.
Thanks for the feedback Gord On Thu, 3 Jul 2008 04:28:00 -0700, SGfla wrote: Gord, Thank you very much for your help! That is exactly what I needed and it worked! SGfla "Gord Dibben" wrote: Did you name the listrange on the second sheet as "vendors"(no quotes) referring to your list of items? In the DV sheet did you enter =vendors in the source dialog? Gord On Wed, 2 Jul 2008 11:18:01 -0700, SGfla wrote: Ok, now I have a different problem. I have created a drop down box but the info I'm using is on the same worksheet, but I want the info for the drop down box to be on a different page in the same worksheet. I tried using the instructions listted: "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.). Type the name for the cells, for example, ValidDepts. Press ENTER. 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." But after following those steps it did not put the list I had selected and named....it only put what i named the list with an equal sign after it (vendors=). Can someone please tell me how to do this? I'm pulling my hair out with this project!!!!! Thanks! SGfla. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
how do I use one drop-list to modify another drop-lists options? | Excel Discussion (Misc queries) | |||
Drop Down List choice selecting another drop down list | Excel Worksheet Functions | |||
how do I link a drop down list entry to a new drop down cell? | Excel Discussion (Misc queries) | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) |