160 likes | 284 Views
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
E N D
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 source.xls topic.alt 2. Save as 1.Save as source..csv 3. Copy cell in 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 3. Click on Paste Link 1. Select cell B9
This is the topic.csv file after Paste Link has been applied (to all 4 cells in fact) These 4 cells are now linked to appropriate ones in source.csv Ignore scores; updated only when re-loaded into 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