dimanche 17 novembre 2019

In Google Sheets how to reference infinite columns in all rows?

The issue of how to reference infinite rows is already answered, but how about referencing infinite columns?

For example Sheet1 has data with an indeterminate number of rows and columns, Sheet2 I have this formula

=arrayformula(if(Sheet1!A:Z="","blank","not blank"))

It goes infinitely down... but what if I want Sheet1!A:InfiniteColumns

This is the best I could come up with

=arrayformula(if(indirect("sheet1!R1C1:"&"R"&rows(Sheet1!A:A)&"C"&COLUMNS(Sheet1!1:1),false)="","blank","not blank"))

So my question is, is there a shorter and simpler way to accomplish this?

Aucun commentaire:

Enregistrer un commentaire