Jan 22 2022 08:44 AM
Jan 22 2022 08:44 AM
Jan 22 2022 09:17 AM
I've attached a spreadsheet here with a formula that will do what you want. I added in a "GraceDays" capability so that you can, if desired, give your clients a little slack. If you set that number to zero, then the "yes" will appear when the Order Received column is blank and Approx Order date is today or less.
Here's an image that shows the formula in the formula/function bar, but the live spreadsheet is also attached.
Jan 22 2022 10:44 AM
@mathetes This is great, thank you! I ran into a couple issues along the way though. I'm not sure how you have the "grace days" added to your spreadsheet. When I try to duplicate it on my spreadsheet, my formula just notes the cell number, rather than "GraceDays" as your example shows. (I am very inexperienced with Excel) If I try to eliminate "GraceDays" all together, which I'm perfectly fine with, I see "NAME?" populates the cell.
Also, some of the "approximate order date" cells are blank as the projects are not underway just yet, so if that cell is blank, a "yes" is populating in the "send reminder" cell.
So.. is there a way to eliminate the "GraceDays" from the equation and eliminate "yes" from any projects that do not currently have an "approximate order date"?
Thanks again for taking the time to help me out!
Jan 22 2022 11:41 AMSolution
Use this formula
This way, if the data in column A is blank, then there is no "Yes' generated, but the rest of the formula is as it was before, except I removed the named reference to "GraceDays" and just refer to the cell F2, which contains the number of days.
[FYI, I had used a slightly advanced feature of Excel, called a "named range," to refer to that cell. It made the formula more "readable," but you can also use the cell reference, F2. And the dollar signs, make it an absolute reference, meaning it stays constant, even as you copy the formula down through multiple rows.]
Since you're a relative beginner, you might find this website useful. https://exceljet.net/search?query=named+range
You can search for various functions, abilities of Excel. There are also videos, and YouTube has lots of useful resources as well.