Excel 2000 - Formula Problem - WinXP O/S

[babyhuey20042003]

I am, at best, an intermediate user of Excel - I do not use macros and am not a great mathematician - That said, here is my problem.
I am the treasurer for a Senior's Society, responsible for budgets and reporting. The Bookkeeper uses Simply Accounting for monthly transactions and, at month end, prints an Income Statement of appx. 115 lines. I take this statement and, by combining lines (using $A$1) and/or ($A$1)+($A$2),reduce this statement to a 50 line (+/-) Statement for incorporation into a monthly comparison budget (budget vs actual). When I use the formula (Cell A1 in the budget = JULY08 SIMPLY cells $C$5+$C$6+$C$7) the formula bar reports, when budget Cell A1 is highlighted,='JULY08 SIMPLY'!$C$5+'JULY08 SIMPLY'!$C$6+'JULY08 SIMPLY'!$C$7. I wish to use, each month, the same Cell Addresses but from the new month without having to go through and reformulate using the new month (ie ='AUG08 SIMPLY'!$C$5+'AUG08 SIMPLY'!$C$6+'AUG08 SIMPLY'!$C$7). Can anyone help.My file copies exceed the size limit - if needed, will reduce size and forward.
Many thanks - DAVE

 

[computerman29642]

Dave, welcome to the forum.

Could you please attach a copy of the file (inserting dummy data if needed)?

 

[babyhuey20042003]

Hi computerman29642
Original File is too big to send so I have made up the attached file by copying the information from Jan.08 to Aug.08 from the original workbook, together with the Simply Income Stmts. for Juy and Aug. (they are the only ones which I am attempting copy by formula) and the Financial Stmt.for July (with cells using formula) and August (basically blank). Hope they will do the job even though the cell addresses are changed due to the copy and rename

DAVE

 

[bomb #21]

You may be able to work something using TODAY and INDIRECT.

See Sheet1 of the attached.

A1 = 28/09/2008 (I'm telling you that in case yours shows different due to regional settings).

B1: =DATE(YEAR(A1),MONTH(A1),1) ; to create first of the month for A1 month/year (displays 01/09/2008 here).

C1: ='Sep 08'!A1 ; a straightforward linking formula that you're probably used to. It says "get the value of A1 from Sep 08 sheet; result = 200.

D1: =VLOOKUP(B1,G1:H4,2,FALSE) ; G1:H4 is a 2 column lookup table, so that's "get the value from column 2 of the lookup table where column 1 = B1". Result = "Sep 08", which is a text string.

E1: =INDIRECT("'"&D1&"'!A1") ; get the value from A1 of the sheet according to D1 result. The syntax has to be spot-on.

So, to change C1 to =A1 from Oct 08 you'd have to edit the formula, as you know.

Now change (Sheet1) A1 to some date in October 08. While C2 stays the same, E2 updates.

When you've had time to take this in, post back and we'll move on to TODAY (if necessary).