Table not showing up in data validation source

[FrumpyJones]

I made a tracker to get questions asked by our New Hires. I decided that to create a good pivot table for reporting, I wanted to have three of columns be limited to a list (Support Staff Name, Agent Name, and Reason).
Made a hidden sheet, populated three columns in that sheet, made the data validation.
All worked well.

Then I had to add and delete data from the validation source lists. This made me have to constantly remember to update the source range for the data validation list
Small pain in the butt.

Then I got "stupid smart" and decided to just make my data source columns into tables. I could then set the range of the validation to the table and never have to worry about adjusting after adding or deleting.
Created a table for each column (not the full column, but enough of a buffer of empties so adding goes easy), named the tables (no spaces, each different and easy enough to remember).
Here's where things get odd.

Only two of the three tables show up when I hit f3 on the source of the range in data validation. I have no idea what I've done wrong. I even tried to manually entering the table name (Following what it looked like after I made the selection with f3 for the other two) but still no good.

I put a copy of the file on my google drive for all of you to have a look at and show me where I done did wrong. Here is the excel File

Thanks

 

[Georgiboy]

I can't look at the file as my system at work will not let me, however:

When I use data validation with tables in Excel, I like to use the INDIRECT function, for example the data validation source for some data in a column named 'Column1' in a table named 'Table1' would be:

=INDIRECT("Table1[Column1]")

You should not need blanks in the table as when you add/ remove items from the table the data validation will update automatically.

 

[Fluff]

Only two of the three tables show up when I hit f3 on the source of the range in data validation.

Those are not table names, they are named ranges. Table names do not show-up when using F3 & you cannot use structured references in data validation, short of using volatile functions.

 

[FrumpyJones]

I can't look at the file as my system at work will not let me, however:

When I use data validation with tables in Excel, I like to use the INDIRECT function, for example the data validation source for some data in a column named 'Column1' in a table named 'Table1' would be:

=INDIRECT("Table1[Column1]")

You should not need blanks in the table as when you add/ remove items from the table the data validation will update automatically.

How would I adapt that if the columns are on a different tab?

 

[FrumpyJones]

Those are not table names, they are named ranges. Table names do not show-up when using F3 & you cannot use structured references in data validation, short of using volatile functions.

I'm not sure I understand your reply. I did the same steps for making all three of the tables, then I named the tables. and then I hit f3 in the SOURCE area, and two of the three tables I created show up (And work). I didn't make them ranges... at least, I didn't deliberately do it...

 

[Fluff]

They are NOT tables, they are both named ranges.
If you open the name manager you will see that the icon for both Reason & Agent are different from the other 4 names.

The grey bar at the top of the icon shows it is a table.

 

[FrumpyJones]

They are NOT tables, they are both named ranges.
If you open the name manager you will see that the icon for both Reason & Agent are different from the other 4 names.
View attachment 111230
The grey bar at the top of the icon shows it is a table.

AH! Thanks. So is there a way to do this so it references the table and not a range? Does @Georgiboy 's reply work?

 

[Fluff]

Does @Georgiboy 's reply work?

Try it and find out.

 

[FrumpyJones]

I can't look at the file as my system at work will not let me, however:

When I use data validation with tables in Excel, I like to use the INDIRECT function, for example the data validation source for some data in a column named 'Column1' in a table named 'Table1' would be:

=INDIRECT("Table1[Column1]")

You should not need blanks in the table as when you add/ remove items from the table the data validation will update automatically.

Finally figured out what you were saying and where I was messing up. This does work perfectly. THANKS!

 

[Georgiboy]

Glad we could help and I am also glad you worked it out