Dropdown behaviour shows different data depending on selection

TheJay

Active Member
Joined
Nov 12, 2014
Messages
364
Office Version
  1. 2019
Platform
  1. Windows
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.
xU4Coxz.png


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 :(
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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'.
 
Upvote 0
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")
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?

1cDye9E.png
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top