Data Validation List from table + string

[jaryszek]

Hi,

i have in DV lists reference to listobject:

Please Login or Register  to view this content.

List consists of 1,2,3.

this is ok.
But i want to have "Text" + =indirect("t_NWVersions[VersionName]") in DV list:
Text
1
2
3

It is possible?

Best,
Jacek

[Pepe Le Mokko]

For the second and last time, please do not use acronyms that not everyone is supposed to understand. Please change your thread title accordingly. Thank you

[jaryszek]

No problem my friend,

i didnt notice before that you mean about acronyms in title.

Title changed,
Best,
Jacek

[Pepe Le Mokko]

Thank you. Understand this is in your best interest

[rorya]

The simple answer is no. You can use a delimited string, or a range, not a combination of both.

[jaryszek]

thank you Rory!

So only way to do this is creating macro in VBA.

Should i set range from table first and resize to include string?

Please help,
Jacek

[rorya]

To use a range, the cells have to be contiguous so you would have to add the text to the table itself, or copy all the info somewhere else and use that range.

[jaryszek]

Hmm so i can not do something like that in memory?

Maybe better option would be add values to dictionary and add one additional text value?
And somehow assing dictionary to a range or maybe array and use as DV list?

Or maybe i shoud use UDF and have this UDF as named range and use this named range as DV list?

Best,
Jacek

[rorya]

Again, no. You can use a range or a literal delimited string, nothing else. You would have to either create a delimited string and assign that to the cell's validation using a macro (a UDF will not work), or put the data in a range as I said earlier.

[jaryszek]

ok thank you.

"You would have to either create a delimited string and assign that to the cell's validation using a macro"

1. this seems to be not very useful. I would like to create macro for example on worksheet activate event and this is not best option i suppose.

2. Why UDF will not work?

Jacek

[rorya]

1. I don't know what answer you expect to that.
2. Because it won't.

[jaryszek]

1. I don't know what answer you expect to that.

I expected to get best possible solution here. So question is how you would do this?

2. Because it won't.

If you will create formula and have string in this formula it should work, can you explain why it won't?

Best,
Jacek

[rorya]

1. I would probably put the data in a range somewhere. I've never had cause to need to do this, so it's hard for me to imagine what the actual reason behind it is.
2. That's just the way it is. It has to be a literal delimited string. You cannot use any sort of calculation.

[jaryszek]

Thank you rorya,

i have source table from Access database but we want to add possibility for user to choose also "Local Defined Value" as text in DV.

Best.
Jacek

[rorya]

Then I'd suggest you simply set up another reference table that you can use as the source for the DV.

[jaryszek]

thank you Rorya,

Best,
Jacek