Relative vs Absolute referencing of Workbooks

[Ronald Dodge]

I normally do not post across groups, but this one impacts each of the 3
groups that this is posted to.

There are many times when I would formulas and charts to refer to only the
workbook that it resides in, not the workbook that it was originally created
in. That is cause I have several workbooks with the same structure, but the
data is specific to the area it covers, which in this case, it's machine
center by machine center. With 15 such files, when there's some sort of a
change, this makes it very long and tedious work, which I don't particularly
care to do.



Formula writing:

If formula refers to only cells within the same worksheet, when you do a
copy and then paste in a different workbook in the same location structure
wise, this is no problem as it keeps the same exact formula.

If formula refers to cells within other worksheets of the workbook it
resides in, when you copy and then paste in the same place structure wise,
but only in a different workbook, the formula refers to the workbook that it
was copied from, not the workbook that it was pasted to. This is a problem
as I don't like to have to do. To get around this issue, I have had to wrap
all such references within an INDIRECT function and double quotes.

Example:

Before: Sheet1!$A:$A

In the above case, it would cause the pasting into a different workbook to
refer to the workbook that it was copied from. In the below case, it would
keep the same exact formula, which then forces it to refer to the workbook
that the formula resides in.

After: INDIRECT("Sheet1!A:A")

Note, in this case, you could include the "$", but it's not needed when
using the INDIRECT function. If your formula is long enough as some of mine
tends to be, you can actually run into the issue of the formula being too
long (too many characters within the cell). That is also another reason why
I had to use the INDIRECT function to get around this issue as the formula
would otherwise include the full path to the workbook, when then would
truncate the formula to the maximum number of characters allow and even
through in some unreadable characters into the formula.

Charts:

This problem is much more prevalent in charting than it is in formula
writing

First, with charts, you can not use the above INDIRECT function method trick
to get around the issue as Excel won't allow it to take place within the
charts objects.

There are charts that you either don't want to plot empty cells, so you
setup formulas to return the NA() function to have the charts plot properly
for what you are wanting the charts to do. Reason for this, if you have
such cells plotted as 0, then the chart looks all off and wrong in many
cases. If you have the formula return empty strings (""), then the charts
plots them as 0's, which is not desireable, thus why for the #NA! error
value return with the formula. In addition to this, you also setup the
Conditional Formatting so as for those cells that do return the #NA! error
message, the font color is the same as the Interior Background color of the
cell to effectively hide that value when it's printed.

Even then, there's still one more trick that I have had up my sleeve to try
since I use a central file to run all of my production reports. That is to
use VBA to change the FormulaSeries property on each of the series of each
of the charts, which I named all of the relavent charts to a meaningful name
while still keeping the name relatively short (10 to 11 characters in all).
Well this trick started to work, but ran into an issue that is still
considered as a problem by Microsoft in Article 139327. The article is only
listed under XL97, but also exists in XL2002 as that's the version I'm
using.

The problem comes into play when none of the values within the series are
plotted rather it be via the fact it not plotting empty cells as 0's or the
values of the referecned range contains the "#NA!" error message (In the
watch window, it would show the value of the cell as "Error 2042" and within
the series collection object, there would be no value object within the
collection for that series).

Given the fact there is no Value Object within the Series Object, it's
rather obvious as to why one can't set anything on the value's object, but
this is the first time that I have seen one not be able to programmatically
set properties that resides on a Collection Object itself due to the fact
there is not one single individual object within that collection object. A
rather large number of properties on the Series Collection Object is like
this. The only thing that I have been able to come up with to get around
this sort of issue is to use a code something like the following after
copying and pasting the objects and worksheet range from one workbook to the
various other workbooks with the same structure:



Dim wshChart as Worksheet, strSerFormula

Set wshChart = Workbooks(strShortFileName).Worksheets("Charts")
strSerFormula = wshChart.Range("C57").Formula
wshChart.Range("C57").Value = 0
wshChart.ChartObjects("chtSetupLine").Chart.DisplayBlankAs = xlZero
wshChart.ChartObjects("chtSetupLine").Chart.SeriesCollection(1).Formula =
"=SERIES(Charts!$B$57,,Charts!$C$57:$J$57,1)"
wshChart.ChartObjects("chtSetupLine").Chart.DisplayBlankAs = xlNotPlotted
wshChart.Range("C57").Formula = strSerFormula



It would be so much easier if there was a much better way of copying and
pasting charts and formulas with such references and have the option to
refer to either the workbook that it originally came from (Absolute
referencing) or to refer to the workbook that it's pasted into (Relative
referencing).

 

[Tom Ogilvy]

is this a suggestion for Microsoft?

If so, you should log into the communities and post it as a "suggestion for
microsoft" (- a choice under the New button) in the appropriate newsgroup -
programming not being one of them. Also, one suggestion per post. Posting
it as if it were a question rather than a lecture accomplishes nothing.


http://www.microsoft.com/communities/newsgroups/default.mspx

 

[Ronald Dodge]

Well pardon me if I don't meet your requirements. I am using Outlook
Express to get to the newsgroups which doesn't have a such button, nor did I
know such button existed on their site. From my understanding, MS typically
don't look at newsgroups, thus shouldn't even be used for suggestions for
them to improve their products. However, I did it as others has asked this
very question and while this is a rather long winded, technical and tedious,
it does point out some different things that can be done as well as the
limitations that I have ran into. On the other hand, for suggestions to MS,
of course, by this same ruling, one would have to go to their website and go
through their suggestion link.

This not only pertains to Charts and Formulas, but also to the
SeriesCollection Object, which is in Programming as pointed out in Article
139327.


For the one suggestion per post, well maybe you see it as 3 different ones,
which I can certainly see that with regards to the SeriesCollection one, but
the other 2 are most definitely interconnected. The chances are pretty high
that when the one is resolved, the other will be resolved too, that is if we
have an option rather to have absolute or relative workbook referencing.

 

[Tom Ogilvy]

You ended with

It would be so much easier if there was a much better way of copying and

pasting charts and formulas with such references and have the option to
refer to either the workbook that it originally came from (Absolute
referencing) or to refer to the workbook that it's pasted into (Relative
referencing).

That sounded like you wanted to make a suggestion to microsoft to add some
functionality. If you were just sharing your wisdom, then pardon my
intrusion - but then what was the point of the last part? Assuming it was a
suggestion, I was trying to help you out. Otherwise it just lays in the sun
like a turd on the sidewalk, with everyone stepping around, and those that
venture into it exclaiming "What's this . . . ??!!" If you can appreciate the
simile. <g>

the one suggestion per post would be appropriate if you were making a
suggestion as I suggested.

nor did I
know such button existed on their site.

Now you know - why I posted.

Well pardon me if I don't meet your requirements.

Not mine. I was trying to help you get your message out since you obviously
invested a lot of time on it.

is in Programming as pointed out in Article

139327.

If it is in an Article in the Microsoft Knowledge Base, then I would think
Microsoft already knows about it. I find it isn't uncommon for them not to
document the same error in every version although sometimes they do.

 

[Ronald Dodge]

Well as I stated before, even though you may have took it as a suggestion
for Microsoft, based on the ruling that I have known about newsgroups and
the fact that it's not guaranteed that MS people would look at the
newsgroups, it's not the place to make the suggestion, but yet, I know
others has had that same question, thus a brief reiteration. Yes, this
would be a suggestion to them, but to my knowledge, this is not the place
for it. As a matter of fact, it was my understanding that MS people rarely
ever look at these newsgroups other than very minor few specific ones such
as the Windows/Microsoft Update one.

When you first replied, it sounded like you assumed that I went through the
website. Not everyone goes through the website as there are some of us who
use programs which includes NNTP such as Outlook Express to get to the
newsgroup as it's quicker to get to such newsgroups rather than jumping
through hoops to get to the newsgroup via the website.