+ Reply to Thread
Results 1 to 8 of 8

Year to date problem

  1. #1
    Registered User
    Join Date
    03-03-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    34

    Year to date problem

    Morning all
    I would appreciate any help that you can provide me with the following problem

    Please see attached file

    I am trying to automate my budgets so when I input the month in Cell F4 on the acc tab then the highlighted Yellow Month and Highlighted Green YTD budget comes in reading from the 2nd Budget tab.

    I have managed to solve the month one and by putting in Dec in F4 I get back the months budget of 300k but I can not seem to get the YTD to work ie for Dec it should be £500k

    I have been looking at this now for a few hours and a fresh pair of eyes may help me solve what i am doing wrong

    thanks
    Attached Files Attached Files
    Last edited by bumpty; 02-13-2012 at 10:31 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Year to date problem

    There's only one Dec and it's in 2011 and it's 300k. Why 500?

  3. #3
    Registered User
    Join Date
    03-03-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Year to date problem

    it should add Nov and Dec to give me the YTD figure of £500k the £300k is going into the month ok but the YTD is not working just comes back as 0 - its the green cell M7

  4. #4
    Registered User
    Join Date
    03-03-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Year to date problem

    is anyone able to assist me with this ?

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Year to date problem

    I'm still not sure what you looking for.. There's lot of reference errors in your workbook...

    Mayb, if you could write all years in Budget 2nd row (2011_2011_2012_2012_2012....._2012)

    Then you could use: =SUMIF(budget!$B$2:$N$2, acc!M7, budget!$B5:$M5)

    is that it?

  6. #6
    Registered User
    Join Date
    03-03-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Year to date problem

    thanks but this only brings back the month not the total year to date amount
    ie if I put Feb in cell F4 - I would expect
    the month (F7) to be £500
    The Year to date (M7) to be £1400

    I have the month one working perfectly its just the YTD figure that I can not get to work - im not saying my formula is correct or the right one but im struggling to find another solution

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Year to date problem

    Here, try this:

    =SUM(OFFSET(budget!$B5,0,0,,MATCH(acc!$F$4,budget!$B$3:$M$3,0)))
    Last edited by zbor; 02-13-2012 at 10:23 AM.

  8. #8
    Registered User
    Join Date
    03-03-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    34

    Thumbs up Re: Year to date problem

    thank you - this works
    your a genious !!!


+ 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