Excel question: carry data over from one timesheet to another without new formulas?

Question

I have timesheets for everyone at work saved in separate files. Each datasheet has a column for overtime worked. In the next week's datasheet a column will include this figure as time in lieu.

At the moment, I have to manually insert the previous datasheet and column into the formula to carry over this figure.

Does anyone know how to automatically do this, so that I don't have to keep reinserting the previous datasheet address into the formula.

Answer 1

Hi, Can you not just copy the cells or the columns or rows from one week to the next. For exapmle if overtime was say in column G you could highlight the cells or column, right click, copy and then place them into the new worksheet by right clicking in the first cell where you want the information to go,, right click, paste special, and then click values.

If weeks 1 overtime was in column G then this would be shown in week 2 overtime in lieu say in column C. I assume you use a new sheet per week within the same workbook

Formula is sheet2 column C=sheet1colum G paste value

Answer 2

1

Answer 3

OK. Do you know anything about Macros.

A macro is a method of recording an action or several actions.

On excel, find in the menu options at the top Macro, then record macro.

When you've clicked this, anything you do from that point is being recorded. So, you would highlight the cells, you need, copy and then go to the new sheet and paste them in. Then return to the new sheet with your cursor and STOP recording.

That macro will be called Macro1 by default. Now insert a button and assign Macro1 to it. You can place that button anywhere on your spreadsheet, or even in the icons bars at the top, and everytime you press that button, if will automatically carry out the actions for you.

Theres a knack to this, but believe me its worth learning. I work with spreadsheets all the time at work and learning about macros was the best thing i ever did. I have all sorts of commands, copies, calculations that were once manual being done inside macros automatically.

You can even take it one step further and write your own macros inside VBA, inside excel.

If you manage to learn the macros, your copy and pasting days will be over.

I promise.

Answer 4

That formula includes "Absolute Addressing", the " $ " warning signs. have you ever tried deleting those for the cellular address, making them "Relative Addressing". undecided this might remedy your subject.