Return data from the most recently updated column

[Herr Rommel]

I have a spread sheet where data is filled into a new column periodically, keeping the existing data. I am looking for a formula that returns the last filed in cell in the most recently completed column.

I have used a formula which gives me the last non zero cell based on:

=LOOKUP(2,1/(C4:BB4>0),C4:BB4)

Which as the whole table contains zero’s (a result from a using a formula to pull the data in from another spread sheet) I have modified to

=IF(ISERROR(LOOKUP(2,1/(C4:BB4>0),C4:BB4)),0,(LOOKUP(2,1/(C4:BB4>0),C4:BB4)))

Which at least returns me a zero rather than an ‘#NA’.

If you look at the attached .xls you will see my problem in that I get the last number from previous columns also pulled over. I can’t get my head around how to get just the data in the whole column that was last filled in returned.

Is there a formula which will basically say find most recently completed column and copy the data from this column? It could use the fact that there will be a new date inserted as the header of the new column?

Hopefully it’s something easy, help greatly appreciated.

[martindwilson]

why L when E is the column with the latest date?

[Herr Rommel]

Firstly apologies the formulas I have used do not relate exactly to the .xls attached as I tried to clean the .xls up a bit to focus on my problem. ie the ranges are different. It is the formula in Column V that I need help with.

I would like the data from column L (Date 16-Mar) to appear in column V (Graph Data).

Column L is the most recently filled in column, the columns A to K inclusive are previous columns.

It is only the data from the most current/recent column that I want copying to column V ie the data in previously completed columns is ignored.

Hope the above explains it a bit better, sorry for the confusion.

[Herr Rommel]

Just for info, in the attached .xls I have highlighted in yellow the data that my current formula pulls. As can be seen it pulls data from various columns rather than just the most recently completed column.

[martindwilson]

well its easy with an index match to get the data from the latest date,

in V5 =INDEX($A$5:$T$19,ROW(A1),MATCH(MAX($A$3:$T$3),$A$3:$T$3,0)) but as your data is shown this will pull the values from 25 nov 2012 as that is the biggest date

or to get march data as shown assuming when a date is entered its a real date and all those 00-jan remain text until a date is put in
=INDEX($A$5:$T$19,ROW(A1),MATCH(TRUE,INDEX(ISNUMBER($A$3:$T$3),0),1))

[Only4Excel]

If after the current DATE in A3:T3 always 0 or text, simply use MATCH with -1,

=INDEX(A5:T5,MATCH(1,A$3:T$3,-1)) copy down

Or, will work with any condition,

=LOOKUP(2,1/A$3:T$3,A5:T5)

copy down.

[Herr Rommel]

Fantastic. Many thanks for all the help. The first solution I picked to try was :
=LOOKUP(2,1/A$3:T$3,A5:T5)
Based purely as this looks the simplest. It works perfectly …. many thanks ‘Only4Excel’.