🚀 go-pugleaf

I only use Quattro for my own work, so my "dummy-proofing" is of a very
personal variety!  However, the following has worked well for me:

To protect the top and bottom of a COLUMN range, I simply add a cell
to the top and bottom, include those cells in the range, and
(optionally) insert something in them that the numeric functions will
ignore (like a label).  Then I protect just the two "guard cells".  I
end up with something like this:


              1      2      3
         A        -------
         B          1.2
         C          3.4
         D          5.6
         E        -------
         F         10.2

Here I would make A2-E2 be the named range. (Say "DATA") I put the
labels "/-" in A2 and in E2, which are protected.  B2-D2 are not
protected, so that they can be erased, deleted, etc.  Cell F2 might
have something like @SUM(DATA), which simply ignores the labels in A2
and E2.

This scheme also makes it easy to add an extra cell to the top or
bottom of the range and have it be included in the range
automatically.

The same scheme should also work for ROW ranges.  If you do this then
you can evey use "Hide Column" to prevent the columns corresponding to
A2 and E2 in the example from showing up.

Hope this helps.

In article <RON.92Aug19115827@bert.cs.byu.edu> ron@bert.cs.byu.edu (Ron Snyder) writes:

>  I have some budget sheets that are used by several accountants
>in my department.  The budget have many(!) named ranges that are 
>accessed by other worksheets.  Today one of the area heads called 
>me because a summary worksheet that refers to these named ranges
>is giving the wrong answers (like ERR).  It didn't take long to 
>figure out that between he and his accountants, some of the endpoints
>of the named ranges had been inadvertently deleted while they were 
>'cleaning' up the budget worksheet.

If I understand your question correctly, you want users to be able to add or 
delete rows without losing range name references.


      A       B      C   
1
2    \-      \- 
3   200     123     
4   344     767 
5   666     444
6   335     569
7    \-      \-
8  

/rncFIRST~A2.B7~


User Rules:

"You can delete or add rows ONLY between the two dashed lines."

thompson@atlas.socsci.umn.edu (T. Scott Thompson) writes:

>I only use Quattro for my own work, so my "dummy-proofing" is of a very
>personal variety!  However, the following has worked well for me:

>To protect the top and bottom of a COLUMN range, I simply add a cell
>to the top and bottom, include those cells in the range, and
>(optionally) insert something in them that the numeric functions will
>ignore (like a label).  

    Unfortunately, a rather large number of numerical functions do not
ignore labels: they treat them as if they are a valid number with a value
of zero. IMHO this is a major disadvantage of spreadsheets for any serious
numerical work. I would think that the only safe option in your solution
would be the completely blank cell. Is there another trick that you can
use to prevent this problem?

>                        Then I protect just the two "guard cells".  I
>end up with something like this:


>              1      2      3
>         A        -------
>         B          1.2
>         C          3.4
>         D          5.6
>         E        -------
>         F         10.2

>Here I would make A2-E2 be the named range. (Say "DATA") I put the
>labels "/-" in A2 and in E2, which are protected.  B2-D2 are not
>protected, so that they can be erased, deleted, etc.  Cell F2 might
>have something like @SUM(DATA), which simply ignores the labels in A2
>and E2.

   Yes, the @SUM would give the correct total, but @AVG or @COUNT will
think that 5 values are present.

>This scheme also makes it easy to add an extra cell to the top or
>bottom of the range and have it be included in the range
>automatically.

>The same scheme should also work for ROW ranges.  If you do this then
>you can evey use "Hide Column" to prevent the columns corresponding to
>A2 and E2 in the example from showing up.

    This would also make it very easy to leave the cell completely blank,
so all functions will work in the expected way.



Dave Halliwell
Department of Geography
University of Alberta
Edmonton, Alberta

ron@bert.cs.byu.edu (Ron Snyder) writes:

>SOFTWARE: Quattro Pro 3.01

>  I have some budget sheets that are used by several accountants
>in my department.  The budget have many(!) named ranges that are 
>accessed by other worksheets.  Today one of the area heads called 
>me because a summary worksheet that refers to these named ranges
>is giving the wrong answers (like ERR).  It didn't take long to 
>figure out that between he and his accountants, some of the endpoints
>of the named ranges had been inadvertently deleted while they were 
>'cleaning' up the budget worksheet.

>  Protecting those rows doesn't work, because sometimes those ranges 
>really _do_ need to be deleted, and I don't want them calling me 
>everytime they want to delete lines in the worksheet.  (They don't want
>that, either. :))

>  Ideally, Borland would have a "Hide Row" option that is similar to 
>the "Hide Column" option, so that I could hide the top and bottom of
>the named ranges, but I am 99% sure that this handy little gadget is
>a figment of my wishes.

I can think of two options.
1) place extra, blank rows (or rows with labels such as \-) in the range
   at the endpoints.  This won't work if the calling formulas cannot deal
   with blanks, but works for things like @sum.  You could also use some
   predetermined dummy values that the calling cell(s) would know to
   factor out of their formulas, like all zeros or all ones.
2) in WYSIWYG mode, you *can* "sort-of" hide a row, by changing its height
   to a ridiculously low number.

-JTT