Hiding columns in Excel 2003

[Les]

Hi,

I do know how to hide columns but I have a problem. Say I have a worksheet
which person A uses columns A through T. I plan on using columns AA and AB
and will be entering data into those 2 columns from time to time. Now one of
my columns is my data entry, say Col AA and AB is a calculated cell which
subtracts the value I placed into col AA from one value from one of the
columns that person a is using. Once I set this up and hide my 2 columns,
and I protect the worksheet. Even protected when I place the formula say
=AA5 into some other cell anywhere on the sheet it still tells me what the
contents of that cell is even though it is hidden. I dont want the other
person to be able to view the data in my 2 cells but it doesnt matter if she
is aware that there are a few hidden columns. Perhaps someone out here can
think of a way to do this some other way??

Thanks in advance,

Les

 

[Shane Devenshire]

Hi,

If the other users are not entering any formulas you might be able to do
what you want with conditional formatting, but otherwise I don't think you
can do it.

 

[Harlan Grove]

. . . Once I set this up and hide my 2 columns,
and I protect the worksheet.  Even protected when I place the formula say
=AA5 into some other cell anywhere on the sheet it still tells me what the
contents of that cell is even though it is hidden.  I dont want the other
person to be able to view the data in my 2 cells but it doesnt matter if she
is aware that there are a few hidden columns.  Perhaps someone out here can
think of a way to do this some other way??

....

This is just how ALL spreadsheets work. You can access the value of
ANY cell from formulas in any other cells.

The only practical alternative would be to use hidden worksheets
either before or after all other worksheets to hold your data. Use VBA
to make your hidden worksheets VeryHidden, which means users can't
unhide them using menu commands. That's not particularly secure. Any
user who knows how to run the Visual Basic Editor could do so and see
the names of all the worksheets in your workbook. Even if you password
protect your workbook's VBA Project, the cleverer users could use the
following udf in a different workbook to get a list of all worksheets
in your workbook.


Function bar(fn As String) As Variant
Dim wb As Workbook, rv As Variant, k As Long, n As Long

Set wb = Workbooks(fn)
n = wb.Worksheets.Count

ReDim rv(1 To n, 1 To 2)

For k = 1 To n
rv(k, 1) = wb.Worksheets(k).Name
rv(k, 2) = wb.Worksheets(k).Visible
Next k

bar = rv
End Function


Just past your workbook's base filename with extension to this udf,
and it returns an array of the names of ALL worksheets in your
workbook along with their visibility.

Nevertheless, very hidden worksheets should be sufficient to hide
information from most users.