Sumif / Sum Offset linking to another spreadsheet

[caledonia]

I am using a spreadsheet template to create a set of accounts. The template creates a Profit and Loss, Balance Sheet and Cash Flow with each month’s data shown in a different column i.e. January in Column C and February in Column D etc.

Following the months there are columns detailing the current month, year to date and full year. This is followed with the corresponding current month, year to date and full year figures linked from a budget model (similar format but contained in a separate spreadsheet).

Previously the links to the budget model for current month and year to date have been updated manually each month; however this month I have attempted to automate this process by using the following formulae:-

Current Month =SUMIF(‘[budgetfile]Accounts!budgetmonths’,’currentmonth’,budgetdata’)

Year to Date = SUM(OFFSET(‘[budgetfile]Accounts!C3,,,,’Month’))) – Month being numerical ie 1, 2 or 3 etc.

These formulae work fine when I have the budget file open, however if I only open my actual accounts pack, the links all return with the VALUE error, even when I don’t update my links.

Is there anyway of using these or similar formulae which would not require the budget model to be open as even when I don’t update my links, the formulae will return the VALUE error.

I appreciate any advice.

 

[Domenic]

Try...

=SUMPRODUCT(--('[budgetfile]Accounts'!budgetmonths=currentmonth),budgetdata)

and

=SUM('[budgetfile]Accounts'!C3:INDEX('[budgetfile]Accounts'!C3:N3,Month))

Adjust the range C3:N3, accordingly.

Hope this helps!

 

[caledonia]

Domenic

Thanks for the formulae. I really like this formula as a replacement for my formula using the offset.

=SUM('[budgetfile]Accounts'!C3:INDEX('[budgetfile]Accounts'!C3:N3,Month))

My only issue with this one is the same problem that I had with my offset formula, which is that if you open the Accounts file, but don't also open the Budget File, it will return a Ref, even when you select to not update links.

Is there anyway to get this formula to work where it will return the previous value if you don't update links as I send this file on for review.

Thanks again

 

[Domenic]

Try the following instead...

=SUMPRODUCT(--(COLUMN('[budgetfile]Accounts'!C3:N3)-COLUMN('[budgetfile]Accounts'!C3)+1<=Month),'[budgetfile]Accounts'!C3:N3)

Hope this helps!