Excel spreadsheet - rolling changes

Posted on 2012-08-24
Last Modified: 2012-08-24

I'm hoping somebody can give me some advice on an Excel question.

I have an Excel spreadsheet, which has multiple tabs associated with departments, in which data has to be entered each week in. For the sake of argument, let's assume that each tab contains two columns "Credit/Debit Previous" and "Credit/Debit Current".

In week 1, I will enter data into the spreadsheet and this will populate the "Credit/Debit Current" column; "Credit/Debit Previous" will be empty at the beginning. The next week, I manually copy the values of "Credit/Debit Current" from the week 1 to the "Credit/Debit Previous" of the week 2 sheet. This process repeats as the weeks pass.

My question is, is there a way to automate this process of copying the necessary values of one sheet to the new sheet?

For reasons I won't go into, the data for each week has to be included in seperate spreadsheet files.

Thanks in advance

Question by:IssacJones
    LVL 50

    Expert Comment


    the description of the scenario is a bit confusing. Do you have a sheet for each department or do you have a sheet for each week?

    >>  multiple tabs associated with departments

    >>  copy the values of "Credit/Debit Current" from the week 1 to the "Credit/Debit Previous" of the week 2 sheet

    Manual copying should not be required. Formulas can do that job.

    It also sounds as if you are entering data into different sheets that have a report format. That is not good practice. Data should be entered into one table, on one sheet only. Then you can create reports by week or by department with pivot tables or other techniques.

    Please post a sample with dummy data that illustrates what you are trying to achieve.

    cheers, teylyn
    LVL 43

    Accepted Solution

    Normally such things are done by VBA. But it is also possible, with some limitations, without VBA. See attached file. for every new week make a copy of the T sheet and then rename that copy to the new week number and it will pick up the current values of the previous week in the previous column.

    Author Comment

    @teylyn - each sheet (file) has different departments on tabs. I agree it isn't good practise but it is what has been inherited and the powers that be don't want major changes :(

    @ssaqibh - I suspected VBA would be an approach. I haven't really done any but I will check your code and do some reading. It may be exacly what I need :)

    Author Comment

    I'm possibly confusing things with the terminology I'm using. I haven't really used Excel before!

    A spreadsheet (which I associated with a file) is for one week, with different tabs (sheets?) for each department.

    Data is entered in one spreadsheet for a week and the following week I want the data from the previous week to be transferred over to the new spreadsheet.

    I agree it isn't ideal way to do it.
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    That is what my spreadsheet is doing.....without VBA.  VBA can also be used as an alternative

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now