Fill in name column based on which sheet is being imported

1
votes

I have a Google sheet which imports tracking numbers from multiple other sheets and different information from each. I am able to do that part but is it possible to assign a name to each sheet being imported and have the name displayed in a cell on the row?

I hope this isn't super confusing, I am providing a sample document below. I am importing information from Tom and Brent sheets into Master Sheet. I would like column B of the master sheet to say which sheet the import is coming from. Any thoughts?

Master Sheet https://docs.google.com/spreadsheets/d/10YnvyaK_ODWZpVoYMMItOr6Dr9j2Gy--aJ3d4hegVi4/edit#gid=0

Tom Sheet https://docs.google.com/spreadsheets/d/1wnxnhRkTiLtut7Ej9kE-NH6GrK0S3St5f3snsr4KR_I/edit#gid=0

Brent Sheet https://docs.google.com/spreadsheets/d/1X5VNVWkjP7ELy2Vuj5uzuK9fB6l1U9Wh9W3ORDkcxA8/edit#gid=0

1
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-queryimportrange

1 Answers

1
votes
=ARRAYFORMULA(SPLIT({
 QUERY(IMPORTRANGE("1wnxnhRkTiLtut7Ej9kE-NH6GrK0S3St5f3snsr4KR_I","Sheet1!A2:C"),
 "select Col1 where Col2 like '%USPS%'")&"♦Tom";
 QUERY(IMPORTRANGE("1X5VNVWkjP7ELy2Vuj5uzuK9fB6l1U9Wh9W3ORDkcxA8","Sheet1!A2:C"),
 "select Col1 where Col2 like '%USPS%'")&"♦Brent"}, "♦"))

0