CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
2dayholiday
2dayholiday

Reputation: 73

Textjoin or Concatenate for this case?

hope you have a good day/evening.

Due to I always seems to use importrange function to import multiple sheets. I want to have a quicker way to replace the date (highlighted in red as per the screenshot) with the date referenced in Col A. This is my Google Sheet under the tab name "TextJoin" Google Sheets Link

enter image description here

Upvotes: 0

Views: 308

Answers (2)

player0
player0

Reputation: 1

try:

=INDEX({""; "={"&TEXTJOIN("; ", 1, 
 "IMPORTRANGE(""13DWtP4L7swqBgK6BGLeA-o_FfyD-D8-Ru30cOPf0I10"", """&
 FILTER(TO_TEXT(A2:A)&"!A2:C"")", A2:A<>""))&"}"})

enter image description here

but you may need to wrap it into query and remove empty rows perhaps like:

=INDEX({""; "=QUERY({"&TEXTJOIN("; ", 1, 
 "IMPORTRANGE(""13DWtP4L7swqBgK6BGLeA-o_FfyD-D8-Ru30cOPf0I10"", """&
 FILTER(TO_TEXT(A2:A)&"!A2:C"")", A2:A<>""))&"}, ""where Col1 is not null"", )"})

enter image description here

Upvotes: 3

Mike Steelson
Mike Steelson

Reputation: 15328

Try

=importrange("_____","'" & text(A2,"M/d/yy") &"'!A2:C")

Upvotes: 1

Related Questions