Excel Data Validation - Offset(List)

[mintjulep]

I'm trying to restrict data entry in a cell to a list of values from a dropdown. Easy enough.

But the values that I'd like to populate the cell with are all abbreviations.

I'd like dropdown to to be the fully spelled out phrase, not the abbreviation.

I have a Table with two columns. Full phrase; abbreviation.

Neat trick: =Indirect("Table1[Full phrase]") as the data validation source makes the list the values in the table.

So I tried =Offset(Indirect("Table1[Full phrase]")0,1) as the data validation source. Unfortunately this makes the list in the dropdown become the values in the offset column.

Suggestions?

 

[SkipVought]

Mint,

Can't use Structured Table references in DV.

I'm at my iPad, so I can't verify this but that's my recollection: 95% certain.

You might try Names Ranges rather than ST refs.

 

[SkipVought]

Named Ranges, oui!

 

[mintjulep]

Skip,

Apparently Structured Tables can be used with DV.

This works:

Allow: List
Source: =Indirect("Table1[Full phrase]")

This also "works", it's just that the result is not what I want:

Allow: List
Source: =Offset(Indirect("Table1[Full phrase]")0,1)

 

[SkipVought]

So, I was wrong!

I got your OFFSET(INDIRECT()) method to work in a DV!

=OFFSET(INDIRECT("Table1[Full phrase]"),0,1)

produces a list of abbreviations.

Interesting exercise.

 

[mintjulep]

If by interesting you mean frustrating, then I agree.

I could obviously use a helper column, but...

 

[SkipVought]

well you can see that I missed your point completely. WYSIWYG.

So, rather, why not just make the list the full phrase and the lookup the abbreviation from the selection in an adjacent cell?