Your question doesn't clearly convey the layout of your BTA sheet, but let's
assume that Column B is for Jan, C is for Feb, etc., and that row 1 contains
headers. Data starts in row 2

To find the correct row that you want to sum, use the MATCH() function.
Change the client ref to whatever cell you're using to hold the client ref.

=MATCH(client ref,BTA!$A$2:$a$500,0)

Now you want to sum all the completed months in that row, so we'll
incorporate the MATCH() formula into the SUM()

=SUM(OFFSET(BTA!$A$1,MATCH(client ref,BTA!A2:a500,0),1,1,MONTH(TODAY())-1))

Finally, to get the portion of the current month, we'll simplify by using
one of the Analysis Toolpack functions [Tools- Add-ins... and make sure the
Analsysis Toolpack is checked].

+OFFSET(BTA!$A$1,MATCH(client
ref,BTA!A2:a500,0),1,MONTH(TODAY()),1)/DAY(EMONTH(TODAY(),0))*DAY(E1)


In simple terms, the MATCH() function says how many rows to move down from
the top of the BTA sheet to find the proper client's row. The SUM(OFFSET())
will sum the number of columns representing the completed months, starting
with column B. You may need to replace the 3 TODAY() functions in the
formula with a reference to your cell E1

"Sarah (OGI)" wrote:

I have the following formula which adds the budget values for all complete
months of the current year together with a pro-rata'd budget value for the
number of days in the current (incomplete) month.

=SUMIF('Summary 2010 GWP
phasing'!$B$4:$M$4,"<="&DATE(YEAR(TODAY()),MONTH(T ODAY()),DAY(0)),'Summary
2010 GWP phasing'!$B$24:$M$24)+((SUMIF('Summary 2010 GWP
phasing'!$B$4:$M$4,"="&DATE(YEAR(TODAY()),MONTH(TO DAY()),DAY(1)),'Summary
2010 GWP
phasing'!$B$24:$M$24)/DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)))*DAY($ E$1))

For example, I am creating a report up to the 13th Feb so I need to combine
the total budget for Jan with 13 days of the Feb budget.

The formula works fine and does what I need. However, I now need to do this
for a list of specific clients but I'm not sure how to factor in a reference
to the relevant client. I was thinking a vlookup but I'm not sure.

I have a list of clients in a sheet labelled League Table and the budget
figures in a sheet labelled BTA. The BTA sheet shows client refs in column A
and 12 (monthly) budget figures in subsequent columns.

Can anyone tell me how best to incorporate a specific client reference.

Many thanks in advance.