# Posts by Boycie92

Re: Number of Calendar Days between the first Start Date &amp; the last End Date

AliGW - As always your solutions are perfect!

Thanks,
Boycie92

Re: Number of Calendar Days between the first Start Date &amp; the last End Date

Quote from Luke M;795335

Could 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

## Files

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

## Files

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)?

## Files

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.

Boycie92

## Files

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

## Files

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

## Files

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

## Files

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