Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Absolute worksheet reference number Results

When one references a cell on a different sheet one uses, for instance,
Sheet1!A1 to get at the value of that cell.
If you change the name of the worksheet from Sheet1 then the reference is
lost.
Is there no way of still refering to the 'logical' sheet name (e.g. Sheet1)
after the sheet has been renamed?
My problem is that I am trying to generate product statistics on a
"compoite" worksheet and the other worksheets contain certain parameters on
each batch . The worksheet tab name is also changed to the batch number and
the numbers are not contiguous (i.e. do not follow a standard order like A,
B, C etc rather more like A, D, E, H).
Yes, I could paste the values onto the composite sheet but this is time
consuming (the values I want are also not in contiguous rows and/or columns)
but if I could rename the sheets but still refer to them as Sheet1, Sheet2 or
some other underlining nomenclature it would be a great boon. I assume that
there must be an absolute reference to a sheet even though the name has been
changed?
On the "composite" sheet I could just then copy the previous data and then
change the sheet reference from say Sheet10 to Sheet 11 or whatever the
absolute sheet reference is.

Following on from this (also as an alternate method), if I had a cell (say
"A1") on the composite sheet that reflected the actual sheet name (such as
"Data1" say) that I wanted to access and I wanted to get the data from cell
"Z1" how do I construct a cell reference from these two parameters (in other
words cell reference "Data1!Z1")?
Logically one should be able to do something like 'A1' + '!' + 'Z1' to get
'Data1!Z1' but Excel will not accept this type of construct.

Sorry for the verbosity but hope that readers will understand what I'm
trying to do!

Thanks in advance to any respondents.

When one references a cell on a different sheet one uses, for instance,
Sheet1!A1 to get at the value of that cell.
If you change the name of the worksheet from Sheet1 then the reference is
lost.
Is there no way of still refering to the 'logical' sheet name (e.g. Sheet1)
after the sheet has been renamed?
My problem is that I am trying to generate product statistics on a
"compoite" worksheet and the other worksheets contain certain parameters on
each batch . The worksheet tab name is also changed to the batch number and
the numbers are not contiguous (i.e. do not follow a standard order like A,
B, C etc rather more like A, D, E, H).
Yes, I could paste the values onto the composite sheet but this is time
consuming (the values I want are also not in contiguous rows and/or columns)
but if I could rename the sheets but still refer to them as Sheet1, Sheet2 or
some other underlining nomenclature it would be a great boon. I assume that
there must be an absolute reference to a sheet even though the name has been
changed?
On the "composite" sheet I could just then copy the previous data and then
change the sheet reference from say Sheet10 to Sheet 11 or whatever the
absolute sheet reference is.

Following on from this (also as an alternate method), if I had a cell (say
"A1") on the composite sheet that reflected the actual sheet name (such as
"Data1" say) that I wanted to access and I wanted to get the data from cell
"Z1" how do I construct a cell reference from these two parameters (in other
words cell reference "Data1!Z1")?
Logically one should be able to do something like 'A1' + '!' + 'Z1' to get
'Data1!Z1' but Excel will not accept this type of construct.

Sorry for the verbosity but hope that readers will understand what I'm
trying to do!

Thanks in advance to any respondents.

Hi,

I have an excel report containing eight or so worksheets. A user can make a number of selections with the report to change its context. E.g. to analyse different types of customers; high spend or low spend etc. Sales projections are then presented with several Excel Charts adapting to the user's preferences.

So far, so good. Although various sheets reference each other, the user can't change data in another worksheet by making a selection in their current sheet. They can only change display options of tables and graphs.

My problem occurs when the user makes a selection (they choose a ComboBox option.) This then triggers my code, which creates the data tables and graphs depending on their selection. The report is quite long so I have created two points in the same worksheet where the user can change the same customer selection option. (Basically, all I have done is create a second ComboBox which triggers the first). Again, this works perfectly causing no errors (other than the display error below…)

However, when I change the second instance of the ComboBox a phantom copy of my chart is dumped on the current screen view. If I scroll off screen and then back again the phantom chart disappears.

I need a quick solution to fix this bug until I have some more time to look at what's causing it. Therefore I simply want to add a line of code, which will scroll off screen and then return back to the same position. (Not very neat, but at least it will get rid of my duplicate chart). As I have screen updating turned off the user won't notice what is happening.

Now, my problem is this. In order to move off screen I need to give a reference point. But I can't give an absolute cell reference as I have two ComboBoxs. If I give an absolute reference, half of the time the user will be returned to the wrong point in the worksheet.

I also can't use activecell.offset as I don't have a cell selected. I have a chart selected. Is there a way of selecting a cell on screen?

I’m a little lost on this one, what can I do?

My code for the ComboBox is:


	VB:
	
 ComboBox2_Change() 
    Application.ScreenUpdating = False 
    Sheets("Pivot Tables").PivotTables("PivotTable3").RefreshTable 
    GroupList 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


On WorksheetA I have a long list of names that are currently 1 row apart (i.e. Name1 is $A$1, Name2 is $A$2, etc.) in the same row as the name there are a number of columns of values such that a row looks like this:
Name1 23 36 1 15.2
Name2 24 34 0 17.8
etc.

I want a table on another worksheet to refer to each of these absolute cell references - is there an easy way to do this without having to type in every absolute reference in every cell in the new table on worksheet 2? Basically I want to increment the reference by one row going down or by one cell going across rather than having to type in each cell indivually?

I know I can cut and paste special the values or the formulas into the 2nd worksheet but I want to refer to the actual cell in every case

I thought after doing two rows I could pick up the corner of the selected box and pull it down the required number of rows but that doesn't work it merely replicated the last row.

Not sure I have made myself clear

Thanks for reading this anyway -
Ashbo

Split from http://www.ozgrid.com/forum/showthread.php?t=102469

Originally Posted by AAE Welcome to Ozgrid.

Assume the following:
Cell A1: is the input cell
Cell B1: contains a number
Cell C1: formula = A1*B1 or $A$1*$B$1

Your formulas must contains absolute cell references, other wise Excel will attempt to update them to the new cell references when copying/pasting.
If you "cut", rather than copy, Excel will maintain the original cell references in the formula.

Both of these questions are well within the experience level of a user with above average skill level as your profile shows. Please lower it (before one of the Admins does it for you) to a more realistic level.

See this training page on how to toggle between relative/absolute cell references: http://www.ozgrid.com/Excel/free-tra...n-17-basic.htm Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.

Lets use my calculator for instance. Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.

Thanks in advance.

PS: And I do consider myself above average for the sole reason that I know far more then every other person I have ever met in real life. And from that fact I can, in my opinion, safely assume that I know more then most people, hence I am above average. Not to say that I know everything or even a great bulk of everything or even 1/10 of everything, just more then the average person.

IN Excel 97
Within the same workbook, I'm wanting to create a new worksheet, but I want
it to have the "exact" same values as an existing worksheet. An exact
duplicate, however, copying it won't help, because I want it to pick up any
changes made to the original worksheet. I want to simply change the rounding
for charting purposes, but need it to have same values, so all the cells need
to link to the original sheet.

Here's a sample of what I've run into.
When I tried to use the $ for absolute value (refering to the original
sheet), it doesn't pick up that the number is a calculation "and" is
converted to a percentage.
I'm miffed by it all.
Perhaps there's a way to copy, but tell it to link to the original sheet, at
the same time (I'm guessing).

Thanks for your help.

I'm sort of stumped with a problem I'm having with how to reference between
several Excel worksheets.

I have a folder that, at its base, has a worksheet, there are several
folders at this level that have Excel worksheets in them as well going in two
levels. All of these documents refer to the worksheet at the root of the
file however, I'm trying to zip these up and send them to a client so they
can play with the numbers in the root file.

However, all of the worksheets refer to the root worksheet absolutely, so,
if I were to zip it up and send it, it wouldn't work. How can I refer to a
worksheet relatively in Excel or how would it be best to transport these
sorts of files?

Thanks.

I want to create a form for purchase requisitions that is a spreadsheet
(using Excel 97) with 4 work sheets.

The first sheet is where the data will be entered, one row per item to be
purchased.
The other sheets will be fully formatted for printing/faxing and will
include company logo, delivery information, order numbers etc.

What I want to do is for the formatted sheets to reference various ranges of
the data entered on the first sheet.
i.e. the first formatted sheet references the data in worksheet1 cells A1:C20.
The second formatted sheet references the data in worksheet 1 cells A21:C40
and similarly for the 3rd formatted sheet, cells A41:C60.

I'd like the users to be able to cut/copy/paste cells around on the data
sheet (worksheet1), or even insert new rows, and for these changes to be
reflected in the formatted sheets
i.e. if a user moves an item 3 rows down on the data sheet by
cutting/pasting the cells, then it will also appear 3 rows lower down on the
relevant formatted sheet. If the change made the row change from row 19 to
row 22, then this item would now appear on the second formatted sheet,
instead of the first.

Neither relative nor absolute cell references seem to achieve this.
e.g. if a new row is inserted on the data sheet, these new cells were never
originally refernced on any of the formatted sheets and so do not appear
there now.
Is there another function, or some other technique for achieving this?

TIA
John

IN Excel 97
Within the same workbook, I'm wanting to create a new worksheet, but I want
it to have the "exact" same values as an existing worksheet. An exact
duplicate, however, copying it won't help, because I want it to pick up any
changes made to the original worksheet. I want to simply change the rounding
for charting purposes, but need it to have same values, so all the cells need
to link to the original sheet.

Here's a sample of what I've run into.
When I tried to use the $ for absolute value (refering to the original
sheet), it doesn't pick up that the number is a calculation "and" is
converted to a percentage.
I'm miffed by it all.
Perhaps there's a way to copy, but tell it to link to the original sheet, at
the same time (I'm guessing).

Thanks for your help.

I have built an Excel 2002 add-in that contains a number of UDFs that
users can call from cells in a worksheet.

When I distributed the add-in, I had it installed in the
%APPDATA%/Microsoft/Add-ins folder, where %APPDATA% is the environment
variable for C:Documents and Settingsusr00000Application Data

The problem is that if a user builds a spreadsheet and then sends it to
another user, the worksheet contains references to the path on the
originator's machine (not using %APPDATA% environment variable.)

How do I fix this? Is there a better place to install add-ins so that
excel doesn't add these absolute path references?

I have 364 worksheets in one workbook-I am trying to get each sheet to look at the previous sheet and pull a number. I select sheet 2 thru 364 and then enter ='jan-01'!ac5---all sheets pull from the first rather than the one in front of them. excel defaut goes from relative to absolute when you refer to another worksheet. how do i change this?

Have a simple idea that is giving me fits.

Have a worksheet with numberic data and labels in rows and columns. Below
each column I use a simple SUM() function which works fine, until I disturb
a row of data. By disturb, what I mean is that if I move the top row of
data down to the bottom of the array, highlight the entire data area, move
that area up one row, I lose the correct references in the SUM() function.
I'm just moving the data area, not the row containing my SUM() functions -
that stays put. IOW, the SUM() function is in row 12, the data area extends
from row 1 through row 10, I move row 1 to row 11 and then move everything
from row 2 through row 11, back up one row.

I've tried using relative and absolute addressing but that SUM() formula
somehow keeps adjusting based on what it sees me moving and giving me an
incorrect result. How do I make the formula "static", or to just tell it
"sum the numbers above and don't watch what's moving around, dog-gone it!".

Thanks for any advice here.

I'm using formula "=VLOOKUP($A$6,QAvg1Q!$A$6:G17,7,FALSE)",However, since I use a number of worksheets, QAvg1Q, QAvg2Q, QAvg3Q,... I would like to not specify the absolute worksheet name, but rather derive it from cell content, for example, with B4="QAvg1Q", B5="QAvg2Q", B6="QAvg3Q", ...
I thought I could do something like: "=VLOOKUP($A$6,(B4)!$A$6:G17,7,FALSE)" but this doesn't work (silly me)...
Can a kind person please advise?

Hi,

New to this so here goes. An admitted newbie but prepared to 'do the yards'.

Have a workbook with a number of worksheets.

One of the worksheets is titled 'TEMPLATE' - this is a calculation sheet that is used to determine pricing - on a quarterly basis. A number of cells in this worksheet reference cells in another worksheet titled 'CURRENT RATE' in the same workbook.

Every quarter, we are provided a new 'CURRENT RATE' worksheet.

At the moment, we have to manually rename the existing worksheet 'CURRENT RATE' to a title referencing the quarter that it was applied to ie Rates DEC2011-FEB2012. Then insert the new version worksheet , rename it 'CURRENT RATE'.

The problem is that the cells in the 'TEMPLATE' worksheet that reference the original 'CURRENT RATE' worksheet changes to the new name of the original worksheet. This results in me having to track through the 'TEMPLATE' worksheet to re-reference the affected cells back to 'CURRENT RATE' once I have inserted the new quarterly worksheet (and renamed it to 'CURRENT RATE').

I've looked at absolute values but am getting an alert. I have tried a number of methods ie =ABS('CURRENT RATES'!$D$3), =$'CURRENT RATES'!$D$3 & ='CURRENT RATES'!$D$3

Alert states 'a user has restricted values that can be entered in this cell'

I have written a Macro to insert formulas into a number of cells.
Using the same Macro I then copy the rows containing these cells to a new worksheet.
How can I make the references (of the new formulas) in the cells absolute so that when copied I get the same results?

Macro as written:

	VB:
	
 CheckBox1_Click() 
    Range("ak11").Select 
    If (ActiveCell.Value) = True Then 
        Sheets("Standard Pricing Models").Range("d11").FormulaR1C1 = "='Process Comp''t Selection'!R[-7]C[33]" 
        Sheets("Standard Pricing Models").Range("B11").FormulaR1C1 = "='Process Comp''t Selection'!R[0]C[1]" 
        Sheets("Standard Pricing Models").Range("c11").FormulaR1C1 = "='Process Comp''t Selection'!R[0]C[-1]" 
        Sheets("Standard Pricing Models").Range("B13").FormulaR1C1 = "='Process Comp''t Selection'!R[-2]C[5]" 
        Sheets("Standard Pricing Models").Range("c13").FormulaR1C1 = "='Process Comp''t Selection'!R[-2]C[3]" 
        Sheets("Standard Pricing Models").Range("B15").FormulaR1C1 = "='Process Comp''t Selection'!R[-4]C[9]" 
        Sheets("Standard Pricing Models").Range("c15").FormulaR1C1 = "='Process Comp''t Selection'!R[-4]C[7]" 
        Sheets("Standard Pricing Models").Select 
        Range("ModelTitle").Select 
        Selection.Copy 
        Sheets("Civils Pricing").Select 
        Range("A1").Select 
        Selection.End(xlDown).Select 
        ActiveCell.Offset(1, 0).Select 
        Selection.Insert Shift:=xlDown 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks in anticipation.

Hi All,

I have a problem that can be solved in one of two ways, neither of which I seem to be able to figure out.

I have an Worksheet_Change event that affects mycell.offset(0,1) when mycell is changed by putting a worksheet formula in it.

The formula is this:
="sumif(Master!G:G," & mycell.offset(0,-11).address & ",Master!J:J)"

The problem is that .address creates an absolute reference so that if the sheet is later sorted all the numbers are screwed up.

I tried creating the same formula except using a .value instead of a .address but I can't get the quoting right.

So I need a either a method to properly quote this or a method to get a relative address.

I'm sure this must be simple, but I haven't encountered any rules for VBA quoting despite searching.

Thanks for your help.

Cheers,

Jen

I am looking for a macro that will combine the data on two worksheets into one. The data on both worksheets are structured the same and the data does not need to be changed in anyway. Just need to append the data from one worksheet to the other worksheet either at the beginning or at the end or the other.

The number of rows that are on the worksheets will change so I cannot use an absolute reference to a set number of rows which is the problem I ran into when I used the macro recorder.

Good morning,

I am linking a row of data in worksheet x to a column of data in worksheet Y and would like to be able to drag down the cell reference in worksheet Y in this way:

=MIN(RuleofOriginData!AR$4:AR$63)
=MIN(RuleofOriginData!AS$4:AS$63)
=MIN(RuleofOriginData!AT$4:AT$63)

However, when I drag it down, the formula is automatically absolute for both the row and the column number instead of just the row as I indicated. Can anyone tell me why this occurs and whether there is a way to fix it?

Thank you

Dear All:

First, allow me to express my deepest thanks for all of the help y'all have provided me over the years while working and figuring out my absolutely FAVORITE application - Microsoft Excel!! Hopefully I can add another notch on the "solved" column after this one..

Following this preface, you will find my original post [which was] under the subject "lookup stock symbol on worksheet and return summary data"

Basically, I may have asked my question the wrong way. What we are trying to do basically is create a form in excel or at least some type of function/macro [in Excel] that can query the other existing worksheet FULL of data. All I really need to know right now is how to return this data using whatever means necessary. I'd REALLY prefer to just use a text entry box like he and I discussed and enter for example "DELL" or "IBM" or "MSFT" as the ticker symbol, and have some fields on my main worksheet populated with numbers/data from the source worksheet. Of course I will want to make calculations on this data - I think I should be able to figure out how to incorporate them into the code after someone can enlighten me as to how to initially retrieve said data from the source worksheet.

Take a look at the correspondence below, or just see the original thread for reference. Biff was helping, but I think it may have just been a "right church..wrong pew" kindof thing. I have worked with pivotables, and I don't think that is the avenue I want to take. And like I said before, this is obviously (DEFINITELY) a solution better provided by a database, but unfortunately the ability to create or have someone create one at this point in time is not an option.

And hell, they have the "Control Toolbox" and "Forms Toolbox" options in Excel, so I figure they must be there for a reason.

If someone could please point me in the right direction, I'd be much obliged.

Thank you very much for your time and efforts in this matter.

Best Regards:

Greg Purnell

Jgpurnell13 - at - Verizon.net

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

HERE IS MY INITIAL POST:

>>>>>>>>>>>>>My friend tried to stump me, but he didn't say I couldn't use you guys to find an answer (not to mention if I do find a solution I can bill his firm)...so here it is:

He has this summary data on one worksheet (but with MUCH more data columns/rows):

Symbol 9/14/2006 9/15/2006
ACG -2.22% -2.09%
ADF -9.79% -10.39%
ADRA -0.51% 0.03%
ADRD -0.41% 0.04%
ADRE -0.63% -0.03%
ADRU -0.56% 0.07%
ADX -14.39% -14.35%
AFB 7.38% 7.58%

and this is on the other worksheet:

ACG Enter symbol here

-2.24% Returns the average

Obviously, the formula he has in the lower box (where result populated is -2.24%) is: =AVERAGE(Data!3:3) (keep in mind I hid 100's of columns so avg is incorrect)

Very basic, I know. So I called him after he sent the email to find out why he's making a mountain out of a molehill. Obviously he could
summarize the data on this worksheet simply by calculating the averages on the source worksheet and just using the =Cell() formula to
return the results in a more readable format.

He does, however, (and as I suspected) want to bring in a lot more than just averages (SDs, variances, etc., etc.). Basically, he wants
to type in a symbol and have it return a bunch of data. He has it figured out one way with an array formula, but with over 1000 symbols,
and market data dating back to early 2006, it is obvious he has way too much data. He did, of course, tell me that the array formulae did
take forever, so on to plan B.

I have some ideas, but I have other stuff I need to be working on, so I just figured I'd come to the place where I've always been helped
with Excel (immensly!).

Basically, he just wants to be able to type in a symbol (a market ticker symbol, but not for regular stocks, they deal with closed-end
funds) - and have it return some summary data. As I said before, he's starting with "averages" but wants to apply the formula that I (well,
"we") come up with in order to return other types of data or variations thereof.

I did tell him right off the bat that this is an obvious and definite application for Microsoft Access or SQL Server (or any DB app.), to
which he agreed. But his company is just a 2 man operation, and he doesn't have the time to learn how to develop and implement one (a
db), nor the funds to have somebody else do so.

Also, as a side note - if you have any comments, every time I bring up how he should be doing this stuff on a database, he brings up "Filemaker Pro" - does
anyone professionally use that app.? I've never heard of anyone using it in the tech circles I am in. Is it a "Mom & Pop" type business app, or just a personal db for someone to arrange their CDs/DVDs, or do businesses actually use the software?

Thank you very much in advance all. You guys and gals have been absolutely WONDERFUL with helping me with Excel over the years!! I
really appreciate it.

Best Regards:

Greg Purnell
jgpurnell13 - at - verizon.net

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

BIFF's INITIAL REPLY:

>>>>>>>>>>>>Hmmm.....

Based on your sample data the average for ACG is -2.16%.

If each symbol is listed only once there is no need to use array
formulas.

With your sample data in the range A2:C9

G2 = ACG

This formula will get the average:

=AVERAGE(INDEX(B2:C9,MATCH(G2,A2:A9,0),))

You can use the same basic syntax for other calculations.

=STDEV(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MIN(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MAX(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
etc

Biff

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MY REPLY:

>>>>>>>>>>>>>>>>>>>>Thanks for the reply Biff. The only thing is that he wants to be able to
type the symbol into a cell and hit "enter" or click a button or something
and have all of this data returned.

Also, I shortened the sample data by several columns, and just put 2 columns
for examples' sake.

Any other ideas?

Thanks,

Greg

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Well crikey! "He" has to do some work!

Maybe a pivot table but I really can't help you with that 'cause I hate pivot tables!

Biff

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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


No luck finding an answer? You could always try Google.