如何从单元格而不是值中获取公式?

如何从单元格中获取文字值(公式)而不是结果值?

How do you get the literal value (the formula) from a cell instead of the result value?

期望的示例:

• A2:"Foo"
• B3:"= A2"-因此显示为"Foo"
• C3:"= CELL(" formula，B3)"显示为"= A2"

当然，CELL()不支持"formula"参数，仅用于说明目的.我已经查看了功能列表，但是什么都没跳出来.

Of course, CELL() doesn't support a "formula" parameter, that's just for demonstrating the intent. I've looked over the function list and nothing jumps out at me.

使用案例:
我想引用另一行，并基于该引用从该行获取其他单元格.放置一个明确的行号是行不通的(例如B3 ="2")，因为修改行后它不会自动更正.具体来说，我希望一行中的多个列相对于另一行中的列，并且能够在一个地方更改相对行而不必编辑那些列中的每一个.

USE CASE:
I would like to make a reference to another row, and based on that reference get other cells from the row. Putting an explicit row number won't work (e.g. B3 = "2"), since it will not auto-correct when rows are modified. Specifically, I would like for multiple columns in one row to be relative to columns in another row, and be able to change the relative row in one place without having to edit each of those columns.

通常，要使一行相对于另一行，可以将这样的列值放入:"= A2 + 5"和"= B2 + 10".这意味着A列是相对行值+5"，B列是相对行值+ 10".如果要更改相对行(例如，更改为第56行)，则需要将每列更改为"= A56 + 5"和"= B56 + 10".如何通过仅编辑一个字段来完成此操作?

Normally, to make one row relative to another you would put column values like this: "=A2+5" and "=B2+10". This means that A column is "relative row value +5" and B column is "relative row value + 10". If you want to change the relative row (for example, to row 56), you need to change each of the columns to "=A56+5" and "=B56+10". How to accomplish this by editing just one field?

举一个实际的例子，考虑一个包含任务列表的工作表，为了计算结束日期，每个工作表都可能被标记为跟随"另一个，但是您希望能够更改参考任务并提供支持任务之间的N:1关系.

For a practical example, consider a sheet that is a list of tasks and each one may be marked as "following" another for purposes of computing end dates, but you would like to be able to change the reference task and to support a N:1 relationship between tasks.

[更新]

实际上，我确实有针对特定用例的解决方案.但是我仍然对最初的问题感到好奇:可以访问单元格中值背后的公式.

Actually, I do have a solution to the specific use case. But I am still curious about the original question: getting access to the formula behind the value in a cell.

解决方案1: -A2:"= ROW()" -B2:"Foo" -C3:"= A2"显示为"2"，并在添加/删除行时自动调整以维持引用

SOLUTION 1: - A2: "=ROW()" - B2: "Foo" - C3: "=A2" displays "2" and auto-adjusts to maintain reference as rows are added/removed

解决方案2:

另一种解决方案是添加唯一的"id"列，并按id存储引用，然后使用LOOKUP()查找行.

Another solution would be to add a unique "id" column and store references by id, then find the row using LOOKUP().