Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default Carry data from sheet to sheet automatically

I have to create a new sheet in a workbook every week. The sheet contains
repetitve formulae so I can copy and paste the entire range from say sheet 2
to my new sheet 3, but I have a running total function that has to carry
through from sheet to sheet throughout the year. When I copy and paste the
formulae from sheet 2 to my new sheet 3, the cell(s) containing the running
total formulae are still looking to sheet 1 (as it as supposed to do when it
was in sheet 2, but now it's in sheet 3, I want it to look at sheet 2
instead). Obviously I can update the formula manually to look at last week's
total in sheet 2 but I have to do this every week. Is there a way to get the
new sheet 3 to refer back to sheet 2 rather than sheet 1 automatically?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default Carry data from sheet to sheet automatically

There is nothing automatic but you could look into using Indirect which would
allow you to put the desired sheet name into a specific cell and all of the
formulas would reference that cell to determine the appropriate sheet. Note
that Indirect is volatile so if you are going to have thousands of these
formulas then you could see a performance hit.
--
HTH...

Jim Thomlinson


"Andrew Ball" wrote:

I have to create a new sheet in a workbook every week. The sheet contains
repetitve formulae so I can copy and paste the entire range from say sheet 2
to my new sheet 3, but I have a running total function that has to carry
through from sheet to sheet throughout the year. When I copy and paste the
formulae from sheet 2 to my new sheet 3, the cell(s) containing the running
total formulae are still looking to sheet 1 (as it as supposed to do when it
was in sheet 2, but now it's in sheet 3, I want it to look at sheet 2
instead). Obviously I can update the formula manually to look at last week's
total in sheet 2 but I have to do this every week. Is there a way to get the
new sheet 3 to refer back to sheet 2 rather than sheet 1 automatically?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default Carry data from sheet to sheet automatically

Thanks Jim, I don't know anything about Indirect so it looks like I'll have a
busy few lunch breaks "playing" with a new toy!

"Jim Thomlinson" wrote:

There is nothing automatic but you could look into using Indirect which would
allow you to put the desired sheet name into a specific cell and all of the
formulas would reference that cell to determine the appropriate sheet. Note
that Indirect is volatile so if you are going to have thousands of these
formulas then you could see a performance hit.
--
HTH...

Jim Thomlinson


"Andrew Ball" wrote:

I have to create a new sheet in a workbook every week. The sheet contains
repetitve formulae so I can copy and paste the entire range from say sheet 2
to my new sheet 3, but I have a running total function that has to carry
through from sheet to sheet throughout the year. When I copy and paste the
formulae from sheet 2 to my new sheet 3, the cell(s) containing the running
total formulae are still looking to sheet 1 (as it as supposed to do when it
was in sheet 2, but now it's in sheet 3, I want it to look at sheet 2
instead). Obviously I can update the formula manually to look at last week's
total in sheet 2 but I have to do this every week. Is there a way to get the
new sheet 3 to refer back to sheet 2 rather than sheet 1 automatically?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
data from sheet to sheet automatically LosLagosGC Excel Discussion (Misc queries) 1 August 15th 08 05:38 PM
How to Automatically transfer specific Data from Sheet 1 to Sheet Jman Excel Worksheet Functions 12 May 10th 07 05:35 AM
create a formula in one sheet that would read data from separate sheet automatically QD Excel Discussion (Misc queries) 0 December 8th 06 05:17 AM
carry over data to other sheet - help needed Svea Excel Discussion (Misc queries) 3 July 17th 06 07:46 AM
can entered data in sheet 1 be automatically pasted in sheet 2 Adnan Jahangir Excel Discussion (Misc queries) 1 February 23rd 06 11:06 AM


All times are GMT +1. The time now is 09:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"