How to check separate sheet cell for any data, and return a Y?

Question

I'm using several Google sheets to capture product data and optimize it. One sheet will feed data into the next, and then again into another sheet, using this formula:

=IMPORTRANGE("https://url-to-shared-google-sheet", "NameOfTab!A2:A1100")

I'm now trying to create a 4th 'progress' sheet that tracks how much of each sheet is complete. I'm trying to figure out how to write a formula that will:

IF there is any data in separate-sheet-file-name, tab-name, column A2-A1100 THEN display a Y (or 1 I don't care), in column B4-B1100

I have been unable to figure out how to combine the call to evaluate a cell and the separate file name. Any help is greatly appreciated.

Answer 1

• in one formula it would be:

  =ARRAYFORMULA({IMPORTRANGE("ID", "NameOfTab!A2:A1100"),
   IF(IMPORTRANGE("ID", "NameOfTab!A2:A1100")<>"", "Y", )})