Dropdown behaviour shows different data depending on selection

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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
Joined
Nov 12, 2014
Messages
141
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

Board Regular
Joined
Nov 12, 2014
Messages
141
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
Joined
Nov 12, 2014
Messages
141
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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
Joined
Nov 12, 2014
Messages
141
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.
 

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
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

Board Regular
Joined
Nov 12, 2014
Messages
141
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)
    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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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.
 

Forum statistics

Threads
1,078,283
Messages
5,339,281
Members
399,291
Latest member
Bdbd55

Some videos you may like

This Week's Hot Topics

Top