corrupted formula in protected worksheet

T

Tom-S

Dave, for information, I've come up with a workaround for this problem.

The names that originally referred to say cells A1:C1, now refer to cells
A3:C3. (The 'Refers to' box contains the default type ref, and doesn't use
INDIRECT.)

A3:C3 contain formulas =INDIRECT("A1"), =INDIRECT("B1"), =INDIRECT("C1")

Cells A3:C3 are also locked and formatted ;;; so content only shows in
formula bar. When the sheet is protected, cells A3:C3 cannot be cut & pasted.

Meantime, cells A1:C1 accept the user input values of the names Max_Day_1 to
3.

Call-ups to the names can retain the original format =INDIRECT("Max_Day_" &
Sheet1!$D$4), where cell D4 on Sheet1 contains a user input of either 1, 2 or
3.

All of this means that if the user decides to cut & paste any of A1:C1, the
names stay referenced to A3:C3, and the call-ups still work, which is what I
was trying to achieve.

Thanks for considering the problem along the way.

Regards,

Tom
 
D

Dave Peterson

Glad you found a solution that works for you.

Tom-S said:
Dave, for information, I've come up with a workaround for this problem.

The names that originally referred to say cells A1:C1, now refer to cells
A3:C3. (The 'Refers to' box contains the default type ref, and doesn't use
INDIRECT.)

A3:C3 contain formulas =INDIRECT("A1"), =INDIRECT("B1"), =INDIRECT("C1")

Cells A3:C3 are also locked and formatted ;;; so content only shows in
formula bar. When the sheet is protected, cells A3:C3 cannot be cut & pasted.

Meantime, cells A1:C1 accept the user input values of the names Max_Day_1 to
3.

Call-ups to the names can retain the original format =INDIRECT("Max_Day_" &
Sheet1!$D$4), where cell D4 on Sheet1 contains a user input of either 1, 2 or
3.

All of this means that if the user decides to cut & paste any of A1:C1, the
names stay referenced to A3:C3, and the call-ups still work, which is what I
was trying to achieve.

Thanks for considering the problem along the way.

Regards,

Tom
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data copy locks cell in worksheet 3
Enabling Autofilter and Sorting in a Protected Sheet 2
Inserting Picture Into Protected Worksheet 1
insert row and copy formula in protected worksheet 4
Hyperlinks in cells in a protected worksheet not working 5
Hyperlink in protected worksheet 4
Protecting Worksheet - Cannot move between cells. 2
AUTOFILTER IN A PROTECTED WORKSHEET 1

Top