hints, tips, tricks for using workspace files

[MrShorty]

I use Excel (2002) as a calculator, data reduction, and analysis too
for laboratory experiments. It often seems that I will end up with
handful of workbooks that work together to acheive this end. As a
example, I could have one workbook that contains information o
standards used to calibrate a GC, then a workbook each for eac
individual experiment, then a summary workbook that pulls results fro
the individual experiments and analyzes, summarizes, and display
tables/charts.
I've tried a couple of times using workspace files to try to combine o
associate a group of workbooks together, but it just doesn't seem a
useful a feature as I was hoping it would be. If I click on th
workspace file it will open all of the associated workbooks, but thi
seems to be the limit of its utility. There doesn't seem to be
"close workspace" command. When I close the individual workbooks,
get a "this file may have been modified by another user. Save a
another name or overwrite changes" message when I'm the only one wh
has access to the file.
Does anyone else use workspace files? What do you use them for? Ho
well do they work for you? Or are they really not very useful

 

[Paul W.]

There are several ways to summarize data.
I never used workspaces.
If your result data from your experiments is is an regular shape then you
could try to summarize and analyse using pivot tables.


Or you can do it this way *** If you have your Sheets all set out in the
same way ***.
Example :
on sheet 1 you have experiment 1 with some data from experiment 1 test 1 in
cell b1
on sheet 2 you have experiment 2 with some data from experiment 2 test 1 in
cell b1
....
on sheet 99 you have experiment 99 with some data from experiment 99 test 1
in cell b1

The way best make sure that all the sheets are the same format (i;e. all set
out in the same way) is to make one sheet and then clone it by grabbing the
tab and using the mouse and the Ctrl key.
Then - just for more clarity - you can rename the sheets Exp1, Exp2,...
Exp99 ..

Then when you have finished filling in all the data in sheets Exp1 to Exp99,
you clone Exp1 one more time to make the summary sheet. Place this sheet at
the extreme left of Exp1. Delete any superfluous sheets.
Name the summary sheet something significant like "Summary Sheet".

You then go into cell b1 on the summary sheet and type "= sum(" without
the quotes. Leave the brace open.
Then press the shift key and click on the tab of the Exp1 sheet.
Click on cell b1 in Exp1.
Then click on the tab of your last sheet (Exp99 in this example).
Click on b1 in Exp3.
Close the brace.
Then you can copy and paste the formula into all the other cells where you
want to summarize data.

There you have it.
In b1 on the summary sheet you should have the formula =SUM(Exp1:Exp99!B1)
b1 in the summary sheet wil addition all the b1 cells between Exp1 and
Exp3.
Of course you can use averages and many other statistical functions.
You will have to be careful if you want to modify the structure of any
sheet.
All the sheets should have the same structure, format, set up.
You should group the sheets before making any changes.
And ungroup them once you have finished.

Add a new sheet ?
Say you have 48 experiments and you want to add a 49th.
You just clone a new sheet. Rename it Exp49 and make sure that it is
"physically" positioned in betwween Exp1 and Exp99.

Delete a sheet from the summary ?
You want to remove an experiment from the summary? Slide it to the right of
Exp99.


You can even get the name of the tab to appear in a cell and use it as a
title for each experient sheet by using the cell function. Tricky but
possible.

HTH

paul











"MrShorty" <[email protected]> a écrit
dans le message de (e-mail address removed)...

 

[MrShorty]

Thanks for your interest Paul. It's true that that is a good way t
organize a summary workbook, and many of my summary workbooks ar
organized in that way (it depends on the specific experiments).

I don't have trouble building summary workbooks. My questio
specifically targets the use of workspace files. My limited experienc
with them suggests that they are little more than a shortcut for openin
multiple workbooks at once. I'm wondering if others who work with the
have found ways that workspace files can be useful