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:

Befo 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 structu



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.Displa yBlankAs = xlZero
wshChart.ChartObjects("chtSetupLine").Chart.Series Collection(1).Formula =
"=SERIES(Charts!$B$57,,Charts!$C$57:$J$57,1)"
wshChart.ChartObjects("chtSetupLine").Chart.Displa yBlankAs = 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).

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000