+ Reply to Thread
Results 1 to 2 of 2

Copying New sheets using formulas involving previous sheets

  1. #1
    Registered User
    Join Date
    Columbia, SC

    Copying New sheets using formulas involving previous sheets

    Hello all, I would greatly appreciate your help.

    I have an excel file that is used to keep tabs on rental units. Each month is a new worksheet, so the worksheets across the bottom are November 08, December 08, etc. I am trying to make my sheet for January 09, and wanted to add a column for "Total Owed", so that the sheet would display the total amount owed by any tenant. So, for example, if a tenant didn't pay rent in November, the December sheet would pick up on it by showing total owed at the beginning of the month is XX"

    This is a pretty simple formula that uses "Rent Due" and "Rent Paid" from previous sheets to show an outstanding balance. The way it is now, I started on December 08, referenced back to sheet November 08 to find out what the balance is for people going forward to December. Then I tried to copy and paste this sheet into January 08, but instead of pulling the data from December 08 (the previous worksheet) it wants to pull from back to November 08.

    So.. the question is....is there a way to reference certain cells in the immediately previous worksheet without calling the sheet by name, so it will copy correctly when copied each month?

    Here is the formula on the December 08 that is not copying correctly:

    (=C2-E2+'November 08'!G2)

    When I copy this to January, the formula should be:

    (=C2-E2+'December 08"!G2)

    But when i copy it into a new sheet, it always remains 'November 08'!G2

    I know that I could just create the new sheet, change the first cell to 'December 08'!G2, and copy it down the sheet each time but I have colors, and different formats, etc. And this formula is only in every third cell sometimes and every fourth cell other times, so it would just make my life a lot easier if there was a way to get this copy to a new sheet correctly. Thanks!

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    I don't know of a way to have what you want to occur AS YOU PASTE without just using a SEARCH/REPLACE on the new sheet once you create the new sheet.

    However, I discussed this very situation in this thread with someone:


    In that thread, I show how creating a permanent "tally sheet" can be used to "tally" information from your sheets, even if the sheets haven't been created yet (they just return zero until you do create them)

    So for your application, the first sheet in your book could be a tally sheet that keeps a running total of what is going on in the other sheets. Using the INDIRECT function, you can create formulas for grabbing info off sheets as they are created.

    Read it over and see if it can be adapted to your needs.
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1