How to Validate Data in Cell using info from another cell?

[cruss]

Ladies, Gents,

I have a column of cells that the user enters as time steps for process. The next column defines the type of time step unit, ie secs, mins, hours etc from a drop down selection list.

Each time step the user enters should be bigger than the last. I have another column that does a quick calc of the time step using the time step units to give a common base for all the time steps.

What I need is a way to validate that the user entered time step value. It should be greater than the previous user value, and gets them to re-enter the data. It should refer to the column where the calculation takes place.

I would like to use the Data Validation ability, but am not sure how to validate 1 cell based on another?

Regards

Chris

 

[mohitspamz]

Hey!
I'm not sure if this is what you want because i could not completely understand your need but may be this will give u a start.
If suppose its Column B you are populating and you want B2 to be greater than B1 and B3 to be greater than B1 to B2 and so forth do the following steps:

Select column B go to data validation choose Whole number, greater than, =Max($B$1:B1).

Then select B2 go to data validation, u'd find the formula as =Max($B$1:B2), change it to =Max($B$1:B1) and select Apply these changes to cells with same settings and click on OK.

Next select B1 and remove data validation setting to that cell only.

This data validation will ensure that every next row in the column is greater than all the rows preceding it.

Hope this helps you to atleast change it according to your needs.

Regards,

Mohit

 

[prasun_saha]

Hope this helps:
Consider col A has the "Time" to be entered, col B as the "Units" having drop down list with Secs, Mins & Hours and col c as the "Time in secs" (as per your desc it is the quick calc of the time step using the time step units to give a common base for all the time steps)

Cells A1, B1 & C1 have the col headers.

Lets consider the formula in col c is set as below to have all enetered time converted in seconds.

C2=IF(B2="Secs",A2*1,IF(B2="Mins",A2*60,IF(B2="Hrs",A2*120,"N/A")))
C3=IF(B3="Secs",A3*1,IF(B3="Mins",A3*60,IF(B3="Hrs",A3*120,"N/A")))
.... and so on

Having done so, select col A and go to Data validation, choose custom and use the formula

=IF(C$3>C1,1,0)

Then select cells A1 & A2 and go to Data validation and select "Clear All".

Then select C3 and go to Data validation and modify the formula as

=IF(C3>C2,1,0)

Then go to the "Error Alert" tab and type message meaningful to the user.

e.g. The new time stamp should be greated than the last time stamp

check the Apply these settings to all other cells with same settings and OK.

You must be done by now. Try putting times in col A and play to check the validation.

 

[cruss]

<P>Thanks guys - both methods work, but both can be fooled if you go back up the list and change an earlier entry to a deliberately high number.  Both methods still accept the too higher number.</P>
<P> </P>
<P>Any ideas how to completely fool proof it?</P>
<P> </P>
<P>Regards</P>
<P> </P>
<P>Chris</P>

 

[cruss]

Gents,

This covers all cases:-

=IF(C3>C2,IF(COUNTBLANK(C4:C4)=0,IF(C4>C3,1,0),1),0)