this could be accomplished with an onEdit Apps Script, instead of array formulas.
this script does NOT obey cell colouring... so black cells (B20 and C20) will have their colour changed to clear, and you will need to go back to those cells and colour them again.
heres the script:
and heres some slight changes to the formulas, that will keep the cell clear if the cell in column D is empty..
B7:
C7:
Steps to follow:
1. go to Extensions -> Apps Script
2. select everything in the existing base script, and overwrite with the script given above
3. save the script
4. enter the B7 formula, and hit enter, DO NOT accept the G-Sheets Autofill prompt, then wait for about 20 seconds while the script does its work and Autofills down to all other rows
5. enter the C7 formula, and hit enter, DO NOT accept the G-Sheets Autofill prompt, then wait for about 20 seconds while the script does its work and Autofills down to all other rows
6. if the script does not fill down far enough, then simply delete the formula, then enter it again, and it will push down further each time
7. recolour cells B20 and C20
8. DONE!
Bookmarks