Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
external usenet poster
 
Posts: 111
Default Relative vs Absolute referencing of Workbooks

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


  #2   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6,953
Default Relative vs Absolute referencing of Workbooks

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



  #3   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 111
Default Relative vs Absolute referencing of Workbooks

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





  #4   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6,953
Default Relative vs Absolute referencing of Workbooks

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.

--
Regards,
Tom Ogilvy

"Ronald Dodge" wrote:

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






  #5   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 111
Default Relative vs Absolute referencing of Workbooks

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.

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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Ronald Dodge" wrote:

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








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
Relative vs Absolute referencing of Workbooks Ronald Dodge Excel Worksheet Functions 4 September 26th 06 10:34 PM
Absolute Values in Relative Cells Sarah Excel Worksheet Functions 3 July 30th 06 02:21 AM
changing from relative to absolute cell reference in multiple form JAaron Excel Worksheet Functions 1 May 22nd 06 08:06 AM
how to change absolute path to relative path hwijgerse Excel Worksheet Functions 0 November 25th 05 08:18 AM
Absolute vs Relative path K2H Excel Discussion (Misc queries) 2 July 28th 05 12:50 PM


All times are GMT +1. The time now is 06:34 AM.

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"