I originally have this formula:
=QUERY('Sheet1'!$C$6:$I, "Select F where D contains '"&$B86&"'")
This is applied to every cell from $B86 down to $B145. Users of the sheet accidentally deletes formula and adding protection isn't an option. So I'm thinking of adding the formula to the header only using arrayformula. However, arrayformula can't be used for query.
QUESTION 1: Is there any other way around to get the same result when only the header has the formula?
={"Messages Sent";ARRAYFORMULA(QUERY('Sheet1'!$C$6:$I, "Select F where D contains '"&B86:B145&"'"))}
This is kinda what I want to achieve. However, this doesn't fill the data from B87 to B145. I only get the header and result for 1 row below the header.
Explanation
This shows the header ={"Messages Sent";
This is supposed to be the arrayformula ARRAYFORMULA(QUERY('Sheet1'!$C$6:$I, "Select F where D contains '"&B86:B145&"'"))}
This is the data from another sheet 'Sheet1'!$C$6:$I
This is the same data from the current sheet to find F in sheet 1 '"&B86:B145&"'
QUESTION 2: HOW CAN I GET THE ANSWER FROM THE QUERY AUTOMATICALLY SET INTO A NUMBER FORMAT SO I CAN DIRECTLY ADD THEM INSTEAD OF FORMATTING THEM MANUALLY AS NUMBER?
try maybe:
={"Messages Sent"; ARRAYFORMULA(IFNA(VLOOKUP(B86:B145,
{REGEXEXTRACT(""&Sheet1!D6:D, TEXTJOIN("|", 1, B86:B145)), Sheet1!F6:F*1}, 2, 0)))}