BadBlue's Excel Sharing features management capabilities that help you monitor
and control your shared spreadsheets.
BadBlue's powerful, advanced Excel Sharing features include:
Multi-user conflict resolution - when two or more users change the same cell
Complete auditing and logging - you can track every change to your spreadsheets
Grid read protection by user - you can restrict which users can see which cells
Grid write protection by user - you can restrict which users can update which cells
Freeze column headers - you can freeze a row as the header text for each column
Add navigation fenceposts - add navigation 'fenceposts' to large spreadsheets
Each of these features is described in greater detail, below.
You can try out of the features for free with
BadBlue Personal Edition. When you're ready to buy, you can
order BadBlue Enterprise Edition with instant, electronic
delivery.
Introduction
The auditing feature logs every change to your spreadsheets.
Conflict resolution
ensures that two users making changes won't intentionally overwrite each other's
work. And cell-by-cell protection lets you control who can read and write...
by row, column or even individual cell.
|  |
|
How does conflict resolution work?
|
|
The term "conflict resolution" describes the situation in which two or more users
have modified the same worksheet cells at nearly the same time. BadBlue
supports conflict resolution warning messages. These messages notify
users when their changes have "collided" with earlier changes by other
users. For instance:
1) User A opens a shared workbook
2) User B opens the same shared workbook
3) User B changes cell B2 to 18000
4) User A changes cell B2 to 12000
5) User B saves their changes
6) User A saves their changes
User B's changes will be saved to the spreadsheet. But
because user A's changes came after user B's changes, A's change to cell B2
will not be saved (note: other, non-conflicted cells that were changed will
be saved).
Instead, user A will receive a warning message:
Cell B2 conflict: user B updated value to '18000' (your update to '12000' was skipped)
User A can now choose to re-enter the change or contact user B to determine
which change takes precedence.
|
|
Can I see who has made changes to my Excel spreadsheet?
|
|
Yes. Each change to a shared spreadsheet is recorded in a log file. To see how this
works, consider the following example. If a shared spreadsheet is located in:
c:\my documents\spreadsheets\sales-funnel.xls
A log file is automatically created in the file:
c:\my documents\spreadsheets\sales-funnel.txt
The log file lists the date and time of each change, who made the change, and what was changed.
For example:
04-05-14 22:01:54 Cell D46 updated to '15000' by user 'maryj'
04-05-14 22:09:53 Cell D45 updated to '25000' by user 'maryj'
04-05-14 22:10:04 Cell D31 updated to '18000' by user 'wallacet'
04-05-14 22:10:46 User georgep does not have update permission
04-05-14 22:11:16 Cell D30 updated to '150000' by user 'maryj'
04-05-15 18:56:04 Cell J309 updated to 'Champion Industries' by user 'maryj'
04-05-15 18:56:04 Cell J301 updated to 'Champion Industries' by user 'maryj'
04-05-15 18:56:04 Cell I301 updated to '192' by user 'maryj'
04-05-16 12:19:46 ACL restriction on user wallacet prevents updating cell C18
04-05-16 12:22:19 ACL restriction on user wallacet prevents updating cell C16
04-05-16 12:22:19 Cell D17 updated to '4-12-2004' by user 'wallacet' |
Note that -- in addition to changes -- errors are recorded.
Errors log the users who tried to update certain
spreadsheets or cells but don't have the correct access rights to do so.
You can password-protect the log-file. To do so, use the following steps:
1) Click on the Manage tab
2) Click on the Setup Search tab link
3) Press the browse button, select the log-file, then press the Share button
4) Click on the Manage tab
5) Click on the Password-protect files link
6) Select the file from the Shared Area list, then press 'Require user authentication'
|
|
Can I freeze column headers on the top of each column?
|
|
Yes. By defining a configuration file for the spreadsheet, you
can "freeze" headers at the top of each row. Consider the following example:
If a shared spreadsheet is located in:
c:\my documents\spreadsheets\sales-funnel.xls
A configuration file can be created at:
c:\my documents\spreadsheets\sales-funnel.ini
The configuration file is a text file that can be edited using Notepad or any text editor.
[VIEWPORT]
;
; Define frozen header row for each column
;
ROW_HEADER=2
; |
The ROW_HEADER setting in the VIEWPORT section of the
spreadsheet's configuration file defines which row should be considered
the 'frozen' header. By defining ROW_HEADER=2, the text on row 2
will be added to each column header to making navigation easier.
|
|
Can I freeze row headers at the left of each row?
|
|
Yes. By defining a configuration file for the spreadsheet, you
can "freeze" headers on the left side of each row. Consider the following example:
If a shared spreadsheet is located in:
c:\my documents\spreadsheets\sales-funnel.xls
A configuration file can be created at:
c:\my documents\spreadsheets\sales-funnel.ini
The configuration file is a text file that can be edited using Notepad or any text editor.
[VIEWPORT]
;
; Define frozen header for each row
;
COLUMN_HEADER=A
; |
The COLUMN_HEADER setting in the VIEWPORT section of the
spreadsheet's configuration file defines which column should be considered
the 'frozen' header. By defining COLUMN_HEADER=A, the text in column A
will be added to each row header to making navigation easier.
|
|
I've expanded my page to be quite large, can I see which row and column I'm viewing?
|
|
Yes. You can add 'fencepost' row and column designators to the live-view display.
If you typically display large numbers of rows and columns at a time, the fenceposts can
help you navigate. To see how, consider the following example:
If a shared spreadsheet is located in:
c:\my documents\spreadsheets\sales-funnel.xls
A configuration file can be created at:
c:\my documents\spreadsheets\sales-funnel.ini
The configuration file is a text file that can be edited using Notepad or any text editor.
[VIEWPORT]
;
; Define frozen header row for each column
;
ROW_HEADER=2
;
; Define fenceposts every 18 rows and 10 columns
;
FENCEPOST_ROWS=18
FENCEPOST_COLUMNS=10
; |
The FENCEPOST_ROWS setting in the VIEWPORT section of the
spreadsheet's configuration file defines how many rows are displayed between
each "row fencepost". The FENCEPOST_COLUMNS setting controls the
number of columns between each "column fencepost". The screen-shot, below,
illustrates how this helps the user when a large number of rows and
columns are displayed.
|
|
Can I protect cells within a spreadsheet from certain users?
|
|
Yes. BadBlue supports cell-by-cell restrictions for specified users.
To see how these restrictions work, consider the following example. If a shared spreadsheet is located in:
c:\my documents\spreadsheets\sales-funnel.xls
You can create a restrictions file named:
c:\my documents\spreadsheets\sales-funnel.ini
If there is no restrictions file present, all users who have passwords for the
file will be able to read it (or update it if they have update permission).
You can create a restrictions file using any text editor (like Windows Notepad).
The following is a sample restrictions file:
[ACL]
;
; maryj is not listed, she has full read and update permission
;
wallacet=READ=A1:A9999,READ=C1:ZZ9999,WRITE=A1:A9999,WRITE=D1:ZZ9999
davids=READ=A1:C9999 |
User maryj is not listed. She has full read and update permission because
she is a valid user and the spreadsheet has been password-protected with update permission
allowed.
User wallacet can not see column B; nor can he change
columns B and C. This is because he is listed as having read permission
for A1:A9999 and C1:ZZ9999 (skipping column B). He is also listed as having
write permission for A1:A9999 and D1:ZZ99999 (skipping columns B and C).
User davids can only read columns A, B and C. He cannot write to any of the
columns.
|
Note that the file must begin with a line that reads [ACL]. Each user
name that requires special restrictions must be listed on a separate line, followed
by an equals sign (for instance, "joe=...").
The allowed READ and WRITE permissions must be listed explicitly.
For instance, if the user
can only read columns A, C, and E, they must be listed in rectangular regions (e.g.,
joe=READ=A1:A9999,C1:C9999,E1:E9999). Or if another user could write to columns
D, E, and F, their restriction clause would read WRITE=D1:F9999.
Protecting Tabbed Workbooks
Note that workbooks (tabbed sheets in the same Excel file) can also be individually protected.
This example illustrates how different workbooks in the same file can be protected:
[ACL]
;
; First worksheet:
; Mike can read columns A, C, D, E, F, and G
; Mike can write to column G only
; Dave can not read it (dummy cell DD9999 is all he can read)
; Second worksheet:
; Mike can read and write to column A only
; Dave can read and write to column A only
;
mike=READ=1!A1:A9999,READ=1!C1:G9999,WRITE=1!G1:G9999,READ=2!A1:A9999,WRITE=2!A1:A9999
dave=READ=1!DD9999:DD9999,READ=2!A1:A9999,WRITE=2!A1:A9999 |
The syntax for each protection statement is:
Username = READ | WRITE = Workbook-number ! Top-left-cell : Bottom-right-cell ,+
The workbook-number is, of course, optional and the first workbook is assumed if unspecified.
Denying access to all worksheets
By default, all worksheets in the file are accessible to the user if not specified. Using
the DENY=ALL directive, you can effectively prevent the user from
reading or writing the sheets unless specified in one of the protection statements.
For example, consider a workbook that contains 12 sheets. The following example prevents
a user ('mike') from accessing any sheet except the second sheet. For the second sheet,
'mike' is permitted to only read column A.
[ACL]
;
; Mike can only see the second worksheet...
; for which he can only read column A
;
mike=DENY=ALL,READ=2!A1:A9999 |
Setting a default worksheet for a user
You can set a default worksheet for each user. When the user performs a 'live view'
of the file, it will automatically open to the specified sheet. The following
builds on the previous example using the DEFAULTSHEET directive.
When the user 'mike' opens the live-view of the file,
the system will automatically display the second sheet. Each user can have their own
default worksheet. If unspecified, the first worksheet will be displayed.
[ACL]
;
; Mike can only see the second worksheet...
; for which he can only read column A
; Also, automatically open the second sheet.
;
mike=DEFAULTSHEET=2,DENY=ALL,READ=2!A1:A9999 |
Allowing users to view formulas
You can selectively allow users to view (and edit) formulas. By default,
a user can never see a formula. However, using the
ALLOW=FORMULAVIEW directive,
you can permit this behavior on a user-by-user basis.
The following example demonstrates this behavior for user 'mike':
[ACL]
;
; Mike has full read-write access for
; the first sheet and can also view
; formulas when editing cells.
;
mike=ALLOW=FORMULAVIEW,READ=A1:ZZ9999,WRITE=A1:ZZ9999 |
Denying access to all users
By default, workbooks may be viewed and modified by a user if the user is not specified in the
restrictions file. However, by using
the DENY=ALL directive, you can remove access to the file -- for everyone --
unless the user is specified in one of the protection statements.
For example, consider a shared workbook on a system with many users.
For this particular workbook, only one user ("mike") is allowed to read and
write the workbook.
[ACL]
;
; Deny access to everyone, except...
; Mike, give him unlimited access to all workbooks.
;
DENY=ALL
mike=READ=*!A1:Z9999,WRITE=*!A1:Z9999 |
Password-protecting the Restrictions File
You can password-protect the restrictions file itself. To do so, use the following steps:
1) Click on the Manage tab
2) Click on the Setup Search tab link
3) Press the browse button, select the restrictions file, then press the Share button
4) Click on the Manage tab
5) Click on the Password-protect files link
6) Select the file from the Shared Area list, then press 'Require user authentication'
|
Can I control whether cell colors and fonts are displayed?
|
|
Yes. By default, BadBlue attempts to display
the proper cell formatting for live-view (browser-based)
spreadsheets.
The supported cell formatting includes cell background color,
font color, font size, and font type-face.
By modifying the configuration file for the workbook,
the integrator can turn off this "rich text" formatting. |
|
| This may be useful for performance reasons.
Older versions of Excel
(for example, Excel 97), which are supported by BadBlue,
may exhibit slower performance when displaying pages
in "rich text" mode. Turning off formatting should
result in faster performance in these situations. Newer versions
of Excel should perform well in either formatting mode.
|
|
Formatting can be turned off using a workbook configuration.
Consider the following example. If a shared spreadsheet is located in:
c:\my documents\spreadsheets\sales-funnel.xls
A configuration file can be created at:
c:\my documents\spreadsheets\sales-funnel.ini
The configuration file is a text file that can be edited using Notepad or any text editor.
[VIEWPORT]
;
; Turn off "rich text" formatting for this workbook
;
SHOW_FORMATTING=N
; |
|
|
Can I override spreadsheet display global settings on a file-by-file basis?
|
|
Yes. By defining a configuration file for the spreadsheet, you
can define its default display settings. Consider the following example:
If a shared spreadsheet is located in:
c:\my documents\spreadsheets\sales-funnel.xls
A configuration file can be created at:
c:\my documents\spreadsheets\sales-funnel.ini
The configuration file is a text file that can be edited using Notepad or any text editor.
[VIEWPORT]
;
; Define default display settings for this spreadsheet
;
COLUMNS=12
ROWS=30
CURRENCY_DECIMAL_POINTS=2
WIDTH_PERCENTAGE=93
; |
The COLUMNS setting in the VIEWPORT section of the
spreadsheet's configuration file defines how many columns should appear
when the spreadsheet is initially opened.
The ROWS setting defines how many rows should appear
when the spreadsheet is initially opened.
The CURRENCY_DECIMAL_POINTS setting defines how many decimal-points
are used when currency values are displayed.
Lastly, the WIDTH_PERCENTAGE setting defines the size of the
spreadsheet in percentage of browser window width (100 is the default,
meaning the entire browser window is used). This value may be less than
100 or greater than 100 (e.g., 200 would indicate a width twice the size
of the current browser window). Note that some browsers may not support
this feature.
|
|
Can I control the display of Charts?
|
|
Yes. Starting with version 2.62, BadBlue supports display of
Excel charts over the web.
By defining a configuration file for the spreadsheet, you
can define the chart display settings. Consider the following example:
If a shared spreadsheet is located in:
c:\my documents\spreadsheets\sales-funnel.xls
A configuration file can be created at:
c:\my documents\spreadsheets\sales-funnel.ini
The configuration file is a text file that can be edited using Notepad or any text editor.
[VIEWPORT]
;
; Define chart display settings for this spreadsheet
;
SHOW_CHARTS=Y
CHART_ZOOM_PCT=150
WMF_PELS_PER_INCH=60
; |
The SHOW_CHARTS setting in the VIEWPORT section of the
spreadsheet's configuration file defines whether or not charts should be
displayed. The default is "Y", for "Yes". Set this option to "N" to
prevent charts from being displayed.
The CHART_ZOOM_PCT setting defines the percentage of chart size
to be displayed in the browser. The default is 150 (percent), which should make
most charts easier to read after the conversion to JPEG format. Percentages
can range from 50 to 500.
The WMF_PELS_PER_INCH setting is used to translate Excel's
WMF formatted charts to JPEG. The default is 60 -- lower values translate to
greater detail, while higher values signify less chart detail. Values
can range from 12 to 1500.
Generally these settings should not need to be modified, but they
are provided to give the integrator more control of chart operation.
|
|
Can I control how Excel links are updated?
|
|
Yes. Starting with version 2.62, BadBlue supports control
of links between Excel files.
By defining a configuration file for the spreadsheet, you
can control link updates. Consider the following example:
If a shared spreadsheet is located in:
c:\my documents\spreadsheets\sales-funnel.xls
A configuration file can be created at:
c:\my documents\spreadsheets\sales-funnel.ini
The configuration file is a text file that can be edited using Notepad or any text editor.
[VIEWPORT]
;
; Define link updating
;
UPDATE_LINKS=3
;
; 0 Doesn't update any references
; 1 Updates external references but not remote references
; 2 Updates remote references but not external references
; 3 Updates both remote and external references
; |
The UPDATE_LINKS setting in the VIEWPORT section of the
spreadsheet's configuration file defines how links are updated. The
default is 3, which means that both remote and external links
are always updated.
|
|
Can I generate a 'permanent' link (URL) to the "live view" of my spreadsheet?
|
|
Yes. To generate a permanent, static link (URL) to a live view of your spreadsheet,
just click on the Folders tab, then the spreadsheet's folder. A "live view" link
will be generated to the right of the file name. Instead of clicking on the link,
right-click on it. Then select "Copy shortcut". This will save the link to the clipboard.
You can now paste the link into an email or an HTML page. Note that you
may have to change the IP address or domain name if you've been using
127.0.0.1 (which is just your own machine). For more information on
sharing your Internet address with others, please see the
Troubleshooting Guide.
If the Excel spreadsheet is only available via the Search Tab,
search for it normally and then click on the Live View option to
generate the normal, dynamic link. For example, a dynamic link that
results from a search and a live-view might read:
http://.../ext.dll?...page=xls2.htx&a0=/get/153/filename.xls&a1=...
To turn this link into a permanent URL,
you can replace the search index ("153", in the example above) with
the path number ("path9", in the example below).
The path-number can be determined by reading the EXT.INI file.
http://.../ext.dll?...page=xls2.htx&a0=/get/path9/filename.xls&a1=...
Using the path-number instead of a search index will generate a completely
static link that will never change unless you change the EXT.INI file.
|
|
