Automatic updating with most recent data

[RobPot]

Hi all,
Here's what I have so far:
Worksheet one ('Data Set') contains rows of different crime types which I update in columns corresponding to fornightly tactical periods (simply paste in a new column of the latest figures).
The next set of worksheets extract the data by crime type using OFFSET and calculate, plot and update Control Charts by crime type per different sheet

Q. Is there a way in which I can extract only the most recent data from these sheets (i.e. data from a particular row and the most recent column) to use in further worksheets?

Hope someone can help!

Robert

[RobPot]

No joy?
If the top row is the most recent date, is it somehow possible to get Excel just to look at the most recent date and look down the column somehow?

Hope to hear from someone!
Robert

[olasa]

Robert,
As I understand you want to extract any CrimeType-row from the last TacticalPeriodDate column? and put it...somewhere to make a certain chart.

Example
Date1....Date2...Date3......Date99
CT1
CT2
...
CT99

To extract a position: Try INDEX(Area,Row_Num, Col_num)
Row_Num=MATCH(lookup_value,...)
Col_Num=MATCH(lookup_value,...)
In your Chart sheet, refere to the lookup_value you want to extract .
And to find the last TacticalPeriodDate, try MAX(Dates)

I hope this is can lead you inte right direction.

Ola Sandstrom


Also look at: http://www.*****-blog.com/archives/2...and-intersect/