Cell Protection and External Data

[tbraddy]

I have a worksheet that has user input to calculate data that will be
put into a database for testing. The worksheet also has a Get External
Data to get that data once the test has run.

I'm trying to preserve the formulas and outputs by protecting the
cells, but when I protect the worksheet, I can't get external data.

Is there a way to protect some cells on a worksheet, but still get
external data on that same sheet? For now, I can put it on a
different sheet, but I would like to have it all on one sheet.

Todd

 

[Shane Devenshire]

Hi,

You could try a macro to get the external data, if you have set protection
through VBA you can use the UserInterfaceOnly:=True option to allow the code
to run the refresh against the protected sheet.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Harlan Grove]

I'm trying to preserve the formulas and outputs by protecting the
cells, but when I protect the worksheet, I can't get external data.

Is there a way to protect some cells on a worksheet, but still get
external data on that same sheet?  For now, I can put it on a
different sheet, but I would like to have it all on one sheet.

Why not fetch the external data into a different, unprotected
worksheet? If you need to show it in the protected worksheet, you
could use simple formulas to fetch it from the other worksheet. You
could even guard against user row/column insertion/deletion using less
simple INDEX formulas. For example, if the unprotected worksheet were
named FETCH and you define the name FETCH referring to =FETCH!
$1:$65536, and you were retrieving the external data as a table with
the top-left cell (source cell FETCH!A1) in cell X99 in the protected
worksheet, you could use the following formula in cell X99,

=INDEX(FETCH,ROWS(X$99:X99),COLUMNS($X99:X99))

then fill X99 down and right as needed. No matter what users do in the
FETCH worksheet, the FETCH name will remain defined as FETCH!
$1:$65536, and these INDEX formulas will always return the value of
the same cell in the FETCH worksheet.