About references

[LN]

Hi All,

Thank you for reading, and in advance, thank you for any opinion related tothis post.

Say I have a workbook with several sheets, one called "Counts" and the others "Day(1) to Day(14)". Inside the "Day()" worksheets I am summarizing information contained in the "Counts" sheet using a SUMIFS() function, which looks like this (for a specific case (Day(14) worksheet):

=SUMIFS(Counts!$H$3:$H$104506,Counts!$G$3:$G$104506,"="&'Day(14)'!$B$94,Counts!$E$3:$E$104506,'Day(14)'!$B121,Counts!$D$3:$D$104506,'Day(14)'!P$94,Counts!$C$3:$C$104506,'Day(14)'!$A$1)

I have a couple of questions about this:

1. The expression shown above corresponds to a cell in the Day(14) worksheet. Some cells in that same worksheet are used in this calculation. Why are these referenced as 'Day(14)'!$A$1, meaning starting with the worksheet name, instead of just $A$1 as the formula is in the same worksheet?

2. I noticed that if the cell in the current worksheet is referenced first in a formula, then, this cell is referenced without the name of the sheet, for example: ($A$1+ Counts!$D$3) as opposed to (Counts!$D$3+'Day(14)'!$A$1), what is the logic here?

3. This referencing situation could be an issue in the moment of creating copies. When creating the copies using the "click right-create copy" capabilities, I understand an independent copy is created and links to the corresponding new sheet are created, meaning that if I have 14 sheets for the Daysand I create a copy like this, then the new one's name would be "Day(15)" and the formulas inside will be updated to:

=SUMIFS(Counts!$H$3:$H$104506,Counts!$G$3:$G$104506,"="&'Day(15)'!$B$94,Counts!$E$3:$E$104506,'Day(15)'!$B121,Counts!$D$3:$D$104506,'Day(15)'!P$94,Counts!$C$3:$C$104506,'Day(15)'!$A$1)

Now, if I copied the entire "Day(14)" worksheet (by select Ctrl+c) and pasted it in a new worksheet, then the references would still be linked to "Day(14)"?

Could anyone expand on the logic/settings of these two ways of copying a worksheet?

Thank you,

 

[GS]

A few things...

Sheetnames:
I assume you're aware that 'Day()' is a built-in Excel function! Any
naming convention that uses the same name as an Excel function/method
or reserve word is just bad practice. I suggest renaming as follows...

Day1, Day2, and so on...

OR

Day_1, Day_2, and so on...

...where formula refs will no longer require the apostrophes, and there
will not be any ambiguities of the name used. Regardless of whether the
syntax of using your existing sheetnames in formulas works, ..it's just
bad practice no matter how you slice it!

Item 1&2:
No sheetname is required to ref cells on the same sheet as the formula.
Sheetnames are required to ref cells on other sheets.

I'll have to play with using this formula syntax before responding to
Item#3 as it's not how I would handle a summary sheet in a project
where adding sheets to be included in existing formulas on that summary
sheet. I'd make sure my summary sheet was deliberately designed to
accomodate growth in its number of ref sheets. Also, all sheets would
be a copy of a 'template' sheet rather than an existing sheet with
data. Also, common ref cells would be given local scope defined names
so cell addresses are eliminated from formulas, and formulas will be
easier to understand as to how they work and/or what they ref!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion