ExcelDashboardWidgets

Hello, I have the same problem as this post http://www.exceldashboardwidgets.com/ph ... ?f=9&t=343 with the format resetting but I am using Excel 2010 not 2003. So is the solution the same for 2010. Do I have to continually manipulate the objects and their sizes every time I copy and paste a widget. I get similar problems with all widgets. That is, every time I connect them a critical part of the widget changes size of colour and the whole display becomes meaningless.

Hi suthosharko

Sorry to hear you are having problems. This is a well documented Excel bug that can be reproduced as follows..

1. Open two Excel workbooks.
2. In the first one add a text box with the words "Hello World" using the default font of Calibri size 11.
3. Change the font of this text to Arial font size 16 with text color red.
4. Copy this from the first Excel workbook and paste into the second Excel workbook.

This is what you should see happen....

5a. In old versions of Excel the new text box will revert back to Calibri font size 11.
5b. In Excel versions newer than 2010 the new text box will retain its original formatting.

If your textboxes are reverting back to Calibri font size 11, the solution is to look at how the original widget textboxes are formatted and reproduce this formatting once and once only in the new workbook (i.e. for the traffic light widget this requires formatting 18 text boxes). Once the dashboard is configured and saved it will retain the formatting forever.

Hope this helps - yes we understand this is frustrating.

All of our videos are made with Excel 2010.

Cheers

DashboardWidgets

[EDIT] - suthosharko, one solution maybe to use PASTE SPECIAL > KEEP SOURCE FORMATTING - give it a go and let us know how you get on.

Dashboardwidgets,

ok have read you reply about the reversion to default fonts. My test was:

1. Open up your dashboard in Excel 2010.
2. Open up new spreadsheet in Excel 2010.
3. Copy a widget from yours to mine (PASTE SPECIAL > KEEP SOURCE FORMATTING).
4. Copied Widget has some format as source.
5. But as soon as I change the formula for the widget component, it then reverts to Calibri 11.
6. I then use Format Painter copying format from your widget to my copied widget component to restore the correct format and while ever I do no change the formula again it appears to stay in the same format, but if I change the formula again, it repeats step 5 and I have to repeat step 6.

Is that how you expect this to work? Don't think so, since if your tutorials are in 2010 then they would experience the exact same behaviour and they obviously do not. So I am at a loss to understand why I am getting this behaviour in 2010 when you don't in your tutorials?? I must be missing something ... I will play some more to see if I can work it out. But if you have any suggestions happy to hear them.

Hi Dashboardwidgets,

Can you please review my previous reply. I have just spent hours dealing with the issue that every time I change a formula for one of the widget components it reformats that component with default formats as per my previous post, and I am continually having to reapply the format that it had by using Format Painter to copy the format from an unchanged widget over to the one that I just made the formula change in. So in reality its taking me about 3-4 times the time to put together a dashboard, than it should, and am seriously reconsidering whether this product is useable in Excel 2010.

Hi Dashboardwidgets,

Apologies. failed to see that this thread had clicked over to a second page and thus missed the fact that yo had replied to my post at the end of page 1. You've referred to the normal.dot file possibly being an issue. I will try and investigate this further and let you know. I will also try this on a colleague's computer.

Hi Dashboardwidgets,

I have done a bit of research. Normal.dot is related to Word not Excel so not sure how this is relevant. If you think that it is, can you please outline what you think the relevance is. Excel has an equivalent concept being Personal.XLSB, but I can't find that I have a copy of this, so don't think its contributing to the problem, but happy to be persuaded otherwise.

I sent the unlocked widget file to a colleague and then tested a change in its formula and it exhibited exactly the same behaviour, i.e. the format of that part changed, and had to be changed back manually.

I have attached my sample dashboard file, with a screenshot of the widget before and after formula changed, in the Circular Dial1 Main Value in approx. position of cell N5. See how it reduces in size and changes colour to black immediately after I hit the enter key on the formula (without even changing the formula itself ... all I do is enter the same formula as was present).

The last screenshot is my version of Excel. I assume from your comments, that when you load this spreadsheet into your version of Excel, that you don't see the same behaviours as described above and you can change the formulas and the formats stay unchanged. Can you please do that sort of test on my file please?

Thanks

Hey suthosharko

Sorry for the delay - we've been busy working towards a tight deadline with a commercial dashboard project we've been working on for a client.

We've made a quick video here in response to your test.



As you can see we still do not have a solution to the problems with your Excel settings but recognize that this is an important issue to solve so are frantically searching Google and following up on possible solutions.

We will be in touch when we have found the solution.

Regards

DashboardWidgets

Hi DashBoardWidgets,

OK I have decided to do some more research. And I found one of your Youtube videos http://www.youtube.com/watch?v=2xSP5Jc76u0 which precisely describes the problem that I have raised (at its 6 minutes 46 second point) and the need to manually change the formats back to what they were after changing the linking formulas, so I am very confused as to why you said that you could not reproduce this in your version of Excel??

I also found reference to the same Excel issue here
http://www.breezetree.com/articles/link ... -cells.htm as follows:

Cell Formatting: When a shape is first linked to a cell via formula, the shape font formatting is suddenly changed to match the formatting of the cell. To fix it, you will need to select the shape and change the font format using the Excel formatting tools. Starting with Excel 2007, once the shape's format is changed back, subsequent changes to the linked cell do not affect the shape again. However, in older versions of Excel (2000-2003), the behavior is inconsistent and any changes to the cell content can cause the shape font to change again.

My interpretation of this is that whenever a formula is changed it takes on the format of the cell its linking to. Is that a correct interpretation?

But then when I view your video called [Video Tutorial] - Dashboard with Real Time Live Web Data on the website, at the 14:40 min:sec mark you change the formula of the widget, but the format of the widget value does NOT change, which is inconsistent with your first video on Youtube referenced above and the behaviour I found described at breezetree, so I have gone from being very confused to exceedingly confused.

So my basic question is what is different between your 2 videos, one of which demonstrates the problem, which you refer to as an 'Excel bug' (my feeling is that its a feature of Excel rather than a bug ... although an undesirable one at that), and one of which that demonstrates that there is no problem?? If it really was a bug in Excel in the first video have you obtained an updated to resolve the bug?? Else what's different?

Hi suthosharko

You'll notice we've cleaned up the thread - sorry about the confusion.

To quickly address your issues...

1. The video you mention https://www.youtube.com/watch?v=2xSP5Jc76u0 is using Excel 2007 not Excel 2010. You can see it is Excel 2007 if you hover over the bubble that says "click here to subscribe" and click the 'X' on the right hand side. Excel 2007 has the round 'pizza' in the corner. The later video with real-time data uses Excel 2010..
We found that Excel 2007 was slightly problematic when pasting widgets into a new workbook as you had to reformat the style of the text boxes (however only once - after reformatting, the text boxes always kept their styling). Since we switched to Excel 2010 we have not had any problems with loss of text box formatting when pasting widgets into new Excel workbooks - although, we recognize that you seem to be having issues.

Thanks for the article on breezetree.com http://www.breezetree.com/articles/link ... -cells.htm. We were unaware that the sudden changes in formatting were due to the textbox matching its formatting to the data cell. However the article mentions the same experience that we have found that in Excel 2007 subsequent changes to the linked cell do not affect the shape again (i.e. you only have to do this once). As we mention above, the article seems to refer to Excel 2007 not Excel 2010. Have you tried this approach (i.e. changing the formatting of the data-cell to match the formatting of the textbox before pasting it or updating the formula?). We are interested to see if you have the same problems with Excel 2013 - have you considered downloading the 1 month trial version from Microsoft?

Once again, sorry that this is causing you problems - we have googled the issue and found very little in terms of configuration settings that could be the root of the problem?

We would be very interested to hear if any other users have the same problem and if they have any thoughts on the solutions.

DashboardWidgets

Hello ExcelDasboardWidgets,

I have not tried it with 2013, but I will try and see if any of my colleagues have it so I can test it. I did exchange emails with Nicholas Hebb who wrote the Breezetree article. He did a test and said the following:

Here is what I see in Excel 2007 and 2013:

* If you change the text in the cell[being reference by the shape's formula], the text in the shape is updated but the shape formatting remains the same.
* If you change the format of the text in the cell, the shape’s format is unaffected.
* But if you change the formula of the shape to point to another cell, the format of the shape’s text will change to match the format of the new target cell [confirming your question - which was "whenever you change the formula in a shape does it ALWAYS reset the shape's format. That is, it doesn’t just apply to the first time you change the formula, which then requires a change-back of the format of the shape, but rather, every time the formula is changed, it keeps on changing the format of the shape, with each and every formula change"].

Thus, his testing indicates that the behaviour in 2007 (which your first video uses) is the same in 2013. He didn't comment on 2010 though. But my testing shows it to be the same as well. So as far as I can deduce all three have the same behaviour, but you're second video shows that this is not the case with 2010.

I don't really know where to go from here, except to suggest that Microsoft be asked to advise what they think the behaviour should be. Do you have any resources to achieve this?