How to use a conditional array formula in Google Sheets?

0
votes

I want my spreadsheet user to be able to choose between two different options for an array formula. I have tried to put it in an IF statement but I can't get it to work. I do not want to cut and paste it down the column because I want it to be applies when a new row is added. I don't really know what I'm doing but I've been fiddling with it for a couple of hours now. Here is the code I currently have:

=IF($B$6="alternating days",ARRAYFORMULA(IF(ISBLANK(indirect("OVERVIEW!$A" & row())),IF($C2:C="l","l","d"),IF($C2:C="l","d","l"))),IF($B$6="weekdays/weekends",ARRAYFORMULA(IF(ISBLANK(indirect("OVERVIEW!$A" & row())),IF($C2:C="l","l","d"),IF(OR(WEEKDAY
(indirect("OVERVIEW!$A" & row()))=1,WEEKDAY(indirect("OVERVIEW!$A" & row()))=7),"l","d"))),"none"))

It's a long formula so please scroll along.

I attempted to have the ARRAYFORMULA at the beginning but it wouldn't let me reference just $B$6.

Thanks for any help you can provide.

1
google-apps-scriptgoogle-sheetsspreadsheetgoogle-apps
can you share a sample sheet with some dummy data - its really difficult to debug without some pretend data to confirm the expected result – Aurielle Perlmann
Here is a link: docs.google.com/spreadsheets/d/… It's on the 'settings' tab in C3. The formula will control the conditional formatting. Thanks – Josh Smith
Can you add a couple rows demonstrating what It should look like once there is a little data populated on that settings page please? thanks – Aurielle Perlmann
If 'alternating days' is selected then it should alternate between L and D down the sheet. If 'Weekends/Weekdays' is selected then the row numbers should correspond with the OVERVIEW page and if the day is a weekend day it will be D and if it's a weekday it'll be L. Thanks you. – Josh Smith
You'll notice if you manually input in that column an L or a D (in lowercase) that it'll change the shading for the corresponding rows on the other tabs. – Josh Smith

1 Answers

0
votes

I recommend something like this:

=ARRAYFORMULA(IF($B$6="alternating days",if(isodd(row(indirect("C4:C"&counta(OVERVIEW!A3:A)+3))),"l","d"),IF($B$6="weekdays/weekends",ARRAYFORMULA(IF(WEEKDAY(OVERVIEW!A3:A)=1,"d",if(WEEKDAY(OVERVIEW!A3:A)=7,"d","l"))))))

If the days are alternating, use odd rows to alternate the letter, otherwise just combine 2 IF statements to determine if it is a weekday or not.

enter image description here