Formulas

[MichelleJ]

I am trying to create a formula in a master workbook that will link data from
several different users who all have their own saved version to which they
will be adding data to on a daily/weekly basis. When I begin to write my
formula in the master file and open the next spreadsheet, excel is
automatically converting the user's worksheet back to the master document.
Any ideas or suggestions?

 

[Tom Hayakawa]

Here's a brute-force method:

1. Create a spreadsheet for everyone who will be involved. You can make
this a template to ensure everyone has the same layout, down to the last cell.

2. Create a master spreadsheet for yourself which contains a worksheet for
each person whose data you want to capture as well as a master or summary
worksheet in which to gather all the data together.

3. For each person's worksheet tab in your spreadsheet, create a link for
every data field you want to collect. If feasible, it's always easiest to
link each cell on each person's spreadsheet to the corresponding cell in your
worksheet for that person. That way you can see exactly what they see.

4. Sum the data (or whatever you need to do to manipulate it into its final
form) in your master worksheet.

5. Every time you open your master spreadsheet, it will ask you if you want
to update with the latest values - just say "Yes"....and you will get
whatever data was in each person's spreadsheet the last time they saved it.

You might want to bulletproof things on everybody else's spreadsheet to make
sure they can't change the basic layout - just allow them to alter the fields
you want them to alter and protect the rest. Like I said, it's brute-force
and simplistic, but you can put it together quickly.

Good luck,

Tom Hayakawa