具有范围的动态INDIRECT公式 [英] Dynamic INDIRECT formula with range
问题描述
在我的情况下,我有一些动态输入,即单元格I25中的月份名称.基于月份,单元格H32中的函数应引用具有月份名称的工作表以及该工作表中的单元格A18.现在,我可以处理这个问题,并通过INDIRECT函数使之成为可能.
I have some dynamic input, in my case, the name of the month in cell I25. Based on the month the function in cell H32 should reference a sheet with the name of the month and cell A18 within that sheet. Now this I can handle and have made it possible through the INDIRECT function.
我遇到的问题是动态范围.例如,我希望单元格H33引用工作表二月"中的单元格A19.我最接近的是=INDIRECT($I$25"&"!A18:A200")
.它似乎正在工作,但是由于某种奇怪的原因,它开始引用从A36开始的单元格内容,但我没有得到.有建议吗?
The issue I'm having is with dynamic range. For example, I would like cell H33 to reference cell A19 within the worksheet "February". The closest I got to it was =INDIRECT($I$25"&"!A18:A200")
. And it seems to be working, but for some strange reason it starts referencing the cells contents from A36 onwards, which I don't get. Suggestions?
任何帮助将不胜感激.
推荐答案
在H32
中使用它并将其向下拖动:
Use this one in H32
and drag it down:
=INDIRECT("'" & $I$25 & "'!" & CELL("address",A18))
注释:
- 我已将公式中的
$I$25 & "!"
更改为"'" & $I$25 & "'!"
以使其更可靠(对于工作表名称包含空格的情况,应在单引号中包含工作表名称,例如:'My sheet'!A18
) - 在
H32
中,此公式的计算结果为=Feb!A18
(其中Feb
是工作表名称),在H33
中的计算结果为=Feb!A19
,依此类推.
- I've changed
$I$25 & "!"
to"'" & $I$25 & "'!"
in formula to make it more reliable (for the case when sheet name contains spaces you should include your sheet name in single quotes like this:'My sheet'!A18
) - In
H32
this formula evaluates to=Feb!A18
(whereFeb
is your sheet name), inH33
to=Feb!A19
and so on.
这篇关于具有范围的动态INDIRECT公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!