More Excel help please.

  Taurus 11:59 10 Apr 08
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 08

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 08

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 08

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 08

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.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now