Leveraging TEXTJOIN

[CCSlice]

Hi Everyone,

I am looking to leverage the TEXTJOIN function in Office 365 version to return which files are currently opened in a list. The function itself returns the names of the people who have currently checked out files (Column F), but what I would like returned is the name of the files (Column C) that are currently opened.

Sample File.xlsx A B C D E F 1 Location Date File Link Modified Date File Currently Checked Out To 2 T:\Data Sets\ 2022-03-08 eLog_Daniel.xlsx Link 2022-02-23 3 T:\Data Sets\ 2022-03-08 eLog_Joseph.xlsx Link 2022-02-22 Joseph Sommers 4 T:\Data Sets\ 2022-03-08 eLog_Miriam.xlsx Link 2022-02-21 5 T:\Data Sets\ 2022-03-08 eLog_Deborah.xlsx Link 2022-02-23 Deborah Teagan 6 T:\Data Sets\ 2022-03-08 eLog_Karen.xlsx Link 2022-02-24 7 T:\Data Sets\ 2022-03-08 eLog_John.xlsx Link 2022-02-26 John Dailey 8 T:\Data Sets\ 2022-03-08 eLog_Philip.xlsx Link 2022-02-27 Philip Mountcastle 9 T:\Data Sets\ 2022-03-08 eLog_Isaac.xlsx Link 2022-02-17 SharePoint List Cell Formulas Range Formula D2:D9 D2 =HYPERLINK(A1&C1,"Link") My thanks in advance for all the help with this challenge.

 

[CCSlice]

Assuming that you have access to MS365 functions, including LET, see if this is what you want.
I have provided examples of all three circumstances.

CCSlice.xlsm
	A	B	C	D	E	F	G	H
1	Location	Date	File	Link	Modified Date	File Currently Checked Out To
2	T:\Data Sets\	2022-03-08	eLog_Daniel.xlsx	Link	2022-02-23			No files are checked out
3	T:\Data Sets\	2022-03-08	eLog_Joseph.xlsx	Link	2022-02-22
4	T:\Data Sets\	2022-03-08	eLog_Miriam.xlsx	Link	2022-02-21
5	T:\Data Sets\	2022-03-08	eLog_Deborah.xlsx	Link	2022-02-23
6	T:\Data Sets\	2022-03-08	eLog_Karen.xlsx	Link	2022-02-24
7	T:\Data Sets\	2022-03-08	eLog_John.xlsx	Link	2022-02-26
8	T:\Data Sets\	2022-03-08	eLog_Philip.xlsx	Link	2022-02-27
9	T:\Data Sets\	2022-03-08	eLog_Isaac.xlsx	Link	2022-02-17
10
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LET(f,FILTER(SPL_DATA[File],SPL_DATA[File Currently Checked Out To]<>"",NA()),r,IFNA(ROWS(f),0),IF(r = 0,"No files are checked out","Warning: "& IF(r<5,"These files are open: "&TEXTJOIN(" | ",,f),r&" activity files are currently checked out")))

CCSlice.xlsm
	A	B	C	D	E	F	G	H
1	Location	Date	File	Link	Modified Date	File Currently Checked Out To
2	T:\Data Sets\	2022-03-08	eLog_Daniel.xlsx	Link	2022-02-23			Warning: These files are open: eLog_Joseph.xlsx | eLog_Deborah.xlsx | eLog_John.xlsx | eLog_Philip.xlsx
3	T:\Data Sets\	2022-03-08	eLog_Joseph.xlsx	Link	2022-02-22	Joseph Sommers
4	T:\Data Sets\	2022-03-08	eLog_Miriam.xlsx	Link	2022-02-21
5	T:\Data Sets\	2022-03-08	eLog_Deborah.xlsx	Link	2022-02-23	Deborah Teagan
6	T:\Data Sets\	2022-03-08	eLog_Karen.xlsx	Link	2022-02-24
7	T:\Data Sets\	2022-03-08	eLog_John.xlsx	Link	2022-02-26	John Dailey
8	T:\Data Sets\	2022-03-08	eLog_Philip.xlsx	Link	2022-02-27	Philip Mountcastle
9	T:\Data Sets\	2022-03-08	eLog_Isaac.xlsx	Link	2022-02-17
10
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LET(f,FILTER(SPL_DATA[File],SPL_DATA[File Currently Checked Out To]<>"",NA()),r,IFNA(ROWS(f),0),IF(r = 0,"No files are checked out","Warning: "& IF(r<5,"These files are open: "&TEXTJOIN(" | ",,f),r&" activity files are currently checked out")))

CCSlice.xlsm
	A	B	C	D	E	F	G	H
1	Location	Date	File	Link	Modified Date	File Currently Checked Out To
2	T:\Data Sets\	2022-03-08	eLog_Daniel.xlsx	Link	2022-02-23	Tom		Warning: 6 activity files are currently checked out
3	T:\Data Sets\	2022-03-08	eLog_Joseph.xlsx	Link	2022-02-22	Joseph Sommers
4	T:\Data Sets\	2022-03-08	eLog_Miriam.xlsx	Link	2022-02-21
5	T:\Data Sets\	2022-03-08	eLog_Deborah.xlsx	Link	2022-02-23	Deborah Teagan
6	T:\Data Sets\	2022-03-08	eLog_Karen.xlsx	Link	2022-02-24
7	T:\Data Sets\	2022-03-08	eLog_John.xlsx	Link	2022-02-26	John Dailey
8	T:\Data Sets\	2022-03-08	eLog_Philip.xlsx	Link	2022-02-27	Philip Mountcastle
9	T:\Data Sets\	2022-03-08	eLog_Isaac.xlsx	Link	2022-02-17	Dan
10
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LET(f,FILTER(SPL_DATA[File],SPL_DATA[File Currently Checked Out To]<>"",NA()),r,IFNA(ROWS(f),0),IF(r = 0,"No files are checked out","Warning: "& IF(r<5,"These files are open: "&TEXTJOIN(" | ",,f),r&" activity files are currently checked out")))

This is a great solution! Perfect! Thanks for helping me out.

 

[Peter_SSs]

You're welcome. Thanks for he follow-up.