testermonkey
New Member
- Joined
- Feb 9, 2018
- Messages
- 3
I think I'm over-complicating a series of formulas I wrote to save time for myself.
My job is to e-mail the customer each week when Task A or Task B is completed on Projects A-F. The real spreadsheet has dozens of tasks and it's time-consuming to filter column-by-column for new dates, so I'm writing formulas to remove some of the labor.
See a simplified screenshot of a the spreadsheet I use and my columns with my formulas:
These are the formulas I've made in the above screenshot:
Afterwards, I send a cleaned-up spreadsheet to my customer filtered for rows where column C returns "TRUE."
I think I'm over-complicating this for myself. Is there a way I can eliminate the step where I paste the values into columns D and E? Do I use nested "IF" formulas? I googled this yesterday at the end of my shift but I think I was not using the correct terms because I didn't find relevant results.
My job is to e-mail the customer each week when Task A or Task B is completed on Projects A-F. The real spreadsheet has dozens of tasks and it's time-consuming to filter column-by-column for new dates, so I'm writing formulas to remove some of the labor.
See a simplified screenshot of a the spreadsheet I use and my columns with my formulas:
![2wqdici.jpg](/board/proxy.php?image=http%3A%2F%2Fi67.tinypic.com%2F2wqdici.jpg&hash=4ea111f9597636abfd776a7558247512)
These are the formulas I've made in the above screenshot:
HTML:
<TABLE><TR><TD><B>part of the spreadsheet</b></TD><TD>how i typed it</tD><TD><B>what it does</b></tD></TR><TR><TD>Cell B10</TD><tD>just typed a date</tD><TD>Contains the date of the last report I e-mailed to the customer</TD></TR>
<TR><TD>Column A</TD><TD>=AND((G2<>""), (G2>$B$10)) </TD><TD> I check if Task A has been completed since the date in B10. I test for blank cells ("G2<>"") because I was finding that I'd get false positives if I didn't eliminate blank cells.</TD></TR>
<tR><TD>Column B</TD><TD>=AND((H2>$B$10), (H2<>""))</TD><TD>I check if Task B has been completed since the date in B10 and for blankc ells, same as the other</TD></TR>
<tr><TD>Column C</TD><TD>=OR(D2=TRUE, E2=TRUE)</TD><TD>I check if there is a "TRUE" in columns D or E (meaning that the formulas in Columns A or B found a new date)</tD></TR>
<TR><TD>Columns D, E</TD><TD>Values from A, B pasted over as "values only"</TD><TD>This is something I feel has to have an answer in formulas. I found that the formula in column C couldn't find the "TRUE" or "FALSE" results of the column A and B formulas, so I copy and paste the results as values into D and E in the mean-time. </TD></TR>
<TR><TD>Columns F, G, H</TD><TD>Project information</TD><TD>These contain the project ID and the dates for the tasks, no formulas from me here</tD></TR>
</TABLE>
Afterwards, I send a cleaned-up spreadsheet to my customer filtered for rows where column C returns "TRUE."
I think I'm over-complicating this for myself. Is there a way I can eliminate the step where I paste the values into columns D and E? Do I use nested "IF" formulas? I googled this yesterday at the end of my shift but I think I was not using the correct terms because I didn't find relevant results.