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.
--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000
"Tom Ogilvy" wrote in message
...
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...s/default.mspx
--
Regards,
Tom Ogilvy
"Ronald Dodge" wrote:
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