Dropdown behaviour shows different data depending on selection

TheJay

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

Well-known Member
Joined
Dec 30, 2008
Messages
6,901
Office Version
2019
Platform
Windows
You will need to link your tickbox (checkbox) to a cell (right click, format control, Cell Link), this cell will show TRUE if the box is ticked or FALSE if it is not.
You can then use simple IF formulas in your other cells based on the outcome of the checkbox cell.
 

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
Thanks for your reply. I understand what you are saying but that's not going to enable the text label for the check box to change from Exclusive to Inclusive when clicked?

In my example I have made TRUE white text as I don't want that to show. Not sure about the IF statements either right now.


The formula in C6 is =DATEDIF(C4,C5-1,"d") and if FALSE, should be =DATEDIF(C4,C5,"d"), how do I place that into an IF statement?
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,901
Office Version
2019
Platform
Windows
The label on the tickbox needs to be empty, with the formula in the cell behind it. Link the tickbox to B6 with white text.

C6 formula =IF(B6,"Incusive","Exclusive")
C7 formula =DATEDIF(C4,C5-NOT(B6),"d")

If you want to do it without the extra cell then you will need to use vba to change the checkbox label.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,901
Office Version
2019
Platform
Windows
Actually, scrap that.

Link the checkbox to C6 with blank text, apply a custom format to the cell of ;;;"Exclusive"
Then apply a conditional format to C6, using a rule formula of =C6 and a custom number format of ;;;"Inclusive"
Finally, in C7 use the formula =DATEDIF(C4,C5-NOT(C6),"d")

Maybe a little more tricky to set up that you might like, but I just tried it and it works fine.
 

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
Thanks Jason. It does work, although at the moment it works the opposite way around. I have tried manipulating it but can't figure out how to make it so that the default state shows exclusive in C6. Also, is there a way to update the text/label in C6 according to whether it is inclusive or exclusive? It should change from 'Exclusive' to 'Inclusive'.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,901
Office Version
2019
Platform
Windows
The custom cell format is the default for false, the conditional format is for true, so you just need to swap them. The label doesn't need to change, it should be blank.

For the formula in C7, just remove the NOT() function, =DATEDIF(C4,C5-C6,"d")
 

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
Thanks for your reply. Removing NOT makes sense, I'm not sure about the other bit about swapping though.

Also, unsure how the total in C8 can respond to C7 and produce a total either calculating what was originally =YEARFRAC(C4,C5-1,1)*C3 or =YEARFRAC(C4,C5,1)*C3. If the formula needs to change, how?

Is there no way for the text to change from Exclusive to Inclusive when clicked?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,901
Office Version
2019
Platform
Windows
For the formula, the same principle applies as it did with datedif. TRUE = 1, FALSE = 0, so by subtracting C6 instead of 1, you get the correct result in both cases.

=YEARFRAC(C4,C5-C6,1)*C3

For the text, as I said previously. The label on the tickbox should be blank. The TRUE / FALSE output from the tickbox in the linked cell, formatted as I explained above will show this as Inclusive or Exclusive depending on the status of the tickbox.

The label on the tickbox can only be changed with vba, but you don't need to do that.
 

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
Thank you for your reply. During testing, something went wrong and now when I click on the check box I get an error in number of days #NUM!

Do you know what will be causing this?

 

Forum statistics

Threads
1,077,824
Messages
5,336,593
Members
399,090
Latest member
Mcoca

Some videos you may like

This Week's Hot Topics

Top