The Annalisa Tutorials
4. Linking Annalisa with Excel
The possibilities
• If a Rating in an Annalisa is
• a value between 0 and 1 in an Excel cell, e.g. 0.9
• a value between 0 and 1 in an Excel cell produced
by a formula or function, e.g. (B8*B6)/100
• a value between 0 and 1 in an Excel cell which is
the output from another application (e.g. Matlab)
that is dynamically linked to the Excel cell
• Then a link can be made between the
Annalisa file and the Excel file such that
changes in the underlying file or model can be
reflected in the .alt file via simple re-loading
Summary (step-by-step slides follow)
• If you want one or more Ratings in an alt file (which we will call
topic.alt) to be the value produced by a formula contained in
an Excel worksheet (which we will call source.xls)
• Save topic.alt as a csv file called topic.csv
• Open source.xls and Save it as source.csv
• select the cell concerned in source.csv and Copy
• make topic.csv the active window and Paste Special>Paste
Link into the appropriate cell
• Save topic.csv (as csv)
• Repeat for as many cells as you wish (n.b. you may use
different Excel source files for different cells)
• Go to topic.alt, click on the Load Topic icon and Open
topic.csv
• You have an Annalisa file with Ratings that` will change
simultaneously with changes in source.csv if topic.csv is re-
opened in Annalisa
Annalisa Excel
topic.alt source.xls
1.Save as 2. Save as
3. Copy cell in source..csv
topic.csv 4. Select cell and Paste
Special/Paste Link into
5. Save as
topic.csv
6. Make topic.alt the active window
7. Load topic.csv, which now has links to source.csv
8. Save topic.alt (optional)
This is the Excel source file saved as a csv file (source.csv)
Formula underlying
value in cell A15
Select cell A15 and
Copy
Next : Paste into topic.csv
2. Select Paste Special
From View menu
1. Select cell B9 3. Click on
Paste Link
This is the topic.csv file after Paste Link has been applied (to all 4 cells in fact)
These 4 cells are Ignore scores;
now linked to updated only when
appropriate ones re-loaded into
in source.csv Annalisa
Next : re-load into Annalisa
This is the new .alt file with Rating links to Excel source file
Scores updated
Ratings updated
next: the source file is revised
Some revisions are made to source.csv and it is re-saved
topic.csv file is automatically updated
This is the revised topic.csv file
Note that both topic and source file have to be open during the original linking
operation. Subsequently changes to source file will impact on topic file whenever it is
open/ed.
Link for selected cell B8 displayed
Next : the re-loaded topic.alt file
Surgery is still favoured
slightly
but what if Weightings are changed?
If you have any questions or
suggestions regarding this or any other
tutorial please send them to
jack.dowie@cafeannalisa.org.uk