i have a spreadsheet. The ENTRY and EXIT should tally.
ENTRY----total of col A:D is in E3.
EXIT---- total of col G:J is in K3.
i expect to get E3=k3
cell K3 is formatted as the sum of G:J
when i enter a data wrongly in anywhere between the cells G:J a warning should come in col K3 (of course after entering data in all the four cells G:J).the idea is the number in entry should tally with that of the exit. is it possible?
the excel file is attached.

On Tuesday, October 16, 2012 10:30:23 AM UTC-7, sumesh56 wrote:
i have a spreadsheet. The ENTRY and EXIT should tally.

ENTRY----total of col A:D is in E2.

EXIT---- total of col G:J is in K2.

Cell K is formatted as E2=K2

cell K2 is formatted as the sum of G:J

when i enter a data wrongly in anywhere between the cells G:J a warning

should come in col K2(of course after entering in all the four cells

G:J).the idea is the number in entry should tally with that of the exit.

is it possible?

the excel file is attached.





+-------------------------------------------------------------------+

|Filename: entry and exit.zip |

|Download: http://www.excelbanter.com/attachment.php?attachmentid=626|

+-------------------------------------------------------------------+







--

sumesh56

See if this does what you want.
Copy into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Integer, J As Integer
I = Range("E3")
J = Range("K3")
If I < J Then
MsgBox "E Does Not Equal K"
End If
End Sub

Regards,
Howard

thanks for the reply. but ,i am not able to understand what do you mean by sheet module. where can i find that?

i googled and find out the following.
Press Alt+F11 to open the Visual Basic Editor, and then click Insert, Module.i selected cell L and copied as you suggested.
then i got a message from the excel. it says-the following features cannot be saved in macro free workbooks-vb project.
to save a file with these features click No and then choose a macro enabled file type in the file type list. i have never used things like you suggested. plz help with directions. i am using excel 2007.

Hi,

It depends upon what is the type of validation you want. For example if you are only looking for a message. You can write a simple formula that compares the two total cells and return an error message. But this wouldn't stop the user from making the mistake.

If you want the user to not proceed until the two totals match you can use a VBA code and attach it to the Worksheet_Change event. Not sure if you can do this yourself. If you need my assistance for the VBA code, please let me know.

Thank You,
Prashant

thanks for the reply.I want the user to not proceed until the two totals match .of course i am not able to do it. kindly help with the VBA code.and give me detailed instructions as how to use it in the said excel sheet.

Hi,

Attached is the updated file and hopefully contains everything you were looking for.

It does the following.
Wouldn't give Error Message if any of the cells (in Entry or Exit boxes) is left empty.
Would give error if only all of the cells are filled in the two sections and their total don't match.
Moreover, it undoes your last action to resume to original state.

Let me know if this works for you.

Thanks,
Prahsant

thanks for the code. it does works . but sorry, only for the first line only. what i mean is it works as i wished for the row A3. but when I enter data in row A4., nothing happens. assumes that the code works only for A3 row only. kindly modify it so that it can work for the whole of the worksheet.

Hi,

Apologies for a delay in response. I wasn't feeling well and couldn't see your message.

I thought you are always going to use only 1 row. Here is a small edit that you need to make in the code.

replace the worksheet_change code in the thisworkbook object with the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CheckRng As Range
Dim EntryRng As Range
Dim ExitRng As Range
Set EntryRng = Range(Cells(Target.Row, 1), Cells(Target.Row, 4))
Set ExitRng = Range(Cells(Target.Row, 7), Cells(Target.Row, 10))
Set CheckRng = Application.Union(EntryRng, ExitRng)

If Not Application.Intersect(Target, CheckRng) Is Nothing Then
If Application.WorksheetFunction.CountBlank(EntryRng) = 0 And Application.WorksheetFunction.CountBlank(ExitRng) = 0 Then
If Application.WorksheetFunction.Sum(EntryRng) < Application.WorksheetFunction.Sum(ExitRng) Then
MsgBox "The Entry Total is not matching with Exit Total", vbCritical, "Error"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
End If
End If
End Sub

Hope this helps.
Prashant

i am not able to understand your idea. do you mean to say that i must prefix your present code with that of the old one. or i should do it afresh? i see something like worksheet on the left pane and change on the right pane when i open the view code dialog. what should i do? click on change? and put your new code? however i did this -



i copied the original worksheet of mine Entryexit. then i went for view code. then i pasted your presentcode. p alt f11. saved the doc as macro enabled. when i checked,
it does not work as expected. it gives "syntex error" (visual basic) whenever i type something on row2.