# Dropdown behaviour shows different data depending on selection

#### jasonb75

##### Well-known Member
I'm sure that there must be an easier way, but this is the best that I can come up with.

=IF(ISBLANK(C6),"",SUMPRODUCT(789.03/(365+(DAY(DATE(YEAR(CHOOSE({1,2},C5,C6-B7))+(MONTH(CHOOSE({1,2},C5,C6-B7))>3),2,29))=29))*LARGE(CHOOSE({1,2,3},((C6-B7)-C5)-((C6-B7)-MIN(C6-B7,DATE(YEAR(C5)+(MONTH(C5)>3),3,31))),(C6-B7)-MIN(C6-B7,DATE(YEAR(C5)+(MONTH(C5)>3),3,31)),0),{1,2})))

#### TheJay

##### Board Regular

That's supposed to be the formula for C8 right? I'm getting the same output as before, the calculation is for 365 days as during the period tested previously.

Have I done something wrong?

#### TheJay

##### Board Regular
Oh, I see. That is the formula for F10. Looking at it, how is it going to work when the figure is anything other than £789.03 to begin with?

The figures in the top row are created by the spreadsheet according to the amount input in C3 and what that equates to when divided by 4, 2 or 8. It also does not allow for the other cells to factor in the number of days within that financial year as part of their calculation, does it?

#### TheJay

##### Board Regular

You will see that I have figured out I can point to a cell instead of having a manual number input. That's working in some of the test cells in column F but not all, some look back to C9, which I suspect is causing the remaining problems but not sure about a fix.

#### jasonb75

##### Well-known Member
I think this should do everything that you need, not sure what you're trying to do with column G though, 25% of 75%? The figures in the top box are actually 24% of 50%.

C8 (although the existing formula is fine)

=IF(ISBLANK(C6),"",C6-C5+NOT(B7))

C9

=IF(ISBLANK(C6),"",SUMPRODUCT(C3/(365+(DAY(DATE(YEAR(CHOOSE({1,2},C5,C6-B7))+(MONTH(CHOOSE({1,2},C5,C6-B7))>3),2,29))=29))*LARGE(CHOOSE({1,2,3},((C6-B7)-C5)-((C6-B7)-MIN(C6-B7,DATE(YEAR(C5)+(MONTH(C5)>3),3,31))),(C6-B7)-MIN(C6-B7,DATE(YEAR(C5)+(MONTH(C5)>3),3,31)),0),{1,2})))

C10

=C9

F8

=C9*50%

Because the other formulas are all linked to C9 they will not need changing.

Also, going back to earlier parts of the thread. I gave you the answer that you asked for, but your idea of the checkbox label changing is going to be confusing and counter intuitive to anyone but you.

"Inclusive End Date" tells people to tick the box to include the end date, not that the end date is included when the box is not ticked.
The label should always show what the box means when it is ticked, not the current status.

#### TheJay

##### Board Regular
Thanks for that.

I have posted some questions relating to this project but not specifically to my original request in another thread now.

Going back on topic then, how would I go about having a dropdown box that, when clicked, shows the option to see either 25%, 50% discount or 25% of 50% discount and then shows only the relevant rows for the selection alongside the full charge (which will always be visible)?

I ask because it's too much data horizontally and would be a much cleaner solution.

Thanks.

#### jasonb75

##### Well-known Member
I'm not going to answer that here as it looks like you're asking for something very similar in this thread, Dynamic heading, trying to combine data from multiple cells I didn't see any mention of a dropdown there, but the same solution would apply with or without.

#### TheJay

##### Board Regular

THis thread is about choosing options from a dropdown to display particular data, I still don't know how to do that as described in my previous post within this thread.

#### TheJay

##### Board Regular
Also, I moved the cells and now my check box hiding VB is not working even though I updated the code to:
VBA Code:
``````Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Case "D8"
ActiveSheet.Shapes("Check Box 1").Visible = (Len(Target.Value) > 0)
End Select
End Sub``````
I don't understand. When I input text and then delete it, nothing happens now.

#### jasonb75

##### Well-known Member
THis thread is about choosing options from a dropdown to display particular data, I still don't know how to do that as described in my previous post within this thread.
Actually, this thread was about changing the label of a checkbox, which has been done.

As I stated in my previous reply, what you are now asking for is closer to the question in your other thread than this one. To combine the content of the cells, you will need some way of choosing which part of the formula to display (which is where the dropdown will come into it).

As for code not working, do you have more than one checkbox? Each one has to be specified by name.