• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

Excel spreadsheet - rolling changes

Hi

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

Issac
0
IssacJones
Asked:
IssacJones
  • 2
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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
0
 
Saqib Husain, SyedEngineerCommented:
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.
DebitCredit.xlsx
0
 
IssacJonesAuthor Commented:
@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 :)
0
 
IssacJonesAuthor Commented:
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.
0
 
Saqib Husain, SyedEngineerCommented:
That is what my spreadsheet is doing.....without VBA.  VBA can also be used as an alternative
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now