Including validation within formula

ejlupien

New Member
Joined
Oct 22, 2006
Messages
6
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
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

New Member
Joined
Oct 22, 2006
Messages
6
"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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,397
Messages
5,547,718
Members
410,807
Latest member
Coldesi
Top