How to protect adjacent cells when pasting? until lock cell is available, is seperate table the only option?

Question

How to protect adjacent cells when pasting? until lock cell is available, is seperate table the only option?

How to protect adjacent cells when pasting? I need help to reduce number of tables which are templates to be re-used for future sheet(s).

Until lock cell is feasible, the only option I know is to add another table (just for formulas); if this is the only option, how to simiplify seperate tables maintenance when template tables need to reference (via indirect) where they will be pasted.

EXAMPLE

how to create a group of selected tables? otherwise how to identify them as template for other user(s)?
changing name of a table which is precedent for another table involves changing formula of dependent cells in seperate table.

iPhone XR

Posted on May 22, 2024 1:04 AM

Reply

Answer 1

Badunit

Level 7

26,428 points

May 22, 2024 4:51 AM in response to SunnyInToronto

I understand the issue with not being able to lock cells to protect formulas but you lost me on your use of the term "templates". I'm not exactly sure what you are doing there.

For your second question, if I understood your question correctly, one idea would be to use REFERENCE.NAME to get the table name into a cell then have all the INDIRECT formulas use that cell for the table name.

=TEXTBEFORE(REFERENCE.NAME(Table 1::$A$1,2,1),"::",2)

This will give you "Sheet 1::Table 1" (for example). When you rename the table or sheet it is pointing at, the function will update with the new name. If you duplicate the sheet, the function will be referencing Table 1 on the new sheet so it will become "Sheet 1-1::Table 1" (for example). Use this cell as part of the string in your INDIRECT functions.

Reply