+ Reply to Thread
Results 1 to 6 of 6

Formula to find most recent column of data

  1. #1
    Registered User
    Join Date
    03-30-2007
    Posts
    7

    Formula to find most recent column of data

    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!

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-30-2007
    Posts
    7
    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!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Take a look at the attached.

    Is there anything different in your setup?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-30-2007
    Posts
    7
    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

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Great!

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1