查找相对于其他单元格中地址的位置 [英] Find cell in position relative to address in other cell
问题描述
是否有一个函数可以放在单元格 A2
中,这将返回任何单元格内的任何单元格,在$ code> A1 ?
Is there a function I can put in cell A2
that will return whatever is in the cell below whatever cell is written in A1
?
所以例如,如果 A1
包含 = B35
然后
A2
将返回单元格 B36
中包含的值,如果我更改 A1
到 = C17
然后 A2
现在将包含 C18
?
So for example, if A1
contains =B35
then A2
will return the value contained in cell B36
and if I change A1
to =C17
then A2
will now contain whatever is in C18
?
我一直在尝试使用 indirect()
, offset()
和 address()
,但无法确定。
I've been trying to use combinations of indirect()
, offset()
and address()
, but can't figure it out.
推荐答案
假设你不想做任何比修复单个单元格参考更复杂的任何事情,你可以混合一点VBA。
Assuming your not wanting to do anything more complex than offsetting a single cell reference you could mix in a little VBA.
在新的模块中:
Public Function tFormula(ByVal Target As Range) As String
tFormula = Mid(Target.FORMULA, 2)
End Function
B1 == C1\"
B1 = "=C1"
C2 =Hello
A1 == Offset(tFormula(B1),1,0)
A1 = "=Offset(tFormula(B1),1,0)"
Offset(tFormula(B1),1,0)=Hello
Offset(tFormula(B1),1,0) = "Hello"
这篇关于查找相对于其他单元格中地址的位置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!