1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2000 - Formula Problem - WinXP O/S

Discussion in 'Business Applications' started by babyhuey20042003, Sep 22, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. babyhuey20042003

    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
     
  2. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Dave, welcome to the forum. :)

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

    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
     

    Attached Files:

  4. bomb #21

    bomb #21

    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).
     

    Attached Files:

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/752349

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice