Problem selecting required cells for formula

[MylesGibson]

Hi

Can anyone tell me how i can select the most recent 10 cells from a list of data to be included in a formula. I have selected it manually, for example F122:F131. This is fine but i don't know how i can put it so that it will update to the 10 most recent results when i input another line of data. So when i put in more data it stays on the previously selected. I want it to move shift down to include the new data but only the 10 most recent.

I would appreciate any help.

Regards

Myles

[David A Coop]

Hi Myles, Welcome to the forum!

If you want to add them, =SUM(INDIRECT("F" & COUNT(F:F) -9 & ":F" & COUNT(F:F)))

The INDIRECT("F" & COUNT(F:F) -9 & ":F" & COUNT(F:F)) part of the formula counts the number of rows being used in column F.

Note: It assumes that you don't have any totals or anything else in the column. If you do, you will have to adjust the count to allow for this. eg. COUNT(F:F)-1 if you have a total or something.

Hope this helps,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[TMS]

Define a Dynamic Named Range, nrColF as:

Please Login or Register  to view this content.

And then use the Array Entered formula:

Please Login or Register  to view this content.

If entered correctly, using Ctrl-Shift-Enter, this will display as:

Please Login or Register  to view this content.

You could use whole column references but that would make the workbook very slow.


Regards, TMS

[FDibbins]

Hi and welcome to the forum

As with most things in excel, there are various ways of doing the same thing. here is another option...
=SUM(OFFSET($F$1,COUNT($F:$F)-10,0,10,1))

[MylesGibson]

Thanks for your help, i can't figure how to translate that into my sheet. I should have been more specific as to what it is i'm trying to do. I have a list of results on one page in a certain column i.e. A letter in each cell, W/L/D. On another page i have a formula which calculates the 'points obtained' for the previous 10 results so to speak. The formula is as follows. I'm not sure if i've done it the most efficient or smartest way but i'm fairly novice to excel.

=COUNTIF(Sheet2!E166:E175,"W")*3+COUNTIF(Sheet2!E166:E175,"D")

What i'm trying to do is find out how i can get the "sheet2!E166:E175" part to update when i enter a new line on the results column.

I hope this explains a little better!

Regards

Myles

[MylesGibson]

Hadn't seen all the replies but managed to get it working using the offset:

=COUNTIF(OFFSET(Sheet2!E176,-11,0,11,1),"W")*3+COUNTIF(OFFSET(Sheet2!E176,-11,0,11,1),"D")

Thanks everyone for all your help.

Regards

Myles