Question description:
This user has given permission to use the problem statement for this
blog.
I need help with my data validation and nested if formulas
Solved by D. C. in 19 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
05/09/2018 - 04:59
Greetings! Thank you for choosing Got It Pro!
Excelchat Expert
05/09/2018 - 04:59
So, you need help with Data Validation and Nested IFs?
User
05/09/2018 - 04:59
hi give me a sec
User
05/09/2018 - 04:59
i have a file
Excelchat Expert
05/09/2018 - 04:59
Sure go ahead.
User
05/09/2018 - 05:01
[Uploaded an Excel file]
User
05/09/2018 - 05:01
in that file, what i want to do is when the bet size: Other Amounts, I want the bet amount to be based on the K4
User
05/09/2018 - 05:02
and the conditions are: If i get a tail, i will double my bet amount for the next round
User
05/09/2018 - 05:02
and if i get a head, i will bet the initial bet size
Excelchat Expert
05/09/2018 - 05:02
Hmm, so in what cell should the formula be?
User
05/09/2018 - 05:02
can you help me modify my formula inJ8
User
05/09/2018 - 05:02
such that the above conditions are met
Excelchat Expert
05/09/2018 - 05:03
Okay, let me clarify the conditions again as per my understanding.
Excelchat Expert
05/09/2018 - 05:03
First, if the bet size = other amounts, bet amount should be K4. Yes?
User
05/09/2018 - 05:03
I have almost finished the formula already, I just cant get it the double when I get a Tail for Coin Toss
User
05/09/2018 - 05:03
yes correct
User
05/09/2018 - 05:03
hold on for a moment
Excelchat Expert
05/09/2018 - 05:03
Okay sure
User
05/09/2018 - 05:04
just want to check with you if it is possible for me to use data valdiation (listing)
User
05/09/2018 - 05:04
but at the same time allow the user to input their own values as well
User
05/09/2018 - 05:04
that means currently the option is $10,50,100
User
05/09/2018 - 05:04
is it possible for other users to input their own values such as $250?
Excelchat Expert
05/09/2018 - 05:05
So your data validation is optiosn + a custom number?
Excelchat Expert
05/09/2018 - 05:05
options*?
User
05/09/2018 - 05:05
Yes
User
05/09/2018 - 05:05
is it possible to do that?
Excelchat Expert
05/09/2018 - 05:05
hold on
User
05/09/2018 - 05:05
ok
Excelchat Expert
05/09/2018 - 05:07
Yes, we can do that.
User
05/09/2018 - 05:07
how do i do that?\
Excelchat Expert
05/09/2018 - 05:07
So what should we do first?
Excelchat Expert
05/09/2018 - 05:08
Nested IF or data validation?
User
05/09/2018 - 05:08
the data validation
Excelchat Expert
05/09/2018 - 05:08
Okay, in what cell do you want it to be?
User
05/09/2018 - 05:08
if i can do that, i have an old version of formula which should work perfectly
User
05/09/2018 - 05:08
J4
Excelchat Expert
05/09/2018 - 05:08
Okay hold on.
User
05/09/2018 - 05:08
can u see the list ?
Excelchat Expert
05/09/2018 - 05:09
Where is the list?
User
05/09/2018 - 05:09
i have already input 10,50,100
User
05/09/2018 - 05:09
a drop down list at cell J4
Excelchat Expert
05/09/2018 - 05:09
No, there's nothing.
User
05/09/2018 - 05:09
oh okay must be some error when i made a duplicate
Excelchat Expert
05/09/2018 - 05:10
In any case, that's fine.
User
05/09/2018 - 05:10
but can you help be create the list to be $10, $50 and $100?
Excelchat Expert
05/09/2018 - 05:10
Sure, got it.
User
05/09/2018 - 05:10
as well as allow the user to input any value that they want
Excelchat Expert
05/09/2018 - 05:10
Here you go.
[Uploaded an Excel file]
Excelchat Expert
05/09/2018 - 05:10
The list is in a different sheet named "Bets"
Excelchat Expert
05/09/2018 - 05:10
You can test it out, the drop down would how 10, 50 and 100.
Excelchat Expert
05/09/2018 - 05:11
But you can directly edit it
Excelchat Expert
05/09/2018 - 05:11
And there would be no errors.
User
05/09/2018 - 05:11
ok
User
05/09/2018 - 05:11
let me check
Excelchat Expert
05/09/2018 - 05:11
sure.
User
05/09/2018 - 05:13
yup that works
User
05/09/2018 - 05:13
can you tell me how did u manage to do it?
Excelchat Expert
05/09/2018 - 05:13
Okay. Sure!
Excelchat Expert
05/09/2018 - 05:13
So it's actually really simple. Just do the data validation.
Excelchat Expert
05/09/2018 - 05:13
You'd be selecting list right? Then selecting your list.
User
05/09/2018 - 05:14
yes, but how do i still allow the cells to be edited to other values?
Excelchat Expert
05/09/2018 - 05:14
In the Data Validation window, there would be the "Error Alert" tab. You just have to disable it by unticking the "show error alert" option.
Excelchat Expert
05/09/2018 - 05:14
After that, anything should be fine :)
User
05/09/2018 - 05:15
ok
User
05/09/2018 - 05:15
give me a while to try it on my sheet
Excelchat Expert
05/09/2018 - 05:15
Sure.
User
05/09/2018 - 05:18
okay i got it
User
05/09/2018 - 05:18
that would be all
User
05/09/2018 - 05:18
thanks
Excelchat Expert
05/09/2018 - 05:18
Cool!
Excelchat Expert
05/09/2018 - 05:18
Happy to have helped. Will mark this as answered now!
User
05/09/2018 - 05:18
ok
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
