Limit in data validation list

[jaryszek]

Hi,

i have formula in data validation list like here:

=IF(AND(Vn_Input_HANA_Workload="BWoH";Vn_Input_HANA_Production="Yes");S_ConditionBWoHYes;IF(AND(Vn_Input_HANA_Workload="BWoH";Vn_Input_HANA_Production="No");S_ConditionBWoHNo;IF(AND(Vn_Input_HANA_Workload="SoH";Vn_Input_HANA_Production="Yes");S_ConditionSoHYes;S_ConditionSoHNo)))

Problem is that there is an entry limit!
How to avoid this?

Or workaround?

Best,
Jacek

 

[RoryA]

It looks like you could use this instead:

=INDIRECT("S_Condition"&Vn_Input_HANA_Workload&Vn_Input_HANA_Production)

given your naming convention.

 

[jaryszek]

thank you RoryA.

excellent idea.

I do not know why there is an REF! error:

=INDIRECT("S_Condition"&Vn_Input_HANA_Workload&Vn_Input_HANA_Production)

=INDIRECT("S_ConditionBWoHYes")

Where S_CondtionBWohYes = named range =

=OFFSET('Queries Source'!$U$2;;;COUNTIF('Queries Source'!$U$2:$U$172;"?*"))

Best,
Jacek

 

[RoryA]

Ah, you have dynamic ranges. INDIRECT won't work with those. You can use EVALUATE in a named formula, or use some VBA, or use Tables instead?

 

[jaryszek]

Evaluate? What do you mean?

Evaluate(Named_Range)?

VBA no, i do not need.

I will think about changing ranges to tables.
Best,
Jacek

 

[RoryA]

Basically yes, but in a named formula - you cannot use it directly in a worksheet.

Using Tables is easier as it gives you the benefit of a dynamic range, but will still work with INDIRECT.

 

[jaryszek]

Still please give an example how to do it via Evaluate function.

Best,
Jacek

 

[RoryA]

Create a defined name called say DVList and use this formula:

=EVALUATE("S_Condition"&Vn_Input_HANA_Workload&Vn_Input_HANA_Production)

Then in your data validation, just use =DVList.

You'll need to save the workbook in a macro-enabled format as this is an old Excel 4 macro function.

 

[jaryszek]

thank you RoryA!

I am trying this Evaluate.

with indirect i should create 4 independent list objects or can i do all within one table?

I have problem with this in data validation list:

=indirect(t_Condtition["S_Condition"&Vn_Input_HANA_Workload&Vn_Input_HANA_Production])

Best,
Jacek

 

[RoryA]

You should use a separate table for each list.