Hide future month's linked cells with value of zero
I have a linked chart built through the end of the fiscal year based on a
spreadsheet supplied by another office. WITHOUT hiding spreadsheet columns or deleting cell content or changing chart coordinates, I would like to instruct the chart not to plot future month's cells with zero values. Is this possible? |
Hide future month's linked cells with value of zero
Change "" in your formulas to NA().
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... I have a linked chart built through the end of the fiscal year based on a spreadsheet supplied by another office. WITHOUT hiding spreadsheet columns or deleting cell content or changing chart coordinates, I would like to instruct the chart not to plot future month's cells with zero values. Is this possible? |
Hide future month's linked cells with value of zero
Jon: Is this in the VBA instructions? I looked there, but could not find it.
DOUG "Jon Peltier" wrote: Change "" in your formulas to NA(). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... I have a linked chart built through the end of the fiscal year based on a spreadsheet supplied by another office. WITHOUT hiding spreadsheet columns or deleting cell content or changing chart coordinates, I would like to instruct the chart not to plot future month's cells with zero values. Is this possible? |
Hide future month's linked cells with value of zero
Jon: I was able to hide the zero plots in the spreadsheets by formatting the
numbers as "0;-0;;@". But, the linked charts still display these datapoints. Please advise... "DOUG ECKERT" wrote: Jon: Is this in the VBA instructions? I looked there, but could not find it. DOUG "Jon Peltier" wrote: Change "" in your formulas to NA(). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... I have a linked chart built through the end of the fiscal year based on a spreadsheet supplied by another office. WITHOUT hiding spreadsheet columns or deleting cell content or changing chart coordinates, I would like to instruct the chart not to plot future month's cells with zero values. Is this possible? |
Hide future month's linked cells with value of zero
This has nothing to do with VBA; these are purely worksheet formulas. In the
linked cells, you must have a formula like =A1 or =IF(ISBLANK(A1),"",A1) Change these both to something like =IF(ISBLANK(A1),NA(),A1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... Jon: Is this in the VBA instructions? I looked there, but could not find it. DOUG "Jon Peltier" wrote: Change "" in your formulas to NA(). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... I have a linked chart built through the end of the fiscal year based on a spreadsheet supplied by another office. WITHOUT hiding spreadsheet columns or deleting cell content or changing chart coordinates, I would like to instruct the chart not to plot future month's cells with zero values. Is this possible? |
Hide future month's linked cells with value of zero
Jon: I do not have a conditional statement in the spreadsheet - I have a
cell reference linked to another workbook. I want to instruct the spreadsheet to accept the link, but not to display it if it is a zero in a future month. Using the number formatting command "0;-0;;@", I can hide the cell content in the spreadsheet, but it still shows up in the graph. I would like to have the graph treat the cell with the hidden content as a blank cell. So, for the first eleven months of the fiscal year, I shall display the linked data, but not the final month (Sep), because it has zeros in it. I want the linear trend line to do the same thing, i.e., to trend the first eleven months and to ignore the twelfth for now. The reason for this is that the underlying data is coming from an outside source and incomplete months contain zeros in their format. I want to use their data, display it in our charts, and not have to instruct someone else how to adjust the cell references each time the data is updated. It should happen automatically, in other words. What do you think - is this possible? "Jon Peltier" wrote: This has nothing to do with VBA; these are purely worksheet formulas. In the linked cells, you must have a formula like =A1 or =IF(ISBLANK(A1),"",A1) Change these both to something like =IF(ISBLANK(A1),NA(),A1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... Jon: Is this in the VBA instructions? I looked there, but could not find it. DOUG "Jon Peltier" wrote: Change "" in your formulas to NA(). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... I have a linked chart built through the end of the fiscal year based on a spreadsheet supplied by another office. WITHOUT hiding spreadsheet columns or deleting cell content or changing chart coordinates, I would like to instruct the chart not to plot future month's cells with zero values. Is this possible? |
Hide future month's linked cells with value of zero
If the cell contains any kind of formula, it can never be treated as a blank
cell. Hiding the value with a number format does not remove the value from the cell. You can either change your link formula as I suggested, or define a dynamic range that knows how many months to plot (http://peltiertech.com/WordPress/200...namic-charts/), or tolerate the zero plotting. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... Jon: I do not have a conditional statement in the spreadsheet - I have a cell reference linked to another workbook. I want to instruct the spreadsheet to accept the link, but not to display it if it is a zero in a future month. Using the number formatting command "0;-0;;@", I can hide the cell content in the spreadsheet, but it still shows up in the graph. I would like to have the graph treat the cell with the hidden content as a blank cell. So, for the first eleven months of the fiscal year, I shall display the linked data, but not the final month (Sep), because it has zeros in it. I want the linear trend line to do the same thing, i.e., to trend the first eleven months and to ignore the twelfth for now. The reason for this is that the underlying data is coming from an outside source and incomplete months contain zeros in their format. I want to use their data, display it in our charts, and not have to instruct someone else how to adjust the cell references each time the data is updated. It should happen automatically, in other words. What do you think - is this possible? "Jon Peltier" wrote: This has nothing to do with VBA; these are purely worksheet formulas. In the linked cells, you must have a formula like =A1 or =IF(ISBLANK(A1),"",A1) Change these both to something like =IF(ISBLANK(A1),NA(),A1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... Jon: Is this in the VBA instructions? I looked there, but could not find it. DOUG "Jon Peltier" wrote: Change "" in your formulas to NA(). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... I have a linked chart built through the end of the fiscal year based on a spreadsheet supplied by another office. WITHOUT hiding spreadsheet columns or deleting cell content or changing chart coordinates, I would like to instruct the chart not to plot future month's cells with zero values. Is this possible? |
Hide future month's linked cells with value of zero
As Jon has already pointed out, replace your cell reference with a
conditional one. =IF(ISBLANK(cell-reference),NA(),cell-reference) On Tue, 5 Aug 2008 10:45:01 -0700, DOUG ECKERT wrote: Jon: I do not have a conditional statement in the spreadsheet - I have a cell reference linked to another workbook. I want to instruct the spreadsheet to accept the link, but not to display it if it is a zero in a future month. Using the number formatting command "0;-0;;@", I can hide the cell content in the spreadsheet, but it still shows up in the graph. I would like to have the graph treat the cell with the hidden content as a blank cell. So, for the first eleven months of the fiscal year, I shall display the linked data, but not the final month (Sep), because it has zeros in it. I want the linear trend line to do the same thing, i.e., to trend the first eleven months and to ignore the twelfth for now. The reason for this is that the underlying data is coming from an outside source and incomplete months contain zeros in their format. I want to use their data, display it in our charts, and not have to instruct someone else how to adjust the cell references each time the data is updated. It should happen automatically, in other words. What do you think - is this possible? "Jon Peltier" wrote: This has nothing to do with VBA; these are purely worksheet formulas. In the linked cells, you must have a formula like =A1 or =IF(ISBLANK(A1),"",A1) Change these both to something like =IF(ISBLANK(A1),NA(),A1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... Jon: Is this in the VBA instructions? I looked there, but could not find it. DOUG "Jon Peltier" wrote: Change "" in your formulas to NA(). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... I have a linked chart built through the end of the fiscal year based on a spreadsheet supplied by another office. WITHOUT hiding spreadsheet columns or deleting cell content or changing chart coordinates, I would like to instruct the chart not to plot future month's cells with zero values. Is this possible? Regards, Tushar Mehta Microsoft MVP Excel 2000-2008 www.tushar-mehta.com Tutorials and add-ins for Excel, PowerPoint, and other products |
Hide future month's linked cells with value of zero
Jon,
Just jumping in here with a follow up question. Your suggestion worked perfectly and the chart no longer shows the zero value. The individual cells however display the characters #N/A. Is there any way to either suppress that, or get it to display something like "upcoming" or "future"? Thanks in advance. "Jon Peltier" wrote: Change "" in your formulas to NA(). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... I have a linked chart built through the end of the fiscal year based on a spreadsheet supplied by another office. WITHOUT hiding spreadsheet columns or deleting cell content or changing chart coordinates, I would like to instruct the chart not to plot future month's cells with zero values. Is this possible? |
Hide future month's linked cells with value of zero
If you have anything in the cell besides NA() or #N/A, you will get an
apparent zero value in the chart. You can hide the #N/A error with conditional formatting: http://contextures.com/xlCondFormat03.html#Errors I don't like hiding anything important, I prefer keeping the range as is. What I often do, though, is keep the chart data range as is for myself, but insert another sheet with the same data, cleaned up and formatted for a nice printed report, or an on-screen table. It just takes a few links, and you didn't have to mess up one data range to try to make it work for two purposes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Lin" wrote in message ... Jon, Just jumping in here with a follow up question. Your suggestion worked perfectly and the chart no longer shows the zero value. The individual cells however display the characters #N/A. Is there any way to either suppress that, or get it to display something like "upcoming" or "future"? Thanks in advance. "Jon Peltier" wrote: Change "" in your formulas to NA(). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DOUG ECKERT" wrote in message ... I have a linked chart built through the end of the fiscal year based on a spreadsheet supplied by another office. WITHOUT hiding spreadsheet columns or deleting cell content or changing chart coordinates, I would like to instruct the chart not to plot future month's cells with zero values. Is this possible? |
All times are GMT +1. The time now is 10:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com