• 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.

Validation drop-down list for each row with customer name

deciog

Active Member
In cell E5 through E14 I would like to have a validation drop-down list according to customer name in cell D5 through D14

For each customer in column D

It is possible

I showed as an example on column N and P what it looked like to have in the validation dropdown

Thank you very much in advance

A hug

Decio
 

Attachments

Peter Bartholomew

Well-Known Member
Just looking through, I believe the problem is that a validation list must be a worksheet range as opposed to a calculated array. The formulas themselves look correct to me.
 

Peter Bartholomew

Well-Known Member
This is a copy that contains the validation lists and appears to work.
The steps were:
  1. To TRANSPOSE the unique list of names to provide column headers;
  2. To convert your 'Dados' to an Excel Table;
  3. To filter the process numbers associated with individual clients;
  4. To lookup the client and return the anchor cell of the validation range using XLOOKUP;
  5. To expand the anchor cell to the dynamic list by adding '#';
  6. To reference the resulting range by name from the validation.
Since I have never done this before there may be room for improvement!
 

Attachments

Peter Bartholomew

Well-Known Member
Hi herofox
I felt obliged to respond to @Decio's post because it is the first time I have seen a modern dynamic array workbook as the target for solution. Your strategy of calculating a validation list for each client entry can also be implemented using the new array functions such as filter and just works as well.

The validation list itself is easily expressed
= TRANSPOSE( FILTER( Dados[PROCESS NO.], Dados[CLIENT NAME]=@Client ) )

Referring to it is slightly more tangled because validation does not recognise DAs; the validation list needs to be wrapped within a defined name.

I will be relieved once I can turn my back on the traditional spreadsheet techniques but, unfortunately, that is not going to be any time soon, given that even Office 2019 requires legacy approaches rather than DA. Reminders of how SMALL and COLUMN can be used in place of FILTER and SEQUENCE are going to be relevant for a long time :eek:
 

deciog

Active Member
Peter, as always helping, thank you

It works perfectly, but I did an example of 57 clients, but it has more than 6000 clients

Note on "Decio Classified Model" works as I would like without having to classify

I tried to use formula as = IF(Dados!$B$2:$B$74=D5,dados!$C$2:$C$74) but I can't put together only the true result to list only the values

I'm not an Excel expert, but I think I have a solution

Decio

herofox thanks for your help

But I would like the result to be like "Decio Classified Model" but without classifying

It will be possible?

Decio
 

Attachments

Peter Bartholomew

Well-Known Member
Maybe something on these lines would work for you. The validation list for a process cell is only populated if the client cell is filled and the process cell is blank. If you only append new entries and never amend existing ones further simplification might be possible.
63300
 

Attachments

deciog

Active Member
Peter, good morning.

This way is much better and faster I will use this

Thank you very much

Hugs

Decio
 
Top