Hello! My name is Dan.
Let me give you some background first before I dive into the problem. I am using Excel 2013 for Windows. I have built a spread sheet that I use to import weather data from an XML source on the internet (WeatherUnderground) using an API. This spreadsheet also organizes the imported information into an XML table, and then I use another worksheet to parse and format the weather data for exporting to Power Point. I have used VBA to force the workbook to refresh every 13 minutes and to save the file at the same time. The reason I used VBA to do this is because for some reason Excel's Data Refresh feature does not work to refresh XML data from the web.
So within the same workbook I have two worksheets that have the XML table into which Excel imports the XML weather data from the web for two different cities (I use one worksheet per city, but the XML tables are identical on each sheet, so, for example, if I go to cell A5 in the worksheet for one city, the exact same cell in the worksheet for the other city contains the same exact type of weather data for that other city). I have a third worksheet that consolidates all the data fields that are important to me and each cell reference for that data. For example, in this third worksheet I have a column that lists weather features like temperature, humidity, and wind direction. The next column is for the first city and references the XML table where the weather information is stored for that particularly weather feature, and the third column references the worksheet and cells for the other city. I use this worksheet to consolidate the weather data and to view it at a glance.
I reference the consolidate information on the third worksheet in a fourth worksheet that I use to format the information into a format that fits my powerpoint presentation. The powerpoint presentation and the excel spreadsheet are open simultaneously and the powerpoint presentation imports the data from this fourth worksheet and automatically updates it periodically while the presentation is looping. (As a side note, the presentation is running continuously as it is connected to a TV that is used as a digital sign that we update by updating this power point presentation.)
That was a complicated background, but hopefully it was clear. If you need further clarification, please let me know! I will be happy to provide it.
Now for my problem...
The cells in the third worksheet that parses and consolidates the pertinent weather information that reference the XML table are absolute references. For example, the first column lists temperature, the second column has this formula: ='Atl WX'!$A$5. The third column has this formula: ='Chatt WX'!$A$5. Note that cell A5 in both of the worksheets reference the cell in the XML table that holds current temperature for each city that was most recently imported from the web by the workbook. Using absolute references like this shouldn't be necessary, but I did this because my cell references kept changing.
And that is my problem... At random times, some, not all, of the cells that reference the XML table change even though they are fixed as absolute references.
As a side note, I realize that a cell with an absolute reference will not change UNLESS the cell referenced is moved by the addition or subtraction of a row or column. That said, the worksheets with the XML tables are NOT changing. There are never any addition or subtraction of rows or columns. For example, even if the cell that is changing now references another cell in the XML table, that new cell doesn't have the same information as the previous reference. So let's say the original cell referenced is $A$5 and then it gets changed somehow to $A$31. The correct information is still in $A$5 in the XML table, but the formula in the cell that is changing actually shows $A$31, and, of course, it references cell A31 in the XML table, which does not contain any information.
This is what is so odd. I would not expect the cells with formulas with absolute references to change. But they are. On top of that, they seem to change in clusters. For example, I have a list of cells that are referenced for the three day forecast. So the first cluster is forecasted temperature for days one, two and three. The second cluster is for precipitation for days one, two and three. The third cluster is some other weather data for days one, two and three. This is the same for a cluster of hourly forecasts: the first cluster is for temperature for hours one, two and three. Second cluster is precipitation for hours one, two and three. And so on.
When I experience the problem it affects difference clusters randomly, but affects the entire set of references. So most recently this problem effected all of the cells that reference the hourly forecast. So if hours one, two, and three reference cells $X$1, $X$2, and $X$3, when this problem occurs, the whole cluster gets shifted and the cell formulas now show the references as $X$50, $X$51, and $X$52.
To further mystify things, it randomly affects the spreadsheets. The first time it happen, it only happened to one cluster referencing the "Chatt Wx" worksheet. The second time it happened it affected two clusters referencing the "Atl Wx" spreadsheet. This last time it only affected one cluster referencing the "Atl WX" worksheet. So it doesn't happen uniformly, and when it affects one cluster, the other clusters appear unaffected.
I think I could solve this by making these cells use the "indirect" formula, but I haven't tried it yet, and I don't know for sure that it will work given the already strange behavior. As well, given that this workbook is already very complex and many the cells already contain some complex formulas, I'd prefer to avoid using the "indirect" formula in order to avoid complicating things further. While I'm not an Excel wizard, I get by. Still, the people I work with are definitely not as versed in Excel as I am, and if they ever had to work on this sheet, it would not be easy to walk them through it.
With that said, has anyone experienced this before? Does anyone have the foggiest idea what could be going on here or what is causing this? And most importantly, does anyone know how I can fix this?
Thank you for your patience in reading through this post and for your help! And please let me know if I need to clarify anything.
Kindly,
Dan
Let me give you some background first before I dive into the problem. I am using Excel 2013 for Windows. I have built a spread sheet that I use to import weather data from an XML source on the internet (WeatherUnderground) using an API. This spreadsheet also organizes the imported information into an XML table, and then I use another worksheet to parse and format the weather data for exporting to Power Point. I have used VBA to force the workbook to refresh every 13 minutes and to save the file at the same time. The reason I used VBA to do this is because for some reason Excel's Data Refresh feature does not work to refresh XML data from the web.
So within the same workbook I have two worksheets that have the XML table into which Excel imports the XML weather data from the web for two different cities (I use one worksheet per city, but the XML tables are identical on each sheet, so, for example, if I go to cell A5 in the worksheet for one city, the exact same cell in the worksheet for the other city contains the same exact type of weather data for that other city). I have a third worksheet that consolidates all the data fields that are important to me and each cell reference for that data. For example, in this third worksheet I have a column that lists weather features like temperature, humidity, and wind direction. The next column is for the first city and references the XML table where the weather information is stored for that particularly weather feature, and the third column references the worksheet and cells for the other city. I use this worksheet to consolidate the weather data and to view it at a glance.
I reference the consolidate information on the third worksheet in a fourth worksheet that I use to format the information into a format that fits my powerpoint presentation. The powerpoint presentation and the excel spreadsheet are open simultaneously and the powerpoint presentation imports the data from this fourth worksheet and automatically updates it periodically while the presentation is looping. (As a side note, the presentation is running continuously as it is connected to a TV that is used as a digital sign that we update by updating this power point presentation.)
That was a complicated background, but hopefully it was clear. If you need further clarification, please let me know! I will be happy to provide it.
Now for my problem...
The cells in the third worksheet that parses and consolidates the pertinent weather information that reference the XML table are absolute references. For example, the first column lists temperature, the second column has this formula: ='Atl WX'!$A$5. The third column has this formula: ='Chatt WX'!$A$5. Note that cell A5 in both of the worksheets reference the cell in the XML table that holds current temperature for each city that was most recently imported from the web by the workbook. Using absolute references like this shouldn't be necessary, but I did this because my cell references kept changing.
And that is my problem... At random times, some, not all, of the cells that reference the XML table change even though they are fixed as absolute references.
As a side note, I realize that a cell with an absolute reference will not change UNLESS the cell referenced is moved by the addition or subtraction of a row or column. That said, the worksheets with the XML tables are NOT changing. There are never any addition or subtraction of rows or columns. For example, even if the cell that is changing now references another cell in the XML table, that new cell doesn't have the same information as the previous reference. So let's say the original cell referenced is $A$5 and then it gets changed somehow to $A$31. The correct information is still in $A$5 in the XML table, but the formula in the cell that is changing actually shows $A$31, and, of course, it references cell A31 in the XML table, which does not contain any information.
This is what is so odd. I would not expect the cells with formulas with absolute references to change. But they are. On top of that, they seem to change in clusters. For example, I have a list of cells that are referenced for the three day forecast. So the first cluster is forecasted temperature for days one, two and three. The second cluster is for precipitation for days one, two and three. The third cluster is some other weather data for days one, two and three. This is the same for a cluster of hourly forecasts: the first cluster is for temperature for hours one, two and three. Second cluster is precipitation for hours one, two and three. And so on.
When I experience the problem it affects difference clusters randomly, but affects the entire set of references. So most recently this problem effected all of the cells that reference the hourly forecast. So if hours one, two, and three reference cells $X$1, $X$2, and $X$3, when this problem occurs, the whole cluster gets shifted and the cell formulas now show the references as $X$50, $X$51, and $X$52.
To further mystify things, it randomly affects the spreadsheets. The first time it happen, it only happened to one cluster referencing the "Chatt Wx" worksheet. The second time it happened it affected two clusters referencing the "Atl Wx" spreadsheet. This last time it only affected one cluster referencing the "Atl WX" worksheet. So it doesn't happen uniformly, and when it affects one cluster, the other clusters appear unaffected.
I think I could solve this by making these cells use the "indirect" formula, but I haven't tried it yet, and I don't know for sure that it will work given the already strange behavior. As well, given that this workbook is already very complex and many the cells already contain some complex formulas, I'd prefer to avoid using the "indirect" formula in order to avoid complicating things further. While I'm not an Excel wizard, I get by. Still, the people I work with are definitely not as versed in Excel as I am, and if they ever had to work on this sheet, it would not be easy to walk them through it.
With that said, has anyone experienced this before? Does anyone have the foggiest idea what could be going on here or what is causing this? And most importantly, does anyone know how I can fix this?
Thank you for your patience in reading through this post and for your help! And please let me know if I need to clarify anything.
Kindly,
Dan
