Most recent data selection

[John Gresko]

I am trying to select the most current data for a particular month from a spreadsheet and copy that number to another cell. I run a query weekly that updates the spreadsheet and places a date updated in a column. I need to find the most recent number from column P and copy it into another cell. The following week I need it to change to that weeks number. Thanks!!

P M C F 8 Updated
245 346 35 18 0 6/1/2011
223 368 38 21 0 6/10/2011
260 433 37 21 0 6/16/2011
252 444 37 21 0 6/22/2011
248 453 36 21 0 6/28/2011

[Killer42]

There are a couple of approaches you could take here. One is to write a script which is executed when the sheet is updated, to find the latest P value and copy it. Another would be to put a formula in your cell which uses a look-up (check the Vlookup and Hlookup functions - I forget which is which) to always show the latest.

I suspect you may need to rearrange the columns for the look-up function to work. The idea is that it scans an array for a value (that would be today's date) or the nearest match, then goes across a specified number of columns and picks up the value there. To go left (to your P column) would be a negative number, and I don't know whether that's supported.