• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation - Different list based on entry in Table field

D

dorreke

New Member
Hi,

I want to be able to display a different drop-down list based on an entry in a table.

So up till now I have a LIST data validation with a formula
=IF(LEFT($G$2;3)="ZVC"; STPRL_AC; STPRL_CR_SSF)

The problem is that I am using a table and I want to do this for every row.
I already tried
=IF(LEFT([@ARTYP];3)="ZVC"; STPRL_AC; STPRL_CR_SSF)

But this is not allowed in the Source of the List in the data validation.

How can I fix this?

Thank you!
 
I would think you could leave it as you had before using a cell reference instead of a structural reference. The data validation will get copied to new rows as the table exands, so there's no issue there. Is there a different problem that I'm not thinking of that you want to avoid by using structural references?
 
Hi, dorreke!

Give a look at the uploaded file. It uses 2 tables, one for the actual data (Tabla1) and another for the data validation list entries (Tabla2).

A field (B, code, 2nd column) from 1st table is used thru the below dynamic named range to create the related DV entries for another field (C, description, 3rd column).

DVList: =DESREF(Tabla2;COINCIDIR(Tabla1[@[Title B]];Tabla2[B code];0)-1;1;1;COLUMNAS(Tabla2)-1) -----> in english: =OFFSET(Tabla2,MATCH(Tabla1[@[Title B]],Tabla2[B code],0)-1,1,1,COLUMNS(Tabla2)-1)

Just advise if any issue.

Regards!
 

Attachments

  • Data Validation - Different list based on entry in Table field (for dorreke at chandoo.org).xlsx
    10.4 KB · Views: 2
Hi

Thank you for your quick reply
My problem is that in row 2 I have Formula =IF(LEFT($G$2;3)="ZVC"; STPRL_AC; STPRL_CR_SSF)
In row 3 I have the same Formula =IF(LEFT($G$2;3)="ZVC"; STPRL_AC; STPRL_CR_SSF)
Although I would expect =IF(LEFT($G$3;3)="ZVC"; STPRL_AC; STPRL_CR_SSF)

This is not automatically happening with my excel.

I would also expect that it would change the cells automatically but for some reason it's not doing it
Can you help me?
 
Hi, dorreke!
If there's a column in the table for which you've set the DV condition to a list as that of your formula (2nd) from 1st post, you should leave out that formula and set all (for the whole column cells) DV condition to list and use the same named range dynamically defined upon a value from each row (in my example, DVList).
If you can't manage to handle it, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!
PS: I'll try to help you, of course, but the file would aid all the people who read this thread, either to help or to learn.
 
dorreke said:
Hi

Thank you for your quick reply
My problem is that in row 2 I have Formula =IF(LEFT($G$2;3)="ZVC"; STPRL_AC; STPRL_CR_SSF)
In row 3 I have the same Formula =IF(LEFT($G$2;3)="ZVC"; STPRL_AC; STPRL_CR_SSF)
Although I would expect =IF(LEFT($G$3;3)="ZVC"; STPRL_AC; STPRL_CR_SSF)

This is not automatically happening with my excel.

I would also expect that it would change the cells automatically but for some reason it's not doing it
Can you help me?
Click to expand...
Remove the "$" from in front of the 2, like so:
=IF(LEFT($G2;3)="ZVC"; STPRL_AC; STPRL_CR_SSF)
 
Hi, I indeed found it together with a friend of mine this morning. I noticed that you already provided me the same input as her. Thank you!
 
You must log in or register to reply here.
Back
Top