Including validation within formula

[ejlupien]

I have the following formula in a cell
=IF(OR(A1="C",A1="H"),0,O7)

I want to include in the same cell something like a validation feature that allows selection of something other than what what the above formula dictates.

If I use the validation function, it overwrites the formula.

Ideally, what I'm looking for is something that logically would be like this

=IF(OR(A1="C",A1="H"),0,O7,ELSE VALIDATE (B1:B5))

Anyone have any ideas on how to do this (I know ELSE VALIDATE doesn't exist - I'm just trying to show my problem).

Cheers and thanks.

Erik

 

[shippey121]

i can see your formula checks for a C or an H in cell A1 if either exist then result is 0 else result is 07,

the bit i dont understand is ELSE VALIDATE B1:B5. what is in these cells, and what are you wanting to achive,

 

[ejlupien]

"i can see your formula checks for a C or an H in cell A1 if either exist then result is 0 else result is 07,"

cell O7 not the number "07"

"the bit i dont understand is ELSE VALIDATE B1:B5. what is in these cells, and what are you wanting to achive,"

B1 to B5 are numbers 1, 2, 3, 4, 5.

I'd like the cell with the formula to return 0, or what is posted in cell O7. But I'd like to allow the person using the document to be able to override what is in the cell with the formula by picking entries from cells B1 to B5 (namely numbers 1 to 5).

So far I've set up the cell with formula that ends with O7)) and I've included a validate function with the cell. Problem I encounter is that when I click on the pull down arrow and select number 1, 2, 3, 4, or 5 (using validate), the formula disappears. I'd like the formula to stay.

Cheers,

Erik

 

[pgc01]

Hi Erik
Welcome to the board

You cannot have both a formula and a value in the cell. Either one or the other.

Hope this helps
PGC

 

[Peter_SSs]

ejlupien

See if this thread gives you any ideas towards what you are looking for:
http://www.mrexcel.com/board2/viewtopic.php?t=237897