The latest values in a block of data

[terrydennis]

I have a potential block of data say from B5 to N10. - see attached spreadsheet

Currently , there is data in B5 to I10 with Row 5 containing the date but the data block grows with time.

What would be the formula to put in cells P6 to P10 which will extract the contents of the latest column of data i.e the contents of I6, I7 , I8 , I9 ,I10 ?

 

[NARAYANK991]

Hi ,

It becomes easy if you use dynamic named ranges. See your file.

As you enter a new date in row 5 , the values in the output range will change.

Narayan

 

[terrydennis]

Thanks for that ; it is just the job. The only problem is that I do not understand dynamic named ranges or how to use them or even create them.
Is there a tutorial that you can direct me to ?

 

[NARAYANK991]

Hi ,

Here are some links :

http://www.homeandlearn.co.uk/excel2007/excel2007s7p6.html

https://exceljet.net/lessons/how-to-create-a-named-range

http://www.contextures.com/xlNames01.html

Narayan

 

[terrydennis]

Thanks for that , Narayan ! I have printed out two of those references you gave and I will have to do some homework !
In the meantime , I have another little teaser in the project I am doing which I cannot get my head round. I am attaching the file for you to look at.

 

[NARAYANK991]

Hi ,

See this file.

The definition of the named range DataRange has been changed slightly , to take into account two changes from your original file :

1. In your original file , column A had text ; in the present file , the column has numeric values.

2. In your original file , the data range started from column B ; in the present file , it starts from column C.

Narayan

 

[Somendra Misra]

Hi,

If the ranges are fixed, than for your file on comment #5 you can use below formula O6 and copy down.

=IF(INDEX(INDEX($C$6:$M$10,,MATCH(99^99,$C$5:$M$5)),ROW(A1))<>"",A6,"")

Regards,