# Problem writing formula

• In an excel workbook with one sheet showing a compilation of data from the other sheets in the book, I am unable to come up with a way to link cells in the compiled sheet to the current "moving average" values of the other sheets.

Specifically, it is to show the moving average (last four rounds) of a golfer in a single cell next to that golfer's name on a page. Each sheet in the workbook is identical and shows certain stats on the golfers. These stats are compared on the last page in the workbook.

My last name is "Happ" so here is the problem: How do I link the cell next to my name to the "current" moving average which changes each time I add another round of golf's score to the list? How do you hit a "moving target" since the moving average is a column rather than a a single cell reference?

• Hi.

In general, the way to get a formula to change it's refernece to reflect the addition of new data is to use a dynamic range as the cell reference. It is easy to set one up tht, for example, will always refer to the last 4 rows of data in a column.

So, for example, given the way you've got your data set uyp, the following will return the average for the last 4 entries on your sheet:

=AVERAGE(INDEX(Happ!D3:D59,COUNTA(Happ!D3:D59)-3,1):INDEX(Happ!D3:D59,COUNTA(Happ!D3:D59),1))

...not including the summary line you've got on row 60 of the data sheets.

If you could change the tab names so that they're all the same as the 'names' you've got on the summary stats sheet - it would make the final formulas easier to write (or at least to copy down...):

=AVERAGE(INDEX(INDIRECT(A4&"!D3:D59"),COUNTA(INDIRECT(A4&"!D3:D59"))-3,1):INDEX(INDIRECT(A4&"!D3:D59"),COUNTA(INDIRECT(A4&"!D3:D59")),1))

although all the indirects() might start playing with your file's performance.

• Thanks Paddy... I can't wait to give this one a try. I searched the internet for hours looking for a way to do this and finally came upon this group.

I'll post the outcome. Thanks again.

Don

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!