Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 43
Default reference cell from previous worksheet without "naming" worksheet

I have a worksheet that has multiple pages. In one cell there is a formula
that references a cell within that worksheet and then adds it to a cell from
the previous worksheet. Every week this worksheet is copied for the new week
using the "move/copy function". I would like for this cell to automatically
change to the previous worksheet without having to manually change it.

Ex. =sum(i17+'week1'!i20) This is the content of the cell in week 2.
=sum(i17+'week2'!i20) This is the content of the cell in week 3.
=sum(i17+'week3'!i20) This is the coentent of the cell in week 4.
I would like for the reference to the previous week to automatically advance
to the next week. Any suggestions???


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default reference cell from previous worksheet without "naming" worksheet

One way:

http://www.mcgimpsey.com/excel/udfs/prevsheet.html

In article ,
Kristin wrote:

I have a worksheet that has multiple pages. In one cell there is a formula
that references a cell within that worksheet and then adds it to a cell from
the previous worksheet. Every week this worksheet is copied for the new week
using the "move/copy function". I would like for this cell to automatically
change to the previous worksheet without having to manually change it.

Ex. =sum(i17+'week1'!i20) This is the content of the cell in week 2.
=sum(i17+'week2'!i20) This is the content of the cell in week 3.
=sum(i17+'week3'!i20) This is the coentent of the cell in week 4.
I would like for the reference to the previous week to automatically advance
to the next week. Any suggestions???

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Unnecessary use of SUM function, was reference cell from previous worksheet without "naming" worksheet

If you want =i17+'week1'!i20, why have you included the unnecessary SUM
function, with only one argument?

=sum(i17,'week1'!i20) would have been another way of doing it, with two
arguments to the SUM function, but with only one argument you might equally
have used =product(i17+'week1'!i20) or =average(i17+'week1'!i20) or
=min(i17+'week1'!i20) or =median(i17+'week1'!i20) or =max(i17+'week1'!i20)

Admittedly the help for SUM does say:
"SUM(number1,number2, ...)
Number1, number2, ... are 1 to 30 arguments for which you want the total
value or sum."
so 1 argument is legal and doesn't cause an error, but it is meaningless in
that case.
--
David Biddulph

"Kristin" wrote in message
...
I have a worksheet that has multiple pages. In one cell there is a formula
that references a cell within that worksheet and then adds it to a cell
from
the previous worksheet. Every week this worksheet is copied for the new
week
using the "move/copy function". I would like for this cell to
automatically
change to the previous worksheet without having to manually change it.

Ex. =sum(i17+'week1'!i20) This is the content of the cell in week 2.
=sum(i17+'week2'!i20) This is the content of the cell in week 3.
=sum(i17+'week3'!i20) This is the coentent of the cell in week 4.
I would like for the reference to the previous week to automatically
advance
to the next week. Any suggestions???




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default Unnecessary use of SUM function, was reference cell from previous worksheet without "naming" worksheet

"David Biddulph" <groups [at] biddulph.org.uk wrote...
....
so 1 argument is legal and doesn't cause an error, but it is meaningless
in that case.

....

Not meaningless, just useless/pointless/inefficient/. . .


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
need formula to reference a cell in previous worksheet Jim Excel Worksheet Functions 3 January 5th 07 05:05 AM
Naming cell ranges, copying formulas for a range & nesting "IF" fu DonF Excel Discussion (Misc queries) 3 October 5th 06 05:47 PM
Macro to Create New Worksheet and Reference Cell in Old Worksheet As Tab Name - "Object Required" Error [email protected] Excel Discussion (Misc queries) 4 September 25th 06 01:35 PM
Why is "History" a "reserved name" while naming Excel worksheets? Pradeep Excel Discussion (Misc queries) 1 June 30th 06 12:55 PM
"Naming a worksheet" question brianTmcnamara Excel Discussion (Misc queries) 1 January 30th 06 08:41 PM


All times are GMT +1. The time now is 08:54 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"