Array formula to reference cell on another tab, displaying plain text instead of formula

0
votes

I'm trying to reference the same cell across all (many) tabs based on time as criteria (using IFS), using an array to pull tab names from 1 column, and manually entering the cell name within the IFS formula.

I'm getting the intended info into each cell, except it's being placed as plain text rather than pulling the info from another tab/cell. For example, the array column is filled with cells that read "TAB!A1" or "=TAB!A1", rather than the info from that specific cell.

Any ideas on what to do here? Here's one of the current formulas I tried out:

=ArrayFormula(IF(LEN(A3:A), "=" & $A3:A & ifs(
$Z$1<1.374+N("  Sunday"),
"!H79",

$Z$1<2.374+N("   Monday"),
"!H67",

$Z$1<3.374+N("  Tuesday"),
"!H69",

$Z$1<4.374+N("Wednesday"),
"!H71",

$Z$1<5.374+N(" Thursday"),
"!H73",

$Z$1<6.374+N("   Friday"),
"!H75",

$Z$1<7.374+N(" Saturday"),
"!H77",

$Z$1>=7.374+N("  Sunday"),
"!H79")))

Here's a sample sheet Columns S-AC is an example of where this would go.

I'm also open to script if that would work better.

Thank you!

1
arraysgoogle-sheetsgoogle-sheets-formula

1 Answers

1
votes

REVISED: Try this ugly brute force formula, in CoverSheet!S2. See my sample tab in your sheet, GK-Cover Sheet. It includes the column label, as the first line of the array:

=IF(N("  Index into the CHOOSE formula below is:          1=Sun, 2=Mon, 3=Wed, 4=Wed, 5=Thu, 6=Fri, 7=Sat, 8=Sun")=0,{ "TODAYS ARMING 1 TIME";
IFERROR(INDIRECT($A3  & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A4  & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A5  & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A6  & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A7  & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A8  & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A9  & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A10 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A11 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A12 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A13 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A14 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A15 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A16 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A17 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A18 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A19 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A20 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A21 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A22 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A23 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A24 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A25 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A26 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A27 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A28 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A29 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A30 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A31 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A32 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A33 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A34 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A35 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A36 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A37 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A38 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A39 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A40 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A41 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")));
IFERROR(INDIRECT($A42 & CHOOSE(ROUNDDOWN(($AB$1-0.374))+1,"!H79","!H67","!H69","!H71","!H73","!H75","!H77","!H79")))},IFERROR(1/0))

My earlier formula attempt obviously failed since I'd overlooked how ARRAYFORMULA resolves any INDIRECT text strings BEFORE processing through the cell range.

So here I force the INDIRECT string resolution for each row, prior to generating the array. The N function in the first line is used to include a helpful comment within the formula - N("... any text...") equates to 0.

It's relatively easy to maintain, provided you aren't going to 100+ columns. I believe I have the math correct for the CHOOSE function, but please verify.

enter image description here