Hi all,

I need to track changes between my original spreadsheet and the returned
spreadsheet that has been updated by numerous people in various parts.
Currently, I have the original spreadsheet on one tab and the pieces I've
received back on another. I'm using an exact formula and then a vlookup
formula that references to the second tab to look at every cell in every row
and compare it to the "changed" tab to see if changes were actually made. If
there is an easy way to do this, please let me know. Below is the formula
I'm currently using, but it keeps telling me there's an error at the vlookup
right after I2 (I've put *'s around it, but please note, those are just there
to show which vlookup it is--they are NOT actually in the formula). It's no
different from the one's before it, so I don't see why that one in particular
is returning an error. Is there a limit to how many vlookups you can use?
Any suggestions/simplifications/help to get this to work will be appreciated!


=if(isna(vlookup(B2,returned,1,false)),"NOT YET
RETURNED",if(exact(D2,vlookup(B2,returned,3,false) )="FALSE","X",if(exact(E2,vlookup(B2,returned,4,fa lse))="FALSE","X",if(exact(F2,vlookup(B2,returned, 5,false))="FALSE","X",if(exact(G2,vlookup(B2,retur ned,6,false))="FALSE","X",if(exact(H2,vlookup(B2,r eturned,7,false))="FALSE","X",if(exact(I2,***vlook up***(B2,returned,8,false))="FALSE","X",if(exact(J 2,vlookup(B2,returned,9,false))="FALSE","X",if(exa ct(K2,vlookup(B2,returned,10,false))="FALSE","X",i f(exact(L2,vlookup(B2,returned,11,false))="FALSE", "X",if(exact(M2,vlookup(B2,returned,12,false))="FA LSE","X",if(exact(N2,vlookup(B2,returned,13,false) )="FALSE","X",if(exact(O2,vlookup(B2,returned,14,f alse))="FALSE","X","")))))))))))))

Hi Jonathan,

Have you tried using the "track changes" option?
If you switch this one before you send the sheet out the the others, then
when it gets sent back the cells which have changed will contain notes
defining the changes, and who made the changes.
I guess you've already tried this?

"Jonathan" wrote:

Hi all,

I need to track changes between my original spreadsheet and the returned
spreadsheet that has been updated by numerous people in various parts.
Currently, I have the original spreadsheet on one tab and the pieces I've
received back on another. I'm using an exact formula and then a vlookup
formula that references to the second tab to look at every cell in every row
and compare it to the "changed" tab to see if changes were actually made. If
there is an easy way to do this, please let me know. Below is the formula
I'm currently using, but it keeps telling me there's an error at the vlookup
right after I2 (I've put *'s around it, but please note, those are just there
to show which vlookup it is--they are NOT actually in the formula). It's no
different from the one's before it, so I don't see why that one in particular
is returning an error. Is there a limit to how many vlookups you can use?
Any suggestions/simplifications/help to get this to work will be appreciated!


=if(isna(vlookup(B2,returned,1,false)),"NOT YET
RETURNED",if(exact(D2,vlookup(B2,returned,3,false) )="FALSE","X",if(exact(E2,vlookup(B2,returned,4,fa lse))="FALSE","X",if(exact(F2,vlookup(B2,returned, 5,false))="FALSE","X",if(exact(G2,vlookup(B2,retur ned,6,false))="FALSE","X",if(exact(H2,vlookup(B2,r eturned,7,false))="FALSE","X",if(exact(I2,***vlook up***(B2,returned,8,false))="FALSE","X",if(exact(J 2,vlookup(B2,returned,9,false))="FALSE","X",if(exa ct(K2,vlookup(B2,returned,10,false))="FALSE","X",i f(exact(L2,vlookup(B2,returned,11,false))="FALSE", "X",if(exact(M2,vlookup(B2,returned,12,false))="FA LSE","X",if(exact(N2,vlookup(B2,returned,13,false) )="FALSE","X",if(exact(O2,vlookup(B2,returned,14,f alse))="FALSE","X","")))))))))))))

Yeah, unfortunately the person before me who sent out the sheet wasn't aware
of that feature. We've already sent out the sheet and received the multiple
changed versions back, so it's too late to implement that.

Thanks,

Jonathan

"nick" wrote:

Hi Jonathan,

Have you tried using the "track changes" option?
If you switch this one before you send the sheet out the the others, then
when it gets sent back the cells which have changed will contain notes
defining the changes, and who made the changes.
I guess you've already tried this?

"Jonathan" wrote:

Hi all,

I need to track changes between my original spreadsheet and the returned
spreadsheet that has been updated by numerous people in various parts.
Currently, I have the original spreadsheet on one tab and the pieces I've
received back on another. I'm using an exact formula and then a vlookup
formula that references to the second tab to look at every cell in every row
and compare it to the "changed" tab to see if changes were actually made. If
there is an easy way to do this, please let me know. Below is the formula
I'm currently using, but it keeps telling me there's an error at the vlookup
right after I2 (I've put *'s around it, but please note, those are just there
to show which vlookup it is--they are NOT actually in the formula). It's no
different from the one's before it, so I don't see why that one in particular
is returning an error. Is there a limit to how many vlookups you can use?
Any suggestions/simplifications/help to get this to work will be appreciated!


=if(isna(vlookup(B2,returned,1,false)),"NOT YET
RETURNED",if(exact(D2,vlookup(B2,returned,3,false) )="FALSE","X",if(exact(E2,vlookup(B2,returned,4,fa lse))="FALSE","X",if(exact(F2,vlookup(B2,returned, 5,false))="FALSE","X",if(exact(G2,vlookup(B2,retur ned,6,false))="FALSE","X",if(exact(H2,vlookup(B2,r eturned,7,false))="FALSE","X",if(exact(I2,***vlook up***(B2,returned,8,false))="FALSE","X",if(exact(J 2,vlookup(B2,returned,9,false))="FALSE","X",if(exa ct(K2,vlookup(B2,returned,10,false))="FALSE","X",i f(exact(L2,vlookup(B2,returned,11,false))="FALSE", "X",if(exact(M2,vlookup(B2,returned,12,false))="FA LSE","X",if(exact(N2,vlookup(B2,returned,13,false) )="FALSE","X",if(exact(O2,vlookup(B2,returned,14,f alse))="FALSE","X","")))))))))))))

The add-in, Compare.xla written by Myrna Larson and Bill Manville, allows
you to compare the contents of two worksheets in same or different workbooks
and highlight differences.

http://www.cpearson.com/excel/whatsnew.htm

Look for Compare.xla at 14-June-2000

Download Compare.ZIP, unzip Compare.xla and add it with rest of your
add-ins.

On Tools Menu, select add-ins and checkmark Compare add-in.
From tools menu then select Compare Sheets and follow instructions.


Gord Dibben Excel MVP




On Fri, 20 Feb 2009 09:56:00 -0800, Jonathan
wrote:

Yeah, unfortunately the person before me who sent out the sheet wasn't aware
of that feature. We've already sent out the sheet and received the multiple
changed versions back, so it's too late to implement that.

Thanks,

Jonathan

"nick" wrote:

Hi Jonathan,

Have you tried using the "track changes" option?
If you switch this one before you send the sheet out the the others, then
when it gets sent back the cells which have changed will contain notes
defining the changes, and who made the changes.
I guess you've already tried this?

"Jonathan" wrote:

Hi all,

I need to track changes between my original spreadsheet and the returned
spreadsheet that has been updated by numerous people in various parts.
Currently, I have the original spreadsheet on one tab and the pieces I've
received back on another. I'm using an exact formula and then a vlookup
formula that references to the second tab to look at every cell in every row
and compare it to the "changed" tab to see if changes were actually made. If
there is an easy way to do this, please let me know. Below is the formula
I'm currently using, but it keeps telling me there's an error at the vlookup
right after I2 (I've put *'s around it, but please note, those are just there
to show which vlookup it is--they are NOT actually in the formula). It's no
different from the one's before it, so I don't see why that one in particular
is returning an error. Is there a limit to how many vlookups you can use?
Any suggestions/simplifications/help to get this to work will be appreciated!


=if(isna(vlookup(B2,returned,1,false)),"NOT YET
RETURNED",if(exact(D2,vlookup(B2,returned,3,false) )="FALSE","X",if(exact(E2,vlookup(B2,returned,4,fa lse))="FALSE","X",if(exact(F2,vlookup(B2,returned, 5,false))="FALSE","X",if(exact(G2,vlookup(B2,retur ned,6,false))="FALSE","X",if(exact(H2,vlookup(B2,r eturned,7,false))="FALSE","X",if(exact(I2,***vlook up***(B2,returned,8,false))="FALSE","X",if(exact(J 2,vlookup(B2,returned,9,false))="FALSE","X",if(exa ct(K2,vlookup(B2,returned,10,false))="FALSE","X",i f(exact(L2,vlookup(B2,returned,11,false))="FALSE", "X",if(exact(M2,vlookup(B2,returned,12,false))="FA LSE","X",if(exact(N2,vlookup(B2,returned,13,false) )="FALSE","X",if(exact(O2,vlookup(B2,returned,14,f alse))="FALSE","X","")))))))))))))