Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    milton keynes, Buckinghamshire
    Thanked 0 Times in 0 Posts

    Named Budget Ranges (2003)

    I have created named budget ranges on a separate "Budgets" worksheet as follows:

    ThisWorkbook.Names.Add _
    Name:="BudgetTotal", RefersTo:=ThisWorkbook.Worksheets("Budgets").Colum ns("E:Q")
    ThisWorkbook.Names.Add _
    Name:="BudgetTotalMonths", RefersTo:=ThisWorkbook.Worksheets("Budgets").Range ("E3:Q3")
    ThisWorkbook.Names.Add _
    Name:="BudgetTotalTotal", RefersTo:=ThisWorkbook.Worksheets("Budgets").Colum ns("E:E")
    ThisWorkbook.Names.Add _
    Name:="BudgetTotalYTD", RefersTo:=ThisWorkbook.Worksheets("Budgets").Range ("E4:Q4")

    Other named ranges relating various forecasts and different cost categories within forecasts will also be created.

    The following formulas held in a costs reporting worksheet correctly pick up the required budget values as follows:
    In month budget:
    =SUMIF(INDIRECT(Budgets!$B$1&"TotalMonths"),$B$2,I NDIRECT(Budgets!$B$1& "Total") INDIRECT("Budgets!"& MATCH(A9,Budgets!A:A,0)& ":"&MATCH(A9,Budgets!A:A,0)))

    Year to date budget:
    =SUMIF(INDIRECT(Budgets!$B$1&"TotalYTD"),$B$2,INDI RECT(Budgets!$B$1&"Total") INDIRECT("Budgets!"& MATCH(A9,Budgets!A:A,0)& ":"&MATCH(A9,Budgets!A:A,0)))

    Full year budget:
    =SUM(INDIRECT($B$1&"TotalTotal") INDIRECT("Budgets!"& MATCH($A9,Budgets!$A:$A,0)& ":"&MATCH($A9,Budgets!$A:$A,0)))

    Within the above $B$1 is set to "Budget" and $B$2 is the period number and $A9 is the work stream name
    In the "Budgets" sheet Column A holds all the various work streams, with the associated budgets and forecasts held along each row.

    However I have quite a few more of these budget formulas to create and I would be grateful if anybody could advise if there is an easier way to create the required budget look up formulas.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Named Budget Ranges (2003)

    I am not sure exactly what you are after, but if you are just trying to extract out a summary table of the results in a data table, why not use a pivot table, it should be able to do this automatically.


Posting Permissions

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