Hello:
I have data that is inserted weekly into a new worksheet in the same
workbook. This data is aggregated into a single worksheet. The final
worksheet is 2 columns. Column A has the person's name, and Column B has the
total for the person. I have a macro set to automatically sort by Column B
when the worksheet is selected.

What I would like to do is have a third column that would tell me how many
spots an individual gained or lost from the previous week. I would like for
this process to happen automatically when the worksheet is selected if
possible. For example:

A B C

John 27 +3
Sallie 31 -1


Any ideas?

Thanks.....

Hi Brad

Try the below workaround...Below mentioned are the assumptions/criterias...

--The weekly sheets are named as 'Week01' 'Week02' (2 digit numerics)

--Try the below formula in sheet 'Week02' C2. The below formula will compare
the value of 'Week02' B2 aganist the value for the name in Sheet 'Week01' and
display the difference

=B2-SUMIF(INDIRECT("'Week" & TEXT(RIGHT(CELL("filename",$A$1),2)-1,"00") &
"'!A:A"),A2,INDIRECT("'Week" & TEXT(RIGHT(CELL("filename",$A$1),2)-1,"00") &
"'!b:b"))

--If you want to mention the difference as +, - or = then select Column C
FormatCellsCustomType:

+#;-#;"="

If this post helps click Yes
---------------
Jacob Skaria


"Brad" wrote:

Hello:
I have data that is inserted weekly into a new worksheet in the same
workbook. This data is aggregated into a single worksheet. The final
worksheet is 2 columns. Column A has the person's name, and Column B has the
total for the person. I have a macro set to automatically sort by Column B
when the worksheet is selected.

What I would like to do is have a third column that would tell me how many
spots an individual gained or lost from the previous week. I would like for
this process to happen automatically when the worksheet is selected if
possible. For example:

A B C

John 27 +3
Sallie 31 -1


Any ideas?

Thanks.....

Thanks Jacob,
I am running a race pool, and the worksheets are named for the location of
the race, ie Daytona, Charlotte.. I have a formula there that looks to the
race results and matches each person's picks with the finishing position.

I have the results linked to a worksheet that has each person in the pool
and total them there. This worksheet is not sorted. That worksheet named
"Totals"

That's where I pull the numbers for the worksheet I'm sorting. It's titled
"Standings" The standings worksheet is the only worksheet that is sorted..

I hope that makes sense..

"Jacob Skaria" wrote:

Hi Brad

Try the below workaround...Below mentioned are the assumptions/criterias...

--The weekly sheets are named as 'Week01' 'Week02' (2 digit numerics)

--Try the below formula in sheet 'Week02' C2. The below formula will compare
the value of 'Week02' B2 aganist the value for the name in Sheet 'Week01' and
display the difference

=B2-SUMIF(INDIRECT("'Week" & TEXT(RIGHT(CELL("filename",$A$1),2)-1,"00") &
"'!A:A"),A2,INDIRECT("'Week" & TEXT(RIGHT(CELL("filename",$A$1),2)-1,"00") &
"'!b:b"))

--If you want to mention the difference as +, - or = then select Column C
FormatCellsCustomType:

+#;-#;"="

If this post helps click Yes
---------------
Jacob Skaria


"Brad" wrote:

Hello:
I have data that is inserted weekly into a new worksheet in the same
workbook. This data is aggregated into a single worksheet. The final
worksheet is 2 columns. Column A has the person's name, and Column B has the
total for the person. I have a macro set to automatically sort by Column B
when the worksheet is selected.

What I would like to do is have a third column that would tell me how many
spots an individual gained or lost from the previous week. I would like for
this process to happen automatically when the worksheet is selected if
possible. For example:

A B C

John 27 +3
Sallie 31 -1


Any ideas?

Thanks.....