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

问题描述

我在 Excel 中有一个表格，其列标题对应于我工作簿中其他位置的动态命名范围的一部分.例如，我有这些列标题:10"、20"等，以及这些动态命名范围:ExampleRange10"、ExampleRange2"等.我想通过连接输入引用 ExampleRange10 的 VLookup 公式字符串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"定义为静态范围时，它可以正常工作.

解决方案

据我进一步研究后发现，Excel 的 INDIRECT 函数根本不适用于动态范围.可能有一种巧妙的方法可以绕过使用 INDIRECT 并坚持使用非 VBA Excel 世界，但我不知道这种方法.相反，我最终创建了一个用户定义的函数，非常类似于 此处.我改变了我的主要公式来读取 =VLOOKUP(B2,DINDIRECT("ExampleRange"&C1),2,FALSE)，其中 DINDIRECT 是 VBA 函数的名称我创建的.

此替代方案的唯一缺点(可能是也可能不是缺点，取决于您如何看待它)是工作簿必须另存为启用宏的工作簿，并且自定义函数的使用不是很自我-记录并需要对其他用户进行一些解释.不过，考虑到所有因素，这对我来说是一个可以接受的解决方案.

对于链接厌恶，这是代码:

公共函数 DINDIRECT(sName As String) As RangeDim nName As Name出错时继续下一步设置 nName = ActiveWorkbook.Names(sName)设置 nName = ActiveSheet.Names(sName)出错时转到 0如果不是 nName 什么都不是设置 DINDIRECT = nName.RefersToRange别的DINDIRECT = CVERr(xlErrName)结束函数

注意:虽然此解决方案有效，但我不会接受我的答案，因为我不想阻止其他人发布更好的解决方案.另外，我是该网站的新手，如果我通过回答我自己的问题而破坏了任何礼仪规范，那么抱歉……我只是想分享我使用的确切解决方案，以防其他人发现它有用.

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.

解决方案

As best I could tell after doing further research, Excel's INDIRECT function simply doesn't work with dynamic ranges. There might be a clever way to get around using INDIRECT and sticking to the non-VBA Excel world, but I'm unaware of such a way. Instead, I ended up creating a user-defined function very similar to the one described here. I altered my main formula to read =VLOOKUP(B2,DINDIRECT("ExampleRange"&C1),2,FALSE), where DINDIRECT is the name of the VBA function I created.

The only downsides (which may or may not be downsides depending on how you look at it) to this alternative is that the workbook must be saved as a macro-enabled workbook and the use of a custom function isn't very self-documenting and requires a little explanation to other users. All things considered, though, this was an acceptable solution for me.

For the link-averse, here's the code:

Public Function DINDIRECT(sName As String) As Range
     Dim nName As Name

     On Error Resume Next
          Set nName = ActiveWorkbook.Names(sName)
          Set nName = ActiveSheet.Names(sName)
     On Error GoTo 0

     If Not nName Is Nothing Then
          Set DINDIRECT = nName.RefersToRange
     Else
          DINDIRECT = CVErr(xlErrName)
End Function

Note: Although this solution worked, I'm not going to accept my answer because I don't want to discourage others from posting better solutions. Also, I'm new to the site, so sorry if I'm breaking any etiquette codes by answering my own question...I just thought I'd share the exact solution that I used in case others find it useful.

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