For Google Sheets, what is the function that will populate a cell with specific text when pulling data from multiple tabs of another sheet?

Question

I am building a Google Sheet that pulls data from another Google Sheet with multiple tabs. The source sheet doesn't include one particular data point (i.e. business name), and I want to populate the cells in the corresponding rows of the new sheet based on the source tab. Is this possible? If so, what is the formula/function for doing so?

In this sheet, I would like column F to be populated based on the source tab rather than manually typed. Thanks!

Answer 1

Add an additional column to each of the IMPORTRANGE` arrays; the extra column will contain the "Business name".

For example: based on the formula in Column E,

=QUERY({IMPORTRANGE("<<insert url1>>", "CareSource Stronger Together Referral Form!e2:e1000"),arrayformula(if(len(importrange("<<insert url1>>","CareSource Stronger Together Referral Form!e2:e1000")),"CareSource",)); importrange("<<insert url2>>","JenCare - AP Prescription Form!E2:E1000"),arrayformula(if(len(importrange("<<insert url2>>","JenCare - AP Prescription Form!E2:E1000")),"Jencare",)); IMPORTRANGE("<<insert url3>>", "Focus Forward - AP Prescription Form!e2:e1000"),arrayformula(if(len(importrange("<<insert url3>>", "Focus Forward - AP Prescription Form!e2:e1000")),"Focus Forward",))}, "SELECT * WHERE Col1 IS NOT NULL" )

---

Logic

This is the extra column:

,arrayformula(if(len(importrange("<<insert url3>>", "Focus Forward - AP Prescription Form!e2:e1000")),"Focus Forward",))

• if the IMPORTRANGE has a value, then create a new Column "Focus Forward".

---

Sample

---

Notes

The existing spreadsheet has one formula for each column; this could be simplified by creating one formula (entered in Cell A2) that populates columns A-F. Using the example data from above, such a formula might look like this:

=QUERY({IMPORTRANGE("<< INSERT URL1>>", "CareSource Stronger Together Referral Form!a2:e1000"),arrayformula(if(len(importrange("<< INSERT URL1>>","CareSource Stronger Together Referral Form!a2:a1000")),"CareSource",)); importrange("<< INSERT URL2>>","JenCare - AP Prescription Form!a2:E1000"),arrayformula(if(len(importrange("<< INSERT URL2>>","JenCare - AP Prescription Form!a2:a1000")),"Jencare",)); IMPORTRANGE("<< INSERT URL3>>", "Focus Forward - AP Prescription Form!a2:e1000"),arrayformula(if(len(importrange("<< INSERT URL3>>", "Focus Forward - AP Prescription Form!a2:a1000")),"Focus Foward",))}, "SELECT * WHERE Col1 IS NOT NULL" )