+ Reply to Thread
Results 1 to 10 of 10

So how do I do it?

  1. #1
    Fizzle
    Guest

    So how do I do it?

    In row 1 of columns A, B, C, D, and E are the numbers 1, 2, 3, 4 and
    5. So in cell D1 there is a 4. The numbers 1 thru 5 refer to the day
    of the month,

    I will enter data in rows 2 thru 10 of columns A thru D starting with
    column A which is the first day of the month. The next day I will
    enter data in column B and so on.

    I want to put a formula in cell K1 that I will describe. I want K1 to
    show the number on row 1 that represents the last column with data.
    Therefore, on the third day when I enter data in cells C2:C10 cell K1
    will read 3. The next day when numbers are put into the column D
    range, K1 will read 4.

    So how do I do it?


  2. #2
    Nick Hodge
    Guest

    Re: So how do I do it?

    Fizzle

    You could use nested IFs (In K2)

    =IF(A2="","",IF(B2="",1,IF(C2="",2,IF(D2="",3,IF(E2="",4,5)))))

    --
    HTH
    Nick Hodge
    Microsoft MVP - Excel
    Southampton, England
    [email protected]HIS


    "Fizzle" <[email protected]> wrote in message
    news:[email protected]...
    > In row 1 of columns A, B, C, D, and E are the numbers 1, 2, 3, 4 and
    > 5. So in cell D1 there is a 4. The numbers 1 thru 5 refer to the day
    > of the month,
    >
    > I will enter data in rows 2 thru 10 of columns A thru D starting with
    > column A which is the first day of the month. The next day I will
    > enter data in column B and so on.
    >
    > I want to put a formula in cell K1 that I will describe. I want K1 to
    > show the number on row 1 that represents the last column with data.
    > Therefore, on the third day when I enter data in cells C2:C10 cell K1
    > will read 3. The next day when numbers are put into the column D
    > range, K1 will read 4.
    >
    > So how do I do it?
    >




  3. #3
    Fizzle
    Guest

    Re: So how do I do it?

    Nick

    That might work but instead of 5 days used in the example I'm dealing
    with 31 and the formula becomes too long.

    -----


    On Sun, 2 Jan 2005 09:03:02 -0000, "Nick Hodge"
    <[email protected]> wrote:

    >Fizzle
    >
    >You could use nested IFs (In K2)
    >
    >=IF(A2="","",IF(B2="",1,IF(C2="",2,IF(D2="",3,IF(E2="",4,5)))))




  4. #4
    Ragdyer
    Guest

    Re: So how do I do it?

    Then try this in AF1:

    =LOOKUP(2,1/(1-ISBLANK(A2:AE2)),A1:AE1)
    --
    HTH,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "Fizzle" <[email protected]> wrote in message
    news:[email protected]...
    > Nick
    >
    > That might work but instead of 5 days used in the example I'm dealing
    > with 31 and the formula becomes too long.
    >
    > -----
    >
    >
    > On Sun, 2 Jan 2005 09:03:02 -0000, "Nick Hodge"
    > <[email protected]> wrote:
    >
    > >Fizzle
    > >
    > >You could use nested IFs (In K2)
    > >
    > >=IF(A2="","",IF(B2="",1,IF(C2="",2,IF(D2="",3,IF(E2="",4,5)))))

    >
    >



  5. #5
    Fizzle
    Guest

    Re: So how do I do it?

    Rag

    That works only if numbers are put into row 2. In practice not every
    A2:AE2 cell will have data.

    -----

    On Sun, 2 Jan 2005 01:23:35 -0800, "Ragdyer" <[email protected]>
    wrote:

    >Then try this in AF1:
    >
    >=LOOKUP(2,1/(1-ISBLANK(A2:AE2)),A1:AE1)




  6. #6
    Frank Kabel
    Guest

    Re: So how do I do it?

    Hi
    try it. This returns always the last filled cell in A2:AE.

    --
    Regards
    Frank Kabel
    Frankfurt, Germany

    Fizzle wrote:
    > Rag
    >
    > That works only if numbers are put into row 2. In practice not every
    > A2:AE2 cell will have data.
    >
    > -----
    >
    > On Sun, 2 Jan 2005 01:23:35 -0800, "Ragdyer" <[email protected]>
    > wrote:
    >
    >> Then try this in AF1:
    >>
    >> =LOOKUP(2,1/(1-ISBLANK(A2:AE2)),A1:AE1)




  7. #7
    Ragdyer
    Guest

    Re: So how do I do it?

    Can you pick a row that will *always* contain data for all the days.
    You realize that you can change it to search *any* row you desire.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "Fizzle" <[email protected]> wrote in message
    news:[email protected]...
    > Rag
    >
    > That works only if numbers are put into row 2. In practice not every
    > A2:AE2 cell will have data.
    >
    > -----
    >
    > On Sun, 2 Jan 2005 01:23:35 -0800, "Ragdyer" <[email protected]>
    > wrote:
    >
    > >Then try this in AF1:
    > >
    > >=LOOKUP(2,1/(1-ISBLANK(A2:AE2)),A1:AE1)

    >
    >



  8. #8
    Fizzle
    Guest

    Re: So how do I do it?

    Frank

    That's the problem. Data may be put into cells 2 thru 10 but if
    nothing is entered in row 2 the formula reverts to the last row 2
    entry.

    -----

    On Sun, 2 Jan 2005 11:11:49 +0100, "Frank Kabel"
    <[email protected]> wrote:

    >Hi
    >try it. This returns always the last filled cell in A2:AE.




  9. #9
    Fizzle
    Guest

    Re: So how do I do it?

    Rag

    You're right. There is a row that always has data and I will change
    the formula to work off of it.

    Thanks guys!

    -----

    On Sun, 2 Jan 2005 02:20:30 -0800, "Ragdyer" <[email protected]>
    wrote:

    >Can you pick a row that will *always* contain data for all the days.
    >You realize that you can change it to search *any* row you desire.




  10. #10
    Ragdyer
    Guest

    Re: So how do I do it?

    Thanks for the feed-back.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "Fizzle" <[email protected]> wrote in message
    news:[email protected]...
    > Rag
    >
    > You're right. There is a row that always has data and I will change
    > the formula to work off of it.
    >
    > Thanks guys!
    >
    > -----
    >
    > On Sun, 2 Jan 2005 02:20:30 -0800, "Ragdyer" <[email protected]>
    > wrote:
    >
    > >Can you pick a row that will *always* contain data for all the days.
    > >You realize that you can change it to search *any* row you desire.

    >
    >



+ 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