Excel 2000 - Formula Problem - WinXP O/S

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

babyhuey20042003

Thread Starter
Joined
Sep 22, 2008
Messages
2
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
 

babyhuey20042003

Thread Starter
Joined
Sep 22, 2008
Messages
2
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
 

Attachments

Joined
Jul 1, 2005
Messages
8,546
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).
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top