Dropdown behaviour shows different data depending on selection

[TheJay]

FIrst of all, apologies for the title, I don't know how to change it and realised I wanted to use a tick box instead of a drop down as I was writing.

I have a spreadsheet which calculates the charge for a date range.

Now I want to introduce a tick box in C6, which changes whether the inclusive or exclusive end is shown in C7 and relevant data in C8. As well as the data changing, when the tick box is clicked, I'd like the text in C6 to change from 'Exclusive End' to 'Inclusive End'.

The idea is that I don't want anything in row D because I need to incorporate other data eventually in that row. Not sure how this is achieved, can it be done without hidden cells?

The forum wont let me attach the spreadsheet

 

[jasonb75]

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]

Hello Jason, thanks for your reply.

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]

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]

This will help you understand: Calculator (Example).xlsm

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]

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]

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]

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]

That thread is asking questions about combining snippets from different cells into the wording of another.

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]

Also, I moved the cells and now my check box hiding VB is not working even though I updated the code to:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address(0, 0)
        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]

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.