0

My Google Sheet that will be updated over time with new sheets. On my dashboard/master sheet, I can write a simple INDIRECT that will pull information from a cell in the sheets. However, the formula does not replicate its way down the column. I understand that I need to use an ARRAYFORMULA to get the auto formula placement done.

I've tried many ways but the one that I think may get me there is to use CONCAT. My columns look like this:

Event Title [uses a script to pull in the names of all the sheets]

Use an array to get the titles so they pre-poluate down the column so I can use it later: =ARRAYFORMULA(IF(Row(A:A)=1,"Get Title from A",IF(ISBLANK(A:A),"",A:A)))

-- The Event Title is now appearing as plain text in Column B.

I then use CONCAT to write the part of the formula I need to help get the name of the INDIRECT in without using the INDIRECT formula. =CONCAT("'"&B5&"'"&CHAR(38)&"!"&"""","B2"&"""")

-- This gets me this result: 'Computers 101'&"B2"

At this point, my hope is that I could then use this information ('Computers 101'&"B2") into an ARRAYFORMULA. I used this formula to try and do that: ={"Event Date";ARRAYFORMULA(A6:A+D6:D&"Cat")}

-- I get the answer: 0

The expected value was the date cell (B2) in the Computers 101 sheet. Any ideas how to proceed? I don't know the names of the sheets in advance.

user3753138
  • 119
  • 2
  • 10

1 Answers1

1

unfortunately, this is not possible within of scope of google sheets formula. if you need ranges from other than the actual sheet you need to use INDIRECT which is not supported under ARRAYFORMULA.

you have 3 options:

  1. hardcode it like: https://stackoverflow.com/a/68446758/5632629 with IFERROR & array of empty cells to match columns of your range

  2. generate formula which will generate your final formula/range as a text string and then use a simple script to convert that string into the valid formula: https://stackoverflow.com/a/61819704/5632629

  3. do it all with scripts

player0
  • 124,011
  • 12
  • 67
  • 124