Re: Number of Calendar Days between the first Start Date & the last End Date
AliGW - As always your solutions are perfect!
Thanks,
Boycie92
Re: Number of Calendar Days between the first Start Date & the last End Date
AliGW - As always your solutions are perfect!
Thanks,
Boycie92
Re: Number of Calendar Days between the first Start Date & the last End Date
Quote from Luke M;795335Could do this array:
=MAX(IF(D2:D999<>"Exempt",C2:C999))-MIN(IF(D2:D99<>"Exempt",B2:B999))
Remember to confirm using Ctrl+Shift+Enter, not just Enter. Side note, I'm not exactly sure where you data is, so may need to adjust range sizes. Just make sure all the ranges are the same size.
Is there anyway to edit this formula so that if my table included blank rows (Start Date & End Date) to ignore them. At the moment the formula is using the blank values and giving me the wrong difference?
Thanks,
Boycie92
Re: Auto populating drop down lists/cells based on conditions
AliGW you are my hero! It works like a charm!
Thank You
Boycie92
Re: Auto populating drop down lists/cells based on conditions
If you don't mind spending more time on this then of course...
Re: Auto populating drop down lists/cells based on conditions
Yes, if I came to a row/date in knew was exempt I would then overwrite the formula. There is also know way I couple predict what rows would be exempt.
The table will be added to in time, so I would need the formula to populate in any new rows. Which I understand with the manual intervention isn't possible.
I am assuming it wont work they way I want it to? I think I will stick to the drop down menus!
I thank you fall all your time on this.
Boycie92
Re: Auto populating drop down lists/cells based on conditions
Hi,
In example 4, I have giving you an idea of what I am expecting. The option in E9 has been selected using a drop down menu. In E10 I have entered =F9. I have then dragged the formula down into the other rows. When it has came to an exempt row (E12) I have manually entered this term. In E14 (highlighted red) I have manually entered in B because that is the last recommended stage.
To be honest I don't know if this will work they way I want it to but I will rely on your expert advice! It may be simpler for me to stick to the dropdown list in Example 1?
Thanks,
Boycie92
Re: Auto populating drop down lists/cells based on conditions
Hi AliGW,
You logic is spot on.
That row is exempt because in certain cases we want to record the data but not actually use it in any other capacity (Crazy I know!). In the actual real life scenario it makes sense
Hence the reason why I need a solution that sees and uses the last recommended option that is not exempt.
Thanks,
Boycie92
Re: Auto populating drop down lists/cells based on conditions
Hi AliGW,
I have updated example 3 to include the possible scenario I would encounter. I have manually updated the conditional column
Its sill a small sample size so if it needs to be enlarged let me know!
Thanks for working on this with me,
Boycie92
Re: Auto populating drop down lists/cells based on conditions
Hi,
Thanks I'm glad I was able to make that a bit clearer.
Yes its likely there will be multiple exempts and the table may get pretty big
thanks,
Boycie92
Re: Auto populating drop down lists/cells based on conditions
Hi,
Initially the drop down box was used for the entire column (E) see example 1. So if I Place A in E9 and it recommended B in F9 I would place B in the next row (E10) using the drop down menu.
The solution I want is to automate this but the exempt part is confusing me.
I have used the drop down box for spelling reasons. Spell it wrong and column F won't work correctly (As this is a sample if have replaced these terms with letters)
Thanks,
Boycie92
Re: Auto populating drop down lists/cells based on conditions
Hi AliGW,
sorry I think I could have made this bit clearer.
In example 2 the only way I could get the cells (E) to auto populate was to remove the data validation and use the =F9 formula.
If I just let the formula runs its course without manual intervention. I get the scenario in Example 3. The formula see exempt within F12 and my current solution then populates E13 with exempt (not what I want)
Is their anyway to write a formula that then looks at the last recommended outcome (F11)and puts that in its place (E13)?
Hi,
I am wondering if someone can help.
I have attached a sample spreadsheet.
Tab Example 1 is what I currently use but I am looking for a solution that will provided me with the results seen in Example 2.
Essentially I only want to enter the condition once in E9 (drop down list). The formula will look at the recommendation in F9 and auto populate this in E10 and so on.
I have entered =F9 into E10 and then dragged the formula down. The problem I am having is when it comes to my “exempt” condition.
If I have this entered I want a formula that is able to see the last recommended condition used and insert that instead.
In Example 2 the condition in E12 is exempt and in F12 the recommendation is exempt. I have then manually entered B into E13. As B was the last recommendation (F11)
Ideally I would like to keep my drop down menus but any solution would be great.
Thanks in advance,
Boycie92
Re: Sequence Formula help
Hi AliGW
That's seems to have done the trick. Thank You!
Boycie92
Re: Sequence Formula help
Strange. I have the issue with the old last row and not the new one. Please see below:
G9 =IF([Condition]<>"Exempt",COUNTIF($E$9:E9,E9),0)
G10 =IF([Condition]<>"Exempt",COUNTIF($E$9:E10,E10),0)
G11=IF([Condition]<>"Exempt",COUNTIF($E$9:E11,E11),0)
G12 =IF([Condition]<>"Exempt",COUNTIF($E$9:E12,E12),0)
G13 =IF([Condition]<>"Exempt",COUNTIF($E$9:E15,E13),0)
G14 =IF([Condition]<>"Exempt",COUNTIF($E$9:E14,E14),0)
if it was to insert another row (15) it would look like this:
G14 =IF([Condition]<>"Exempt",COUNTIF($E$9:E15,E14),0)
G15 =IF([Condition]<>"Exempt",COUNTIF($E$9:E15,E15),0)
See how whenever I enter a new row the formula in the previous last row changes? this is the issue I am having. I don't wait it to change.
Thanks,
Boycie92
Re: Sequence Formula help
Hi,
apologies, I didn't realize I deleted it!
I have attached the correct example now.
when I add in a new row I am just typing in bellow the previously row so I will start writing a date into B14 this creates a new record in the table. As soon as I create it, the formula in G13 changes.
Thanks,
Boycie92
Re: Sequence Formula help
Apologies for the massive delay. Please see that attached example.
I have added in a new row and you can see in G13 that the formula has changed to this: =IF([Condition]<>"Exempt",COUNTIF($E$9:E14,E13),0) when it should be this =IF([Condition]<>"Exempt",COUNTIF($E$9:E13,E13),0)
It seem to start referencing the new row that's been added
Thanks,
Boycie92
Re: Sequence Formula help
Thank you for taking the time to help me with this.
I don't think i have been very clear about my problem so i apologies for that.
Essentially i need the formula to count each instance of a term,
So if A appears once (Cell F9) then in cell G9 it should say 1
If B appears once (Cell F10) then in cell G10 it should say 1
If C appears in Cells F11 & F12. In Cells G11 it should say 1 and in cell G12 it should say 2.
The sequence should restart once a new option has been selected.
I hope this helps make it a bit clearer! Sorry Again,
Boycie92
Re: Sequence Formula help
My apologies,
This assumes i have used the solution correctly. The named range only applies to one option (cell F9) . So if i have five terms in column F, the first one (F9) would be used.
The count if part would then only count the number of instances for the term in cell F9.
Again this is based on the way i have used the solution, if i have applied it wrong, then please let me know.
Thanks,
boycie92
Re: Sequence Formula help
Hi,
Carim, i have tried your suggestion and it is not exactly what i need. If you look at my example, you will understand why.
KjBox - Yes column F is within the same table. Please see my attached example.
Thank you both,
Boycie92
Hi,
I was wondering if someone could help.
I have this formula:
=IF([@[Stage*]]<>"Exempt",COUNTIF($F$9:F9,F9),0)
It has been used to count the number of instances per term used in column F.
I have a table the problem is that when i add in new rows the formula in the row above alters and says something like this
=IF([@[Stage*]]<>"Exempt",COUNTIF($F$9:F11,F10),0)
Is there any way to stop this from happening?
Thanks,
Boycie92