Let formula for text

[darkhangelsk]

Hello,

I have a "LET" formula trying to use, however, i got an error whenever I put a text on the first column.

This is use for data validation, so i can remove the unique values and it is based on the data from "Shift" then "Group" headers.

Thank you in advance!

 

[Fluff]

Can you please post your actual formula.

 

[darkhangelsk]

I hope mini workbook is working

Dependent Dropdown List1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Shift	Group	Name				Shift	Group	Name			Shift	Group	Name		Count
2	Text	Video 1	Employee 1				#VALUE!	Video 1	Employee 2		-->	08:00	Video 1	Employee 2		1
3	8:00	Video 1	Employee 2				8:00	Video 2	Employee 3
4	8:00	Video 1	Employee 3				12:46	Non Video 1	Employee 4
5	8:00	Video 1	Employee 4				10:00	Non Video 2	Employee 5
6	8:00	Video 1	Employee 5				16:00	Video Appeal				Shift	Group	Name
7	12:46	23	Mean				18:00					8:00	Video 1	Employee 2
8	Text	22	Due				23:30
9	Text	24	Blank				23:45
10
11
12
13
14	8:00	Video 2	Employee 6
15	8:00	Video 2	Employee 7
16	8:00	Video 2	Employee 8
Sheet1

Cell Formulas
Range	Formula
G1:I1,L6:N6,L1:N1	G1	=Table1[#Headers]
G2:G9	G2	=LET(data,UNIQUE(FILTER(Table1[Shift],Table1[Shift]<>0)), HOUR(data) & ":" & TEXT(MINUTE(data),"00") )
H2:H6	H2	=UNIQUE(FILTER(Table1[Group],Table1[Shift]=--$L7))
I2:I5	I2	=UNIQUE(FILTER(Table1[Name],(--(Table1[Shift]=--$L7))*--(Table1[Group]=$M7)))
L2:N2	L2	=UNIQUE(FILTER(Table1,(--(IF(--$L7=0,1,Table1[Shift]=--$L7)))*--(IF($M7=0,1,Table1[Group]=$M7))*--(IF($N7=0,1,Table1[Name]=$N7))))
P2	P2	=ROWS(L2#)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
L7	List	=Shift#
M7	List	=Group#
N7	List	=Name#

What i need is to include the word "text" on the data validation from the table.

 

[Fluff]

If you want to keep the word Text then why not just use

=UNIQUE(FILTER(Table1[Shift],Table1[Shift]<>0))

and format the column to Time.

 

[darkhangelsk]

If you want to keep the word Text then why not just use

=UNIQUE(FILTER(Table1[Shift],Table1[Shift]<>0))

and format the column to Time.

It worked. but if i use that and select the "text" on the data validation, the "Group" and "Name" is having an error of #VALUE!

I think because the error coming from here:

=UNIQUE(FILTER(Table1,(--(IF(--$L7=0,1,Table1[Shift]=--$L7)))*--(IF($M7=0,1,Table1[Group]=$M7))*--(IF($N7=0,1,Table1[Name]=$N7))))

 

[Fluff]

How about

=UNIQUE(FILTER(Table1,(IF($L7=0,1,Table1[Shift]=$L7))*(IF($M7=0,1,Table1[Group]=$M7))*(IF($N7=0,1,Table1[Name]=$N7))))

 

[darkhangelsk]

How about

=UNIQUE(FILTER(Table1,(IF($L7=0,1,Table1[Shift]=$L7))*(IF($M7=0,1,Table1[Group]=$M7))*(IF($N7=0,1,Table1[Name]=$N7))))

I think it's working as it changed to #CALC!. However, the H2 and I2 (Group and Name header) still have error of #VALUE!. here's their formula:

H2:
=UNIQUE(FILTER(Table1[Group],Table1[Shift]=--$L7))

I2
=UNIQUE(FILTER(Table1[Name],(--(Table1[Shift]=--$L7))*--(Table1[Group]=$M7)))

 

[darkhangelsk]

ohh... i have updated the Data Validation, and it went back to #VALUE! error.

 

[Fluff]

Try it like

=UNIQUE(FILTER(Table1[Group],Table1[Shift]=$L7))

and

=UNIQUE(FILTER(Table1[Name],(Table1[Shift]=$L7)*(Table1[Group]=$M7)))

 

[darkhangelsk]

Try it like

=UNIQUE(FILTER(Table1[Group],Table1[Shift]=$L7))

and

=UNIQUE(FILTER(Table1[Name],(Table1[Shift]=$L7)*(Table1[Group]=$M7)))

Thank you! it seems working. Will do full test and check for any issue before marking it "SOLVED". Again, Thank you so much!