Forum Discussion
How to Create a Formula Based on Multiple Conditions
mathetes Thanks for your reply! Attached is a smaller version of my spreadsheet (the original was too big to attach) - let me know if I can provide anything else.
bperry1792 so I tweaked your reference sheet to have Phase and Level making a table. Fill in the estimated times into that table then you can use that table to do a lookup to fill in the estimated end time:
If those aren't "hard"/known times but you have a large file of past jobs and such you could do the same thing but fill in the table by using formula (probably a couple sumifs()/countif() or a sumproduct() formula)
mtarler Thank you so much! The table idea proved to very helpful and that is exactly what I did. I had to create a new sheet for the 50+ different phases I had, but it worked wonderfully for the data that I needed to reference.
mtarler built for you exactly what I was envisioning. He suggests that you "use that table to do a lookup to fill in the estimated end time:"
I'm going to take it a step further to suggest a specific way to access the numbers in that two-dimensional array, once they've been filled in. It's to use XLOOKUP, a newer function that allows you to use both the horizontal axis and the vertical axis to find the intersection. Here's a screen grab from exceljet.net that explains it.
You could also use OFFSET with two embedded MATCH functions to accomplish the same result, but I think the nested XLOOKUP is simpler.
You seem handy enough with Excel (given some of the other formulas you've written), but if you need more help, I'm sure either mtarler or I could return to give that. I've got another pressing commitment at the moment, but wanted to give this additional resource. Here's the exceljet link for still more help with that function. https://exceljet.net/excel-functions/excel-xlookup-function
mathetes wow, thank you so much! XLOOKUP proved to be just what I needed. By using the table I created thanks to mtarler 's idea, I was able to use that function to calculate how many days were added to the start date based on a certain phase, and then was able to put a simple formula together to to add "x" amount of workdays to get my end date.
While we're here though...
Do either of you have experience with VBA's and Macros? I'm hoping to be able to set up automatic emails to notify users when a cell has been updated that is relevant to them. I did some research and found a ton of code that is based on numerical values, but I can't seem to find any that uses text values. For example, if the salesperson is "Keith Brown" and the "Current Department" phase turns "Sales_Design", I want him to be notified of that. And likewise, if it goes into a department that is not sales related (like Estimating), then I'd like the Estimating manager to be notified when the "Current Department" phase is updated to "Estimating".
Thanks again for taking time out of your day to work with this!I have not actually written a macro for decades. Yes, I did do some back in the day, but not many even then.
So I second @mtarler 's suggestion that you start a new thread asking specifically about macros that can generate an email under specified conditions.
And I'm glad that XLOOKUP worked so well for you.
