input from drop down list

[prprewitt]

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.

[#2]

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

[#3]

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.

[#4]

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

[#5]

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.

[#6]

@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.

[#7]

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

[#8]

@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.

[#9]

This link may help you resolve the error:
https://www.myonlinetraininghub.com/excel-tables-as-source-for-data-validation-lists

[#10]

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/

[#11]

@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.

[#12]

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

[#13]

@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?