在Excel公式中引用动态命名范围 [英] Referencing Dynamic Named Range in Excel Formula

问题描述

我在Excel中有一个表，其列标题对应于我的工作簿中其他地方的动态命名范围的一部分。例如，我有这些列标题：10，20等，这些动态命名范围：ExampleRange10，ExampleRange2等。我想输入一个VLookup公式，引用ExampleRange10连接字符串ExampleRange和列标题10。这将允许我简单地将公式扩展到表中的所有列，而不是在每列的公式中手动键入ExampleRange10，ExampleRange20等。

我知道INDIRECT函数，并且以前使用命名范围成功使用它，但它似乎并没有使用在这种情况下，动态命名范围。我猜这是一个细微差别，它与Excel定义了动态命名范围有关（它们没有显示在公式栏左侧的命名范围下拉列表中，并且在VBA中有一些有趣的属性， 例如）。有没有办法可以使用INDIRECT公式与动态命名范围，还是有另一种方法可以解决这个问题？

编辑：这是使用的确切公式。

这是主要公式： = VLOOKUP（B2，INDIRECT（ExampleRange& C1），2，FALSE ）其中C1包含10，我的动态命名范围称为ExampleRange10的公式是： = OFFSET（Sheet1！$ F $ 2,0,0，COUNTA（Sheet1 ！$ F $ 2：$ F $ 25），2）。主要公式返回#REF！，但是当我删除动态命名范围公式并将ExampleRange10定义为静态范围时，它可以正常工作。

解决方案

我最近碰到这个确切的砖墙，你已经猜到的答案只是你不能用INDIRECT引用动态命名范围。

然而，您可以使用动态范围公式本身作为INDIRECT的参数，但这并不适用于您想要做的事情。有一点PITA，因为它是一种非常有用的功能。

I have a table in Excel with column headings that correspond to part of a dynamic named range elsewhere in my workbook. For example, I have these column headings: "10", "20", etc., and these dynamic named ranges: "ExampleRange10", "ExampleRange2", etc. I'd like to enter a VLookup formula that references ExampleRange10 by concatenating the string "ExampleRange" and the column heading "10". This would allow me to simply extend the formula across all columns in the table, instead of manually typing "ExampleRange10", "ExampleRange20", etc. in each column's formula.

I'm aware of the INDIRECT function, and have used it successfully in the past with named ranges, but it doesn't seem to be working with the dynamic named range in this case. I'm guessing this is a nuance that has something to do with how dynamic named ranges are defined by Excel (they don't show up in the named range dropdown to the left of the formula bar, and they have some interesting properties in VBA, for example). Is there a way I can use the INDIRECT formula in conjunction with a dynamic named range, or is there another way that I can go about solving this problem?

Edit: Here are the exact formulas used.
This is the main formula: =VLOOKUP(B2,INDIRECT("ExampleRange"&C1),2,FALSE) where C1 contains "10" and the formula for my dynamic named range called "ExampleRange10" is: =OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$25),2). The main formula returns "#REF!", but it works correctly when I remove the dynamic named range formula and simply define "ExampleRange10" as a static range.

解决方案

I hit this exact brick wall recently and the answer as you have already guessed is simply that you can't reference dynamic named ranges with INDIRECT.

You can however use the dynamic range formula itself as INDIRECT's argument, but this is no use for what you want to do. Somewhat of a PITA since it's the kind of functionality that would be very useful.

这篇关于在Excel公式中引用动态命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！