More Excel help please.

  Taurus 11:59 10 Apr 2008
Locked

Can anyone help? I need to compare two cells from different work books and recieve an alert in one book should the contents of the two cells not match. The cells are both contain cash totals.
Many thanks
Dave

  VoG II 20:10 10 Apr 2008

More information needed!

Are these two files or two sheets within the same file? What are the names of the files and sheets. Which cells. Etc...

  Taurus 23:01 10 Apr 2008

Hi VoG, sorry about late response, have been out. They are two separate sheets in two separate workbooks or files. Both are usually open at the same time (I know, they could be in the same workbook, that would make life easier!. One workbook is called 'Accounts 2008' sheet 'Totals' cell varies from H5 to H50. Other book is called 'Red Book Live', worksheet 'Q2 08' cell T23. It's probably confusing, I'm confused and I developed the sreadsheet, but basically there are two seperate lots of entries in the two seperate workbooks that have different sets of figures going into each but have to have the same result in the two workbooks. I would like Excel to inform me if they are different to alert me to erroneous imput in one or other of the books. Thanks for your help.

Dave

  VoG II 09:54 11 Apr 2008

I'm not sure how we handle "'Totals' cell varies from H5 to H50". Should we be checking all of those, the last filled cell or what? Anyway, the following will check H5.

Open Accounts 2008 and right click the Excel logo just to the left of File on the menu bar. Select View Code and copy and paste the following into the white space on the right.


Private Sub Workbook_Open()
Dim path As String, xval As Variant, yval As Variant
path = ThisWorkbook.path
Application.ScreenUpdating = False
On Error Resume Next
Workbooks.Open (path & "\Red Book Live.xls")
On Error GoTo 0
ThisWorkbook.Activate
xval = Workbooks("Red Book Live.xls").Sheets("Q2 08").Range("T23").Value
yval = ThisWorkbook.Sheets("Totals").Range("H5").Value
Application.ScreenUpdating = True
If xval <> yval Then MsgBox "Entries do not tally!", vbCritical
End Sub


then close the code window using the X. Save and close the file then re-open it. If H5 doesn't equal T23 in the other book then you will get a warning message, otherwise nothing will (appear to) happen except that Red Book Live will also be open (whether it was open before or not).

NB the code assumes that both files are in the same folder.

  Taurus 20:18 11 Apr 2008

HI VoG, once again, sorry for delay in answering but work has once again raised it's ugly head (Work, the curse of the drinking classes). I will run the routine, but I'm sure it'll work, you've not let me down before. Once again, many thanks for your help.

Dave

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?