Excel 2003; value depending on adjacent cells

[network_engineer]

Hi all,

How could I validate that cells do not have values higher than the adjacent cells? E.g. B1 should never be greater than A1. I know we could use the Data >> Validation; how would I do it if I wanted it across a whole range?

B1:B40000 should never be greater than the respective adjacent cells in column A?

Thanks.

Kind regards.

 

[jasonb75]

Select the whole range you want the validation applied to then set the max value permitted to =A1

Note that validation doesn't work if the reference cell is empty.

 

[Akashwani]

Hi,

Welcome to MrExcel.

Highlight column B2:B?????.
Go to Data Validation ( alt A V V )
Select Whole Number from the Allow Drop down.
Select Less than or Equal to from the Data Drop down.
In the Maximum box put =A2
You can add an Error Message to the Data Validation if you wish.
Click OK

I hope that works for you.

Ak

 

[network_engineer]

Thanks! It worked, and I copied the validation to other rows! Appreciate the help!

Kind regards,
Ben

 

[network_engineer]

Hi again,

Sorry, I hit some errors. So, let me update the cell references and then explain:

I have numerical values in cells C4, D4, andE4, i.e. C4:E4. I have a validation in place on the range C4:E4 that allows only one of these cells (C4, D4, or E4) to filled in at any given point of time.

Now, in F4, I wish that values filled would never exceed the value filled in C4 through E4.

I have the validation in place, i.e. Data Validation, Validation Criteria, Allow Whole Number, Maximum value to be C4:E4; however, this works ONLY for C4. If the value in D4 is 2, then I still get an error if I enter 1 in F4. Did I miss something?

Help!

Thanks.

Kind regards.

 

[jasonb75]

use the formula

=MAX($C4:$D4) for the max value permitted in F4.

This appears to be a duplicate of http://www.mrexcel.com/forum/showthread.php?t=550060

 

[network_engineer]

Hi!

Thanks, it worked.

I wasn't trying to duplicate, just broke down the tasks into two posts.

Thanks!

Kind regards,
Ben