Dynamically compare two slightly different copies of a table

[David Humphries]

Wonder if anyone can help with this.

I have a spreadsheet which contains information about the predicted
availability of various people, which changes from week to week.

I'm looking to provide a weekly analysis of the changes that happen, to
check from week-to-week that nothing "crazy" has happened.

The information is in this format:

Name 08/01 08/08 08/15 (etc)->
-----------------------------
Dan 0 1 1
Dave 1 1 0
Rob 0 0 0

At the moment, I've got a copy of the above table from last week on one
tab of a spreadsheet; and an up-to-date copy of the table on another
tab.

On a third tab, I've produced a simple compare like this:

Name 08/01 08/08 08/15 (etc)->
-----------------------------------------------------
Dan S1!A2-S2!A2 S1!A3-S2!A3 S1!A4-S2!A4
Dave S1!B2-S2!B2 S1!B3-S2!B3 S1!B4-S2!B4
Rob S1!C2-S2!C2 S1!C3-S2!C3 S1!C4-S2!C4

This is fine as far as it goes, but when new people start, they push
all the rows down like this:

Name 08/01 08/08 08/15 (etc)->
-----------------------------
Dan 0 1 1
Dave 1 1 0
* Donna 0 0 1 *
Rob 0 0 0

This then screws up my compare tab, because the newer copy of the table
then has its rows in different places.

I'm trying to produce a dynamic solution. I was planning to copy the
list of names from the latest sheet and then VLOOKUP the values from
the latest and previous sheet, with some IF(ISNA()) statements to avoid
errors when new people start. However, there are 300 rows and 65
columns in the table - too many for that to be a reliable solution I
suspect.

There must be an easier way to do this!

All help much appreciated,
Dave

[Morrigan]

Try using INDIRECT() when you compare the 2 sheets. As long as the new inserted row is inserted at the same place on both sheet1 and sheet2, your comparison would be accurate.


Hope it helps.

Wonder if anyone can help with this.

I have a spreadsheet which contains information about the predicted
availability of various people, which changes from week to week.

I'm looking to provide a weekly analysis of the changes that happen, to
check from week-to-week that nothing "crazy" has happened.

The information is in this format:

Name 08/01 08/08 08/15 (etc)->
-----------------------------
Dan 0 1 1
Dave 1 1 0
Rob 0 0 0

At the moment, I've got a copy of the above table from last week on one
tab of a spreadsheet; and an up-to-date copy of the table on another
tab.

On a third tab, I've produced a simple compare like this:

Name 08/01 08/08 08/15 (etc)->
-----------------------------------------------------
Dan S1!A2-S2!A2 S1!A3-S2!A3 S1!A4-S2!A4
Dave S1!B2-S2!B2 S1!B3-S2!B3 S1!B4-S2!B4
Rob S1!C2-S2!C2 S1!C3-S2!C3 S1!C4-S2!C4

This is fine as far as it goes, but when new people start, they push
all the rows down like this:

Name 08/01 08/08 08/15 (etc)->
-----------------------------
Dan 0 1 1
Dave 1 1 0
* Donna 0 0 1 *
Rob 0 0 0

This then screws up my compare tab, because the newer copy of the table
then has its rows in different places.

I'm trying to produce a dynamic solution. I was planning to copy the
list of names from the latest sheet and then VLOOKUP the values from
the latest and previous sheet, with some IF(ISNA()) statements to avoid
errors when new people start. However, there are 300 rows and 65
columns in the table - too many for that to be a reliable solution I
suspect.

There must be an easier way to do this!

All help much appreciated,
Dave