**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.*