Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting,microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative vs Absolute referencing of Workbooks | Excel Worksheet Functions | |||
Relative vs Absolute referencing of Workbooks | Charts and Charting in Excel | |||
Absolute/Relative Referencing & Buttons! | Excel Programming | |||
Chg Absolute to Relative | Excel Programming | |||
missing macro absolute/relative referencing toolbar | Excel Programming |