I'm trying to create a formula with these functions, in order:
If one cell (E2)has no data, the affected cell (H2) remains blank
If a different cell (F2) has data, text is implemented in H2
If neither of those apply, a month-based dated function between the (E2) listed above and a separate, constant cell ($A$2) will implement text.
I've attached the sheet. I'm getting a #VALUE error?
Is this part in H2
DATEDIF(INDEX(C2,A:A,2,"m"),{0,7,8},{"Pending","Pending","Delayed"})
Is that trying to do the same type of thing G2 is doing with this
LOOKUP(DATEDIF(C2,A2,"m"),{0,5,7},{"Early","On Time","Delayed"})
If so, this is probably what you want in H2
=IF(ISBLANK(E2),"",IF(F2<>"","COMPLETE",LOOKUP(DATEDIF(C2,A2,"m"),{0,7,8},{"Pending","Pending","Delayed"})))
It's pretty much the same thing, I just want the cell to stay empty if there's nothing in E2, but I want A2 to remain a constant, but C2 to not remain a constant and to change as I add rows to the table. Does that make sense?
Last edited by AliGW; 06-08-2018 at 01:59 PM. Reason: Unnecessary quotation removed.
Do you mean as the formula is filled down the column, the A2 should stay A2..
ie, in H3 it would be
=IF(ISBLANK(E2),"",IF(F2<>"","COMPLETE",LOOKUP(DATEDIF(C3,A2,"m"),{0,7,8},{"Pending","Pending","Delayed"})))
Then in H4
=IF(ISBLANK(E2),"",IF(F2<>"","COMPLETE",LOOKUP(DATEDIF(C4,A2,"m"),{0,7,8},{"Pending","Pending","Delayed"})))
That's where absolute references come in. use the $ on the row in A2
=IF(ISBLANK(E2),"",IF(F2<>"","COMPLETE",LOOKUP(DATEDIF(C2,A$2,"m"),{0,7,8},{"Pending","Pending","Delayed"})))
It's a table, so when I add rows the $ doesn't work. That's why I used INDEX. I think I"m getting the syntax wrong with the INDEX part of the formula.
Last edited by AliGW; 06-08-2018 at 01:58 PM. Reason: Unnecessary quotation removed.
It works fine for me.
I added rows 39 and 40 after I corrected the formula.
I'm sorry, I don't think I explained correctly. I add the rows to the top of the table. I have to keep the most recent update at the top, where it is visible.
Last edited by AliGW; 06-08-2018 at 01:58 PM. Reason: Unnecessary quotation removed.
I would suggest adding new rows to the bottom, then just sort the table
But if you must, I think this is what you were trying to do with the INDEX
=IF(ISBLANK(E2),"",IF(F2<>"","COMPLETE",LOOKUP(DATEDIF(C2,INDEX(A:A,2),"m"),{0,7,8},{"Pending","Pending","Delayed"})))
That worked, thank you!
You're welcome.
I'll just re state that I think you'd be better off with the original formula using A$2. And adding new rows at the bottom then sorting the data.
But that's just an opinion, so whatever works for you.
Glad to help.
Why is that better? Is it less likely to cause formula errors? Genuinely curious, not snarky!
Last edited by AliGW; 06-08-2018 at 01:58 PM. Reason: Unnecessary quotation removed.
1 less function call (more efficient).
Easier to maintain
If you or someone else is looking at the formula in the future, it may be simpler to understand without figuring the purpose of the index.
Hard coding a row # like that is often problematic in future adjustments.
In general, I tend to avoid inserting and deleting rows/columns after my sheet is already designed. i.e. once sheet is just being used, no longer in development.
Inserting/Deleting anything should not be an intended function of the person 'Using' the sheet (again, just in my opinion).
That can effect other formulas that refer to the row/column that was deleted.
I see what you're saying.
This is meant to be an ongoing tracking system. There are probably better formats/software to use, but Excel is the tool I have at hand. I'm sure there is a better way via Excel to do what I'm doing...less clunky. This is just what I came up with.
Thanks for your help and input.
Last edited by AliGW; 06-08-2018 at 01:58 PM. Reason: Unnecessary quotation removed.
Grasshopper:
Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks