Hi,

I have a spreadsheet with months and figures across columns, i.e:

July;August;September;October...
62000;45000;23000;56000...

Assuming dates start at A1 and figures at A2.

The figures are either pulled as actuals (from an actuals sheet) or budget (from a budget sheet) for a rolling forecast. I have a cell (I6) that updates the current date when you open the workbook. My question is - how can I create a formula that pulls actuals figures in upto the current date, and budget figures for the remaining months. This would obviously automatically move forward automatically based on the month in cell I6.

Thanks in advance. Let me know if you require any further details.

garygoodguy presented the following explanation :
Hi,

I have a spreadsheet set up with months and revenue across columns,
i.e:

July;August;September;Oct....Total, YTD Total
70000;65000;80000, 72000...Total, YTD Total

I also have a cell (I6) that updates and displays the current month when
logged in. Assuming the months start at A1 and data on A2 - how would I
go about creating a formula that only totals the YTD figure based on the
current date? I.e. if the current month is November, the sum total for
YTD only includes July, Aug, Sep, Oct, and Nov. (Please note the fiscal
year starts 01 July).

Thanks in advance. Please advise if you require any further details

If I understand correctly, months are arrange left to right starting
with Jul and ending with Jun. In the YTD column, simply sum the other
columns. Those not yet used (Dec-Jun) will be empty and so not affect
the totals. I assume you are totalling the months at the bottom and so
the same formula will work there as well as individual rows.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[quote='GS[_2_];1608097']garygoodguy presented the following explanation :
Hi,

I have a spreadsheet set up with months and revenue across columns,
i.e:

July;August;September;Oct....Total, YTD Total
70000;65000;80000, 72000...Total, YTD Total

I also have a cell (I6) that updates and displays the current month when
logged in. Assuming the months start at A1 and data on A2 - how would I
go about creating a formula that only totals the YTD figure based on the
current date? I.e. if the current month is November, the sum total for
YTD only includes July, Aug, Sep, Oct, and Nov. (Please note the fiscal
year starts 01 July).

Thanks in advance. Please advise if you require any further details

If I understand correctly, months are arrange left to right starting
with Jul and ending with Jun. In the YTD column, simply sum the other
columns. Those not yet used (Dec-Jun) will be empty and so not affect
the totals. I assume you are totalling the months at the bottom and so
the same formula will work there as well as individual rows.

--
Garry


Hi Garry,
If I understand correctly, months are arrange left to right starting
with Jul and ending with Jun. = Correct

Those not yet used (Dec-Jun) will be empty and so not affect
the totals. I assume you are totalling the months at the bottom and so
the same formula will work there as well as individual rows. = yes, but what happens next month when you open the spreadsheet and choose the following month (i.e. Jan) - I would like the YTD to sum the next month (i.e. Dec) automatically. Is this possible?

Cheers

The formulas should automatically update as you add values (assuming
Calculation mode is set to Automatic).

Alternative approach:
You could set up your months to collect values from an 'Amount' column
by date if you record dates for each revenue transaction. This is how
my bookkeeping app works, so transaction entries can be input at random
and each month column only collects entries for their respective month.

YTD is done on a 'Summary' sheet that's set up like a P&L so it shows
revenue, COGS, and Expenses by month. Totals are by month, quarter, and
YTD. This sheet collects values from 'Income' and 'Expenses', and so is
read only. YTD is by row for each section. Detail is handled via
Outlining for each Expense category, where sub categories display their
respective amounts. Detail for the Revenue sub categories are handled
the same way, though there's only one revenue category.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion