Function for filling in values to adjacent cells

user_15412016 0
Viewed 27

I have a Google sheet (Form Connector) that uses a form for users to input data into. From there, another sheet (Summary) gives a summary of all the data that is put into it via the form. Another sheet gives a breakdown of all the data (Daily RT). The Daily RT sheet is shown below

Daily RT sheet

Within the cells that say "No RT" is the following formula, with respect to their row.

=IFNA(ARRAY_CONSTRAIN(FILTER('Form Connector'!$D$2:$F,'Form Connector'!$C$2:$C=$B$1,'Form Connector'!$B$2:$B=$A3),1,20),IF($A3<TODAY(),"No RT",""))

Column A is the dates, B:D is data as well as E:G. I've been focusing on the IF($A3<TODAY(),"No RT","") in the formula to be able to replace the "No RT" part of it.

Question: Is there any way, instead of "No RT", to fill in 0's in columns B:D using the one formula which is in column B via an array?

vlookup google-sheets-formula google-sheets if-statement filter
1 Answers

try in B3 after you remove everything from B3:D range:

=ARRAYFORMULA(IFNA(VLOOKUP($A3:$A, 
 FILTER('Form Connector'!$B2:$F, 'Form Connector'!$C2:$C=B$1), 
 {3,4,5}, 0))*1)

enter image description here

update:

=ARRAYFORMULA(IF($A3:$A>=TODAY(),,IFNA(VLOOKUP($A3:$A, 
 FILTER('Form Connector'!$B2:$F, 'Form Connector'!$C2:$C=B$1), 
 {3,4,5}, 0))*1))
user_56326290
Related