Data validation with filter

[Berna11]

Good evening I would need your help for a data validation formula
In the attached file I used the formula:

= OFFSET (Sheet2! $ J $ 2; MATCH (A4; Sheet2! $ K $ 3: $ K $ 81; 0)

but, while in the first two validations of A4: A5 from the desired result in B4; B5
with the choice in A6 the validation of B6 alone gives a name instead of 3 as listed in column D.
Thank you

[JohnTopley]

Set calculations to "Automatic"

result is "tabita"

[Berna11]

Thanks jphn
even setting the result automatically is not correct.

the result in the menu in B6 it must be :Tabita - Tordiquinta - Virgo

If you see Sheet2 I have filtered column K for FUSO and resubmit the 3 names listed above
Thanks again

[JohnTopley]

In OFFSET to need to set the HEIGHT

maybe


=OFFSET(Foglio2!$J$2,MATCH(A6,Foglio2!$K$3:$K$81,0),,COUNTIF(Foglio2!$K$3:$K$81,A6))

or

=OFFSET(Foglio2!$J$2,MATCH(A6,Foglio2!$K$3:$K$81,0),,3)

[Berna11]

Hello we took a step forward but it is still not perfect.
as you can see from the file in
*B6 from Dino is not correct
must give Virgo.

[JohnTopley]

click on B6 drop down arrow; you will be surprised!

[Berna11]

Yes, if I click on B6 they appear

Tabita - Dino - Tordiquinta

but the correct ones are:

Tabita - Tordiquinta - Virgo

[JohnTopley]

I am confused as to how DV are (or meant to be) working.

If you look at columns J/K on Foglio2 you will see you need an INDEX/SMALL construct to extract those names belonging to "FUSO": put these in a named range and reference the named range in FV for B6

Using a simple Offset from J2 will not work as "Dino" is the midst of the "FUSO" entries

[Berna11]

I tried to alphabetize the column K sheet2
and so returns the correct result.
Thank you again for your precious help
see you next time

[johnmpl]

Hi, to all!

Berna11... if you want the solution that JohnTopley suggest, you have to sort your data by column K (2). If you can't sort, we can make a solution for that... just tell us. Blessings!

[Berna11]

Hi Johnmpl
I sorted by column k.
But if there is another solution without making the order, every time I listen well.
Thank you

[johnmpl]

Hi, again!

Check the file in Sheet "Foglio1", in columns C:I (this columns could hidden if you want). You can extend the formula with gray background depends on data.

Blessings!

[Berna11]

hi johnnpl
your solution is really interesting.
It works properly..
A question:
* are the results from column D to the right possible to have them in alternating columns?
Column D = name
column E = empty
Column F = name
column G = empty
...
Thank you so much

[JohnTopley]

Try

in D6

=IFERROR(INDEX(Foglio2!$J$3:$J$7,SMALL(IF(Foglio2!$K$3:$K$7=$A6,ROW(Foglio2!$J$3:$J$7)-ROW(Foglio2!$J$2)),INT((COLUMNS($A:A)-1)/2)+1)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy into F, H etc

[Berna11]

Hi John
with this formula he repeats the same name twice
column D and column E same name
Column F- G same name
...
I wanted to alternate name and white.
D=name
E=white
...
...

[JohnTopley]

If you read my instructions

Copy into F, H etc

you will get right result!

You cannot drag formula across. You have to copy to the required cells BUT you can copy as many as you like.

The IFERROR will leave blanks when the end of the data is reached

[Berna11]

I apologize, john
I had not read the last line:

Copy into F, H etc

ok if I drag the formula to the right then I delete
the formula where it is not needed
Thank you again
see you next time