Problem with validation check!

[Neo1]

Hello I tried performing a validation check so that a value is less than or equal to a value in another workbook, but it doesnt let me choose the cell from the other workbook I guess it has to be in the same worksheet your applying the validation check in right? isnt there a way i can choose the cell from the other workbook?

Thanks a lot
From John

[Duke Carey]

You can use a cell in the current workbbok to reference the cell in the other
workbook, then tie your validation to the linked cell


"Neo1" wrote:

>
> Hello I tried performing a validation check so that a value is less than
> or equal to a value in another workbook, but it doesnt let me choose the
> cell from the other workbook I guess it has to be in the same worksheet
> your applying the validation check in right? isnt there a way i can
> choose the cell from the other workbook?
>
> Thanks a lot
> From John
>
>
> --
> Neo1
> ------------------------------------------------------------------------
> Neo1's Profile: http://www.excelforum.com/member.php...o&userid=30329
> View this thread: http://www.excelforum.com/showthread...hreadid=523656
>
>

[Al]

Can you put another cell on the worksheet and link it to the other workbook.
Then use a formula in your validation < your new cell?
HTH

"Neo1" wrote:

>
> Hello I tried performing a validation check so that a value is less than
> or equal to a value in another workbook, but it doesnt let me choose the
> cell from the other workbook I guess it has to be in the same worksheet
> your applying the validation check in right? isnt there a way i can
> choose the cell from the other workbook?
>
> Thanks a lot
> From John
>
>
> --
> Neo1
> ------------------------------------------------------------------------
> Neo1's Profile: http://www.excelforum.com/member.php...o&userid=30329
> View this thread: http://www.excelforum.com/showthread...hreadid=523656
>
>

[Neo1]

yes I can but still the error message doesnt appear when i change the sell from the other worksheet only if i change it from the one that has been linked which is in the worksheet of the validation check..and i dont want to enter the value here i want to enter the value in the other worksheet...what can i do?

Thanks
From John

[Harlan Grove]

Neo1 wrote...
>yes I can but still the error message doesnt appear when i change the
>sell from the other worksheet only if i change it from the one that
>has been linked which is in the worksheet of the validation check..and
>i dont want to enter the value here i want to enter the value in the
>other worksheet...what can i do?

Validation is EASILY defeated. If you set validation on cell A1 to
accept only values > 0, then define X referring initially to 100, then
cell A1 accepts the formula =X since it initially evaluates to 100
which is > 0. Then change the definition of X to -5000. A1 will change
to -5000 without any validation error message being displayed.

If you're applying a validation rule to cell X99 in worksheet A in
workbook 1.xls based on a corresponding value in cell K43 in worksheet
B in workbook 2.xls, you can change the latter cell at will so that the
entry in the former cell becomes invalid and Excel won't complain at
all. Only when you try to change the former cell would the new value of
the latter cell come into play.

Data > Validation is barely capable of preventing honest errors by
naive but dilligent users. It's little or no help against lazy users
who can defeat validation by pasting stuff into cells from other
applications, and it's way too weak to prevent intentionally invalid
entries.

The only way to impose real validation is using Change and Calculate
event handlers coupled with do-nothing but widely used udfs that would
return #NAME? errors (and thus screw up the formulas which call it) if
users try to disable macros in order to bypass the event handlers.