cell validation of either set value or user input

[jamesburden]

Not sure if this is possible...

Can I set validation on a cell to only allow user input if a separate cell has a particular value, otherwise the cell has a default value of 1? For example, the narrative formula would therefore be something along the lines of:
If a1 = "POD" then b1="1" else user can put in whatever they like.

If possible I'd prefer to do this without using macros.

I really appreciate any advice/wisdom!
Thanks

 

[MisterCrash]

How about something like this?

Cell A1: Enter whatever you like
Cell B1: Enter whatever you like
Cell C1: =IF(A1="POD", 1, B1)

That way, you can enter anything you like in cell B1, and it will show up in cell C1, unless "POD" is entered in A1, in which case C1 will be set to 1.

 

[jamesburden]

Thanks MisterCrash. For various reasons I need this to be limited to two cells. Mainly because there are further calculations that come from the cell that either has a value of 1 (if a1="POD") or a user inputted value of any other number.

I guess the key question is whether it is possible within one cell to either have a set value (if conditions are met) or a user-entered value (if other conditions are met).

Any other ideas?

 

[MisterCrash]

What's wrong with having those other formulas reference cell C1 instead of B1? Is there really nowhere else on the spreadsheet where you can fit this formula, and have the other formulas reference that cell?

As far as I know, you can't allow both free data entry in a cell and force it to a set value under certain circumstances unless you use VBA. Once you enter something manually, it will overwrite any formula contained in the cell.

You can use data validation to prevent someone from entering a number in cell B1 if A1 is "POD". To do this, select cell B1, then go to Data | Validation, and in the Allow box, choose "Custom". Then, in the Formula box, enter this:

=NOT(AND((A1="POD"),(B1<>1)))

If "POD" is entered in cell A1, then cell B1 will not be changeable except to the value of 1. However, it won't set the value of cell B1 to 1.

 

[jamesburden]

Really appreciate that. This looks really useful. I'll give it a go. Thanks