I have been putting together several multi-worksheet workbooks, with multiple formulas running between the sheets. I am over my head most of the time, but have been able to figure out most of what I need by trial and error, and searching the web.
I've hit a roadblock tonight trying to add some new functionality with (what I consider) a complicated formula, that has been working fine for quite some time.
This is the formula that is working fine...
=IF(AND(I$1>=Staff!$N12,OR(I$1 <= Staff!$O12,ISBLANK(Staff!$O12))),(Staff!$H12*Setup!$C$26)*H12,0)
It checks a start date (col N) and end date (col O) on the "staff" tab, and then checking those dates with cell i1...and if all is well it makes a simple calculation. Again, this works perfectly.
The new functionality that I am trying to add is to check another cell value (Staff!B10) for a text string...so something like If(Staff!B10="CAFS")...and if the result is true, it runs the above formula...and if the result is something other than the noted text string, I simply want to run the same formula with a small change (instead of Setup!$C$26 it will simply be Setup!$C$27).
I have tried several things including the two samples below...both of which simply return a "False" value.
=IF(Staff!B5<>"CAFS",(AND(I$1>=Staff!$N6,OR(I$1<=Staff!$O6,ISBLANK(Staff!$O6))))*(Staff!$H6*Setup!$C$26)*H6,IF(Staff!B5="CAFS",(AND(I$1>=Staff!$N6,OR(I$1<=Staff!$O6,ISBLANK(Staff!$O6))))*(Staff!$H6*Setup!$K$26)*H6))
=IF(Staff!B10="CAFS", then(AND(I$1>=Staff!$N10,OR(I$1 <= Staff!$O10,ISBLANK(Staff!$O10))),(Staff!$H10*Setup!$K$26)*H10, IF(AND(I$1>=Staff!$N10,OR(I$1 <= Staff!$O10,ISBLANK(Staff!$O10))),(Staff!$H10*Setup!$C$26)*H10,0)))
I apologize if I am doing a poor job of trying to explain my issue, but as I noted above, I'm over my head (and getting pretty frustrated). Any assistance would be greatly assisted.
Thanks,
Bill
Bookmarks