Entering a number on any sheet one time only
 

hello everyone

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

Paul Maynard
Moscow
Russia


--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile: http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=528506

Entering a number on any sheet one time only
 

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,VLOOK UP(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.

Entering a number on any sheet one time only
 

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


--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile: http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=528506

Entering a number on any sheet one time only
 

You can attach via this forum.

---BigPig---


--
bigpig
------------------------------------------------------------------------
bigpig's Profile: http://www.excelforum.com/member.php...o&userid=32027
View this thread: http://www.excelforum.com/showthread...hreadid=528506

Entering a number on any sheet one time only
 

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 |
+-------------------------------------------------------------------+

--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile: http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=528506