[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Enter input in a cell based on selected option in a drop list in another cell (Excel 2003)

Hi everybody,

As a translator, I have worksheets on which I manage my translation projects. In those worksheets, one cell is dedicated to my client's name. Another cell is for the initials of the project manager for the current project. Each client has more than one project manager. Currently I have to manually select the client's name from a drop list (which values are from a range of cells in another worksheet) and the PM's name from another drop list (which values are also from another range of cells in another worksheet).

I would like that the second list (PMs' one) would be dynamically populated depending on the client's name selected in the first drop list.

How can I achieve this?

Many thanks in advance for any helpful answer.

Cheers.

PG
0
pg111
Asked:
pg111
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
You can do what you want. Below are some techniques.

To create a drop-down menu for a cell where the source list will vary in length select the cell and then choose the menu command Data->Validation to open the Data Validation dialog box. Choose List from the Allow drop-down list. In the Source text box enter the following formula:

   =OFFSET(A1,0,0,SUMPRODUCT((A1:A100<>"")*1),1)

where A1 is the first cell in the list of values and A1:A100 is the array of cells representing the largest possible list. If the range of drop-down list values has been named then use this formula:

   =OFFSET(DropdownValues,0,0,SUMPRODUCT((DropdownValues<>"")*1),1)

Note that SUMPRODUCT is used instead of COUNTA because COUNTA counts cells with formulas even though the formula result is empty.

To use a list that includes only values associated with a specific key:

   =OFFSET(B1,MATCH(C1,A1:A100,0)-1,0,COUNTIF(A1:A100,C1),1)

The example assumes there are two lists, one with keys (column A) and a second with list values (column B), and that each key is contiguous. The formula assumes a key is entered into C1.

To use a list on another worksheet the INDIRECT function must be used since Excel does not allow direct references to other worksheets in a validation list formula:

   =OFFSET(INDIRECT("'Other Sheet'!A1"),0,0,COUNTA(INDIRECT("'Other Sheet'!A:A")),1)

To use a list on another worksheet that includes only values with a specific key:

   =OFFSET(INDIRECT("'Other Sheet'!B1"),MATCH(A1,INDIRECT("'Other Sheet'!A:A"),0)-1,0,COUNTIF(INDIRECT("'Other Sheet'!A:A"),A1),1)

To use a list on another worksheet that includes values in a column identified by a specific header identified by the value in A1:

   =OFFSET(INDIRECT("'Other Sheet'!A2"),0,MATCH(A1,INDIRECT("'Other Sheet'!A1:C1"),0)-1,COUNTA(OFFSET(INDIRECT("'Other Sheet'!A:A"),0,MATCH(A1,INDIRECT("'Other Sheet'!A1:C1"),0)-1))-1,1)

The example assumes there are two columns on the source worksheet, one with keys (column A) and a second with list values (column B), and that each key is contiguous. The formula assumes a key is entered in cell A1.

To use a list on another worksheet where the worksheet is entered in cell A1:

   =OFFSET(INDIRECT("'" & A1 & "'!A1"),0,0,COUNTA(INDIRECT("'" & A1 & "'!A:A")),1)

To use the above formulas with the ListFillRange of a combo box or a list box, a named formula must be used. Choose the menu command Insert->Name->Define. In the top text entry field enter a name for the formula such as "MenuList". In the bottom text entry box enter one of the above formulas. Use absolute cell addressing ($A$1) to ensure that the cell references are not adjusted by Excel. Click Add and then click Done. Set the ListFillRange property to the value used to name the named formula. This technique works with both ActiveX and Forms controls.

Kevin
0
 
pg111Author Commented:
Thanks Kevin. You're quick on the draft!
I will look into your answer more carefully later and certainly revert to you with questions or just congrats!
Thanks again.
0
 
pg111Author Commented:
The matter is complex. Moreover I had to "translate" US Excel formulas into French ones. But the explanations were both accurate and comprehensive. Thanks and congrats to Kevin!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now