Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: I think this may be too easy but.......

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    its driving me crazy, I just can't figure it out.

    I have a list of months (Jan - Dec) in Column A and a list of hours worked (per month) in Column B.

    I want to set up a mechanism that will allow me to select two dates from the list and then be given the total hours worked during this period.

    Is there a function or or combination of functions that I can use to acomplish this?

    So the solution will go something like this
    Enter the two dates
    Find the position of date 1
    Find the position of date 2
    Go to the next coloumn annd Sum everything in between the two references.

    I should be able to do this but I cant, after 2 days Im very close to giving up, unless you guys can help.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



    [ This Message was edited by: Qroozn on 2002-05-01 23:03 ]

  3. #3
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There are probably tidier ways of doing this.
    In Sheet1, I created a list of months from Jan to Dec in Cells A1 to A12. I put the time values in cells B1 to B12.

    In Sheet2, cells A1 and A2, i entered the months to add between (inclusive). In cell B1, I entered the formula
    =MATCH(A1,Sheet1!$A$1:$A$12,0)
    and copied down to B2. This finds the location of the months.

    In cell C1, I entered the formula
    ="Sheet1!B"&B1
    and copied down to cell B1. This created the references i wanted added.

    In the cell I wanted the result to appear, I entered the formula
    =SUM(INDIRECT(C1):INDIRECT(C2))
    and blow me down, it worked.

    As I said, there are probably more elegant solutions, or you may be able to wrap the whole lot up into one formula, but it's the best I can do at the moment.

    Richard

  4. #4
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    As I said in the earlier post, you could wrap it all up in one formula;

    =SUM(INDIRECT("Sheet1!B"&MATCH(A1,Sheet1!$A$1:$A$12,0)):INDIRECT("Sheet1!B"&MATCH(A2,Sheet1!$A$1:$A$12,0)))

    Change the relevant sheet names and references to suit.

    HTH

    Richard

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Wow this is brilliant thanks very much.
    I was still in the process of applying your sugestions from the earlier post when I read your final instuctions.
    Im off to try it.

    Cheers mate

    Steve

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It worked perfectly.

    Thanks for all your suggestions and help guys

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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