Formula to find most recent column of data

**cm_027** · 06-07-2007, 07:51 AM

Greetings, all:

I have a spreadsheet where I summarize data monthly. I have the months in columns. Each month, I add a column full of data. On another sheet, is the most recent month's data. For example:

Please Login or Register  to view this content.

What I am looking to do is automatically fill SHEET2's data based on the right-most column of data. When I put information in for April, I would like SHEET2 to automatically update with April's data. I can do this in VBScript easily enough but then I have to maintain the VBScript along with all of the signing or macro warnings.

What is a formula to put into SHEET2's data columns to get the most recent month of data?

Thanks!

**NBVC** · 06-07-2007, 08:19 AM

To bring the header over,

Try:

=INDEX(Sheet1!1:1,MATCH(9.99999999E+307,Sheet1!2:2))

To bring the data over,

Try:

=LOOKUP(9.9999999E+307,Sheet1!2:2) copied down.

This assumes your header is in Row 1 of Sheet1 and the data begins in Row 2.

**cm_027** · 06-07-2007, 08:33 AM

Thanks for the reply and hint.

It does not seem to be working for me, though. I'm getting the first column with data, not the last. In my sample data, I am getting January, not March. Adding April data has no effect.

I'm still working with the formulas. I think this is pointing me in the right direction but is not quite there yet.

Thanks! I appreciate the quick response!

**NBVC** · 06-07-2007, 08:42 AM

Take a look at the attached.

Is there anything different in your setup?

**cm_027** · 06-07-2007, 08:58 AM

A profound thanks.

Now, I have to look through what I typed and determine where I fat-fingered it. It is working perfectly now, exactly as you had originally stated.

Thank you