Add the 'sheet name' to a formula address from a dropdown selection Google sheets

Question

I am trying to extend a question asked here

Combining formula to get unqiue list from delimited values in a column with formula to get range concatenating Sheet-name to output

I have a formula that gets the unique list of items in a column for delimited strings

=sort( unique( transpose( trim( 
  split( 
   join( 
     "|", 
     offset( Elements!A2:A, 0, match(A1, Elements!A1:D1, 0) - 1 ) 
   ), 
   "|", false, true 
 ) 
) ) ) )

But I have multiple sheets I want to use this on so I have to manually change the sheet name

I am trying to change the sheet name by referencing a dropdown of sheet names

I have two formulas that get the value of the dropdown

=INDIRECT(cell("address",'UniqueList_(ignore)'!K1))

and

=TEXTJOIN(", ", 1, INDIRECT("UniqueList_(ignore)"&"!K1"))

and if I try to use either of them like this

=sort( unique( transpose( trim( 
  split( 
   join( 
     "|", 
     offset( INDIRECT(cell("address",'UniqueList_(ignore)'!K1))&!A2:A, 0, match(A1, INDIRECT(cell("address",'UniqueList_(ignore)'!K1))&!A1:Z1, 0) - 1 ) 
   ), 
   "|", false, true 
 ) 
) ) ) )

I get formula parse error

Here is a Google sheet with all of this laid out

https://docs.google.com/spreadsheets/d/15tpuRdZUrSfiNapXWUtL2Zt1QOaxtf4T7SG1FwLyi30/edit?usp=sharing

Thanks for any assistance with this

Answer 1

See if this helps

=sort( unique( transpose( trim( 
  split( 
   join( 
    "|", 
     offset( INDIRECT('UniqueList_(ignore)'!K1&"!A2:A"), 0, match(A1, 
     INDIRECT('UniqueList_(ignore)'!K1&"!A1:Z1"), 0) - 1 ) 
   ), "|", false, true 
 ) 
 ) ) ) )