+ Reply to Thread
Results 1 to 14 of 14

change last cell in column

Hybrid View

  1. #1
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887
    I also found another way to help, I think..

    In cell B2 on sheets April and May, you have a formula pointing to cell B38 on the previous sheet. As you mentioned, the last row might not be 38 for a given month, so instead of '=March!B38', '=April!B38', etc., use this for cell B2 in April:

    =OFFSET(March!B2,MAX(IF(NOT(ISBLANK(March!B2:B50)),ROW(March!B2:B50),0))-ROW(March!B2),0)
    For May!B2 change the month reference to April, like so:

    =OFFSET(April!B2,MAX(IF(NOT(ISBLANK(April!B2:B50)),ROW(April!B2:B50),0))-ROW(April!B2),0)
    PS - If anyone reading this has better ideas to automatically change the month, by all means do share.

    UPDATE: Those are array formulas.. you must press CTRL+SHIFT+ENTER after typing them, not just ENTER.
    Last edited by Paul; 04-04-2007 at 01:09 AM.

  2. #2
    Registered User
    Join Date
    12-31-2005
    Location
    Atkins, Arkansas
    MS-Off Ver
    2013
    Posts
    63
    All right I will try that later tonight when I get home. Thanks alot. I will let you know how it works out.

    Josh

  3. #3
    Registered User
    Join Date
    12-31-2005
    Location
    Atkins, Arkansas
    MS-Off Ver
    2013
    Posts
    63
    I can't get that formula to work right.
    =OFFSET(March!B2,MAX(IF(NOT(ISBLANK(March!B2:B50)),ROW(March!B2:B50),0))-ROW(March!B2),0)
    It just gives me the value of b2. I can change the formula to b38 and it will give it to me but whenever it is in b39 it just says 0. I have been trying a few things and still no result. did I do something wrong.


    Nevermind when I pushed CTRL+SHIFT+ENTER it worked. Woops.

    Thanks a lot

    Josh
    Last edited by jj33002; 04-04-2007 at 10:37 PM.

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887
    After typing the formula, did you press CTRL+SHIFT+ENTER? It is required of this type of formula (array formulas).

    If you're not sure, go to that cell, press F2 to activate the cell, then press CTRL+SHIFT+ENTER.

    If you only press ENTER, it will return the first value in the array B2:B50, which is why you're seeing the value from B2.

  5. #5
    Registered User
    Join Date
    12-31-2005
    Location
    Atkins, Arkansas
    MS-Off Ver
    2013
    Posts
    63
    I really appreciate the help. And the quick responses. Maybe I will have another brain teaser for you some other time.


    Josh

  6. #6
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887
    My pleasure. Glad we could help you out.

+ 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