Data Validation with Formula
 

GOAL
I'm using data validation for columns B, C, and D. I would like to do
the same for column E based on the content of column B, C, or D.

PROBLEM
In B, C, or D - there is at least one selection in either col. B, C, or
D, but it can also pertain to one or both of the other columns (note:
the dollar amount would be the same for all entries on that row). What
formula can I use to automatically enter one of the three values in
col. E?


SAMPLE WORKSHEET
[col B] [col C] [col D] [col E]

[row 08] O & M Revenue Client Hours
[row 09] $26k - $75k 388 - 1,119
[row 10] $0 - $25k $0 - $25k 0 - 387
[row 11] $26k - $75k $26k - $75k 388 - 1,119
[row 12] $76k + 1,120 +


VALUES
Data Validation for col. B, C, D = $0 - $25k, $26k - $75k, $75k +
Values for col. E = 0-387, 388 - 1119, 1120 + (correspond to dollar
amounts)

if an entered value would be the same in any entry
try
=max(B1:D1)
if the entry can be negative it gets a little more complex
=if(max(B1:D1)=0,min(B1:D1),Max(B1:D1))

"Annabelle" wrote:

GOAL
I'm using data validation for columns B, C, and D. I would like to do
the same for column E based on the content of column B, C, or D.

PROBLEM
In B, C, or D - there is at least one selection in either col. B, C, or
D, but it can also pertain to one or both of the other columns (note:
the dollar amount would be the same for all entries on that row). What
formula can I use to automatically enter one of the three values in
col. E?


SAMPLE WORKSHEET
[col B] [col C] [col D] [col E]

[row 08] O & M Revenue Client Hours
[row 09] $26k - $75k 388 - 1,119
[row 10] $0 - $25k $0 - $25k 0 - 387
[row 11] $26k - $75k $26k - $75k 388 - 1,119
[row 12] $76k + 1,120 +


VALUES
Data Validation for col. B, C, D = $0 - $25k, $26k - $75k, $75k +
Values for col. E = 0-387, 388 - 1119, 1120 + (correspond to dollar
amounts)

Opps I read further in your posting. I can't tell what the cell levels are.
But I am pretty sure my previous posting will not work.

"Annabelle" wrote:

GOAL
I'm using data validation for columns B, C, and D. I would like to do
the same for column E based on the content of column B, C, or D.

PROBLEM
In B, C, or D - there is at least one selection in either col. B, C, or
D, but it can also pertain to one or both of the other columns (note:
the dollar amount would be the same for all entries on that row). What
formula can I use to automatically enter one of the three values in
col. E?


SAMPLE WORKSHEET
[col B] [col C] [col D] [col E]

[row 08] O & M Revenue Client Hours
[row 09] $26k - $75k 388 - 1,119
[row 10] $0 - $25k $0 - $25k 0 - 387
[row 11] $26k - $75k $26k - $75k 388 - 1,119
[row 12] $76k + 1,120 +


VALUES
Data Validation for col. B, C, D = $0 - $25k, $26k - $75k, $75k +
Values for col. E = 0-387, 388 - 1119, 1120 + (correspond to dollar
amounts)