Saturday, January 22, 2011

Converting values into English words

Ozgrid has published a neat trick on making Excel convert numbers into actual English words!  This could be quite handy for inserting into word documents or just to show off to your co-workers!

Friday, January 21, 2011

Excel Everest | The Math After the Party

Excel Everest | The Math After the Party

Needle in a haystack - Finding your formulas!

Have you ever found yourself faced with a huge table of data with some cells that contain formulas while others don't?  It can be like finding a needle in a haystack!  MrExcel has come up with a brilliant solution to this by enabling highlighting of formula cells!  Click on the link to the article published by MrExcel for more:

Wednesday, January 19, 2011

Scroll Bars - Negatives & Decimals

Ever wish you could choose a different increment than a positive whole number?  With VBA you can accomplish both and far more.

In I introduced how scrolling bars were able to affect a graph in real time by linking that scroll bar to a cell and allowing the chart and corresponding graph to update based on the values.  The limitation is that you can only scroll from 0 - 30,000.

One way you can work around these limitations is by using VBA to create a value in one (or more) cells outside of the linked as shown here:

Although the value in cell A1 still changes by increments of 1 cell A2 can now be affected by any change in the very same scroll bar.  This is done by using the change method to the object scrollbar1.

Now, with the code above, any change in the scroll bar will launch the script to update cells A2 with the formula defined in that script with cell A1 - 50 * 0.25.  Move the scroll bar to 50 and the value in A2 becomes your new zero.  Move it to the left and you will see it progress down by 0.25 increments in the negative direction!

You can clean this worksheet up by dragging the scroll bar to cover up your linked cell (A1) to allow your user to only see the resulting -1.75 when the scroll bar value is actually 43.  Imagine now how easy it is to have several cells to apply complex formulas, functions or even text results driven by this scroll bar...