Thursday, July 28, 2005

 

Trick 3 - Variation on a theme

Reiner came up with a nice variation on Trick 2.

We have a spreadsheet that is orthogonal. However, in the first column, in this case we have a trade id.

The trick makes it neat to add a new trade. The event when something is entered in the sheet is there. When a new ISIN is added, the formulas from the row above are copied down automatically.

The alternative is running lots of nasty macros.

Wednesday, July 20, 2005

 

Trick 2 - Entering Numbers in Formulas

Here is a snippet from a sheet. X contains a number, and XX is X squared.



i.e. The formula in cell X is "=X*X"



Now type 10 in cell XX overwriting the formula, and you get this.

X now contains the square root of 10, and XX still contains the formula "=X*X"

What's going on?

Well, there is some code behind what is happening.


Sub Start()
Application.OnEntry = "Update"
End Sub

Sub Update()
On Error GoTo errorhandler
With Application.Caller
If Application.Caller.Address = Range("XX").Address Then
Range("X") = Sqr(Application.Caller)
Range("XX").Formula = "=X*X"
End If
End With
Exit Sub
errorhandler:
Exit Sub
End Sub


First the start macro needs to be run. Typically this would sit in the autoexec macro.

Then when something is entered on the sheet, the Update macro is called. This checks to see if you are overwritting the formula in XX. If it is, it calculates the square root, and repastes the formula back in cell XX.

End result, code that works both ways.

If there is a function such as eval in vba, the square root call could also be parameterised.

Saturday, July 09, 2005

 

Download details: Excel 2003/2002 Add-in: MSN Money Stock Quotes

Download details: Excel 2003/2002 Add-in: MSN Money Stock Quotes: "This add-in for Excel 2003 and Excel 2002 allows you to get dynamic stock quotes from the MSN� Money� Web site. The tools and features found in Excel are particularly well suited to analyzing financial data such as stocks. This add-in allows you to easily gather and study the stocks of interest to you, refresh your quotes when you want, and readily change or modify the quotes gathered."
 

Office Tips and Hints Blog

Office Tips and Hints Blog: "The Data Consolidation technique allows you to compare lists quickly and easily.

With the Consolidation technique, you can identify the number of duplicate entries in two or more lists without using a formula.
(not that it's easier, just that there are no formulas) "

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]