I have this budget sheet I am making. A big thing is I want to see what is due throughout the month. (Chart on Bottom). I have all the accounts listed via query and I’ve been trying to write a formula that would read the name of the account and match it to the tables above and accurately put a dot on that date. Then when I check the box next to the account in the table it changes the dot from red to green.
Currently the formula I have written half way works. It puts the dot in the correct spot however when I check a box for any account it turn them all green instead of just the dot that correlates to that account.
I either need a more efficient formula or script to use that will work.
This formula is in all the cells within the date chart. (Currently its only written for the first two Account Tables while I am testing if it will work)
=IFNA(
IFS(
OR(
AND(COUNTIF($A$5:$A$27,TRUE),COUNTIF($B$5:$B$27,$A32),COUNTIF($G$5:$G$27,F$31)),
(AND(COUNTIF($N$5:$N$27,TRUE),COUNTIF($O$5:$O$27,$A$32:$A),COUNTIF($T$5:$T$27,F$31)))
),
IF(countifs($B$5:$B$27,$A32:$A,$G$5:$G$27,F$31)+COUNTIFS($O$5:$O$27,$A32:$A,$T$5:$T$27,F$31),"🟢",""),
OR(
AND(COUNTIF($A$5:$A$27,FALSE),COUNTIF($B$5:$B$27,$A$32:$A)),
(AND(COUNTIF($N$5:$N$27,FALSE),COUNTIF($O$5:$O$27,$A32)))
),
IF(
countifs($B$5:$B$27,$A32:$A,$G$5:$G$27,F$31)+
COUNTIFS($O$5:$O$27,$A32:$A,$T$5:$T$27,F$31),
"🔴",""
)
),""
)
I’ve solved the problem.
Here is the new formula I created.
If the checkbox is checked and the other criteria is met then it returns a green dot, if it is unchecked it returns a red dot. If the criteria isn’t met then there is ‘No Match’ so the IFNA returns the cell blank.
This perfectly graphs the date chart with red and green dots showing when an account is coming due and showing ones that have been paid.
=IFNA(
IFS(
IF(
AND(
COUNTIF($B$5:$AU$10, $A32)>0,
COUNTIF(Sheet7!$B1, TRUE)>0,
COUNTIF(Sheet7!$A1, F$31)>0),
"true", FALSE),
"🟢",
IF(AND(
COUNTIF($B$5:$AU$10, $A32)>0,
COUNTIF(Sheet7!$B1, FALSE)>0,
COUNTIF(Sheet7!$A1, F$31)>0),
"true", FALSE),
"🔴"))