linking workbooks

P

phil

Hi, what is the best way for others to enter (simple Y or
N entries) in to a shared workbook that updates my
workbook as the overseeing person. this data will need to
be entered every day for me to keep a track on standard
operating checks that i have devised for the safe upkeep
of a secure premises. Would linking the information or
shared workbook be the best option or something else?
:) Phil
 
A

Arvi Laanemets

Hi

I myself prefer for such cases the design, where every user has his own
workbook on shared network resource (on server). All workbooks are of
similar design, and (all needed) data from those are mirrored into
supervisor workbook through links. When there are some general data, needed
by all users, then they are on separate sheet(s) in supervisor workbook, and
all users mirror this/those sheet(s) through links into their workbooks too.

Supervisor workbook can be situated on network resource, or on local
computer (when users have to mirror data from supervisor workbook too, then
it's better to have it on network resource).

For supervisor workbook I usually use one of next designs:
1. Data (detailed or summary) from all users workbooks are collected into
single (hidden) sheet, with an identifier for user added. For every user is
reserved some number of rows, headers are on top row. A fixed dynamic range,
which includes all mirrored data, is defined. ODBC query is used to collect
all those data into table without gaps on separate sheet. Report sheet(s),
based on condensed table, are created.

2. For every user is created a sheet, where (detailed or summary) data from
this user workbook is collected. Report sheet(s), based on those users
sheets, are created.

Why an own workbook for every user? The main reason - because then all users
can open their workbooks and enter data, independent of others - no messages
that workbook is opened by another user anymore.
 
Top