Two drop-down boxes that affect each other

[C05T1N]

Hi guys,

I need to split sales (the whole 100%) into two figures (Credit and Debit) on one of my worksheets.
What I'm thinking about is having drop-down boxes on both (ranging from 0% to 100%), which I can easily do by using Data Validation -> List and just typing the values there.

The problem is that the two figures should always add up to 100%.
So, while keeping the format of drop-down lists, I need the value that the users change to somehow affect the other value (and the other way around).

Say, if the 60% in the image gets changed to 70%, the cell on the right should change automatically into 40% (while staying part of a drop-down-list that the user can change). If the 40% gets then changed into 20%, the first cell needs to read 80% automatically.

dropdowns.JPG

What is the best way to do this?


Thank you.

[dilipandey]

Hello and welcome to the forum.

Instead of an Image, please upload a sample excel workbook along with your expected output. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

[امين اسماعيلي]

hi dear friend
I think You can do this but to be honest i dont realy know what you want to do .first creat your drop down list for next cell use vlookup or index function.

[FDibbins]

I dont think what you want is possible with regular formulas. A cell can contain either a value OR a formula, it cant contain both.

I would suggest that you restrict input to ONE of the cells - with DV applied, and leave the other as a calc