Freeze Variable Cell to a Permanant Number

**jrabrfc** · 06-24-2007, 02:05 PM

Hi,
I'm trying to update a league table spreadsheet with a running total of points for each team. I've got a countif function counting the number of games, but obviously that will change as the season goes on. I want to make the cell with the countif result "Freeze" so it can't be changed.

For instance, after game 1, cell a2 will contain "teamname1" as a countif on number of games. After game 2, cell a2 will change to be "teamname2" because an extra game will have been played. I want to freeze cell a2 after the first game so I can then do a lookup on that in the running total after each game.

Sorry if this sounds complicated. I can provide the spreadsheet if required to show what I'm trying to do. Any help would be appreciated. Thanks.

**tuph** · 06-24-2007, 05:53 PM

It would probably be easier if you could zip and attach your workbook.

**jrabrfc** · 06-25-2007, 10:17 AM

I've attached my spreadsheet.

Basicall what I want to do is work out the points total on the results tab for each team, then add it to the running totals tab, without doing it manually.

I'm just lazy I suppose, but it would be nice to be able to do it.

Any ideas would be greatly appreciated!

**tuph** · 06-25-2007, 08:43 PM

Originally Posted by jrabrfc

I've attached my spreadsheet.

Basicall what I want to do is work out the points total on the results tab for each team, then add it to the running totals tab, without doing it manually.

I'm just lazy I suppose, but it would be nice to be able to do it.

Any ideas would be greatly appreciated!

Not lazy - it's smart to use Excel's functionality!

The attached uses SUMPRODUCT to calculate cumulative home and away scores. The numbers don't match what you had typed into the cells, so I may not have it right (I don't understand the scoring method) but you can tweak the formulas to suit.

Hope this helps. There's lots of info available here if you search the forum for SUMPRODUCT.

**rylo** · 06-25-2007, 09:14 PM

Hi

Try

'Running Totals'!B4=SUMPRODUCT(--(Results!$C$1:$C$61='Running Totals'!$A4),--(Results!$B$1:$B$61<='Running Totals'!B$3),Results!$G$1:$G$61)+SUMPRODUCT(--(Results!$D$1:$D$61='Running Totals'!$A4),--(Results!$B$1:$B$61<='Running Totals'!B$3),Results!$H$1:$H$61)

Copy down / across as required. You will have to extend the ranges to suit your source data.

rylo