Hi,

not sure if VBA or a function can help.

I have a sales pipeline workbook with sheets which are added and auto named each week (Week1, Week2 etc). There is a sheet called summary at the start. a VBprog copies the previous sheet and renames it to the next Week. Included is a table of sales pipeline entries (deals). Some are calculated fields in the table. New entries can be added to the table over the weeks. None are deleted.

In each line item, I have a series of yes/no questions which 'score' the opportunity. I use conditional formats and list box to do this and store the result in the table. As we get more positive responses, the score goes up over the weeks, and thus over sheets.

I want to be able to compare this score, deal by deal and show movement - just to make it a little more interesting. I have a sort capability on each sheet. Thus the order of the deals may change week to week but the trend needs to be correct, not just referencing the same cell on the previous sheet. It has to cater for new lines to be added (new deal entries).

Any ideas anyone?