Entering a number on any sheet one time only

P

paulrm906

hello everyone

What I have is a sheet which is called "Credit Slip No" with al
numbers from 1 to 1000 in Column A, now when they enter the Vouche
number on any sheet (which may end up being lots of sheets) all th
information pertaining to this Credit Slip No is then recorded in th
sheet "Credit Slip No". Now my question is. Is it possable to preven
any person from entering the same Credit Slip No more then one time o
any of the other sheets beside the sheet called "Credit Slip No". Thi
Credit Slip No when entered on the other sheets is always entered int
column A. So in actual fact the number is already in Sheet "Credit Sli
No" in column A so now it can only be entered one more time on any o
the other sheets. I hope I have not totally confused everyone with m
explanation.

Paul Maynard
Moscow
Russi
 
B

BigPig

Hi Paul,

Not knowing how your workbook is set up, could you put in a formula in your
spreadsheets, that evaluates the 'Credit Slip No' against a cell next to it
if it is blank or not?

For example: If a sheet other than 'Credit Slip No', in cell a1, as you
stated the user types in a number or uses data validation and then puts in
the pertinent information in the row, then somewhere within that sheet (I
used cell E4) you could type
=IF(OR(VLOOKUP(A1,Credit_Slip_No!A1:B10,2)=0,VLOOKUP(A1,Credit_Slip_No!,A1:B10,2)=""),"Slip Number Available!","Credit Slip No. Taken")

Where A1 holds the credit slip number. 'Credit_Slip_No' is the name of the
sheet that I used. A1:B10 in the range that I used, yours would be much
larger.

What the formula does is if a user selects a number it checks the 'Credit
Slip No' Sheet, looking for that number and one cell to the right of that to
see if there is anything in it. If there is something in it, then it will
return "Credit slip No. Taken", otherwise "Slip Number Available!" will be
displayed.

There are a few ways to do this, I picked one that I thought would be the
easiest.

Hope this helps.
 
P

paulrm906

Hello Bigpond

Not 100% sure what you are trying to tell me and I have given it a try
but cannot seem to understand. It is a little bit hard to explain
exactly what my program and sheets look like. If you like I could
e-mail a copy of my program to you and then we could take it from
there. Plus I could then give you a bit of a longer explanation so you
can understand a bit better also

Paul Maynard
Moscow
Russia
 
P

paulrm906

Hello Bigpig

Ok I am going to attach the program but I cannot be 100% sure I am
doing doing everything correct but here goes, for obvious reasons in
the program the names have been changed. If you are trying to work how
the formulas in the sheet call "Credit Slip No" work, they will not
work unless the sheet name and the persons ID No are the same then all
that information will be recorded on the main page.
The file name is

Paul Maynard
Moscow
Russia


+-------------------------------------------------------------------+
|Filename: SLOTS CREDIT.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4566 |
+-------------------------------------------------------------------+
 
Top