Home

input from drop down list

prprewitt
Occasional Contributor

I created a check register table that I would like to select the transaction name from a drop down list that uses a table from a separate sheet.  I have looked at combo and list box - not what I wanted.  Then tried Data Validation - did not work but closer to what I was looking for.  I also use this workbook to track my credit card transactions and would like to use the same list table.

12 Replies

Data validation with a drop-down list is probably the best option and I'm wondering if you just missed a step & that's why it didn't work. Here's a video I made that explains how to do this, so hopefully that helps you accomplish what you're wanting to do: https://youtu.be/8sdFsXfj25Y

This tutorial demonstrates how to create a drown-down list in Excel using data validation. In this video, we are creating a simple HR employee hire form and limiting the user to selecting numeric months and years from a drop-down list on a separate tab. The tab can be hidden from the workbook once
Assuming the label in A1 is Transactions and your list starts in A2, define TransactionNames as a dynamic named range for your list with this formula:
=$A$2:INDEX($A:$A,COUNTA($A:$A)
To create the drop-down list, press Alt,A,V,V then in the Source box, type:
=TransactionNames
Press Enter and you’re done.

Thanks for the help.  My list is a table named 'Transaction' in a sheet named 'ListTable'.  Will that work as a dynamic named range?

You have to test it by adding new items to the list and see if the new items are included in the drop-down list. If included, then your list is dynamic.

@Twifoo 

Thanks again for your help.  As I go through the steps to add the drop down list using data validation, it will not let me go to the sheet where the list is.  Also, where do I input the formula you gave me earlier?  I have made many careful attempts with no success.

Press Ctrl+Alt+F3, then in the Refers to box, enter the formula for the defined name.

@Twifoo 

In order to experiment with Data Validation, I created a new workbook with a worksheet named 'DV' that contains a table with (3) column headings; DATE, TRANSACTION & NOTE.  I added a 2nd worksheet named 'List' that contains a table with the heading 'LIST' and several sample transaction names.  Both tables were created using 'Insert > Table...'

 

I successfully created a dynamic list named 'DLIST'.  When I type =DLIST in the source box I get a 'Source evaluates to an error...'.  If I type =List!DLIST (I am thinking that it could not find the worksheet with the DLIST) then I get a 'Cannot reference other worksheet...' error.

The cryptic structured references, along with the error you are experiencing, are the reasons why, like Bill Jelen, I am also not a fan of Excel Tables. See his article here:
https://www.mrexcel.com/excel-tips/i-am-not-a-fan-of-excel-tables/

@Twifoo 

Thanks, I finally got it to work.  If the worksheet is named 'List' and the data is in column A row 2-10, the formula in the source box is =List!$A$2:$A$10  Unfortunately, if I add new items to the list it is not updated (I have Excel 2007).  Evidently this has been addresses in version 2010.

Select A2 in your List sheet, then follow my instructions in my first reply.

@Twifoo 

Thanks, the formula you gave me in your 1st reply works great.  By the way, you need another ) at the end for it to work.  Still does not update the list when you add new items.  It apparently does not re-count the lines when you add new items.  Is there a way to re-calculate the formula automatically?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies