更新由Dynamic Source创建的多个命名范围使用溢出

Question

在Excel 365中，目前，我根据动态标头行手动更新命名范围，每个标头下方具有动态值。我希望有人可以帮助我使用VBA代码根据当前数据/表更新名称和范围。

目的是为每个项目提供一个带有相关单位值的下拉列表值。

源数据来自每周更新的外部工作簿。数据位于item和单位的列中，但是每个item and 单位通常都有多个实例每周可能会有新项目或删除一些项目，与关联的单位值相同。来源如下所示：

项目	单位
AA 120	100
AA 120	100
AA 120	150
AA 60	350
BB 200	36
BB 200 30 BB 200	30
BB 200	30
SH 1001	55
SH 1001	55

拉动独特的标头，

=IFERROR(INDEX($B$4:$B$600, MATCH(0, COUNTIF($K$2:K2, $B$4:$B$600), 0)),"")

使用以下公式在以下公式中 标题下方的行，要拉出每个标头的唯一值，

=UNIQUE(FILTER($C$4:$C$600,$B$4:$B$600=L2))

结果表如下所示：

AA 120	AA 60	BB 200	SH 1001 1001
1001	1001 100 330	36	55
150		30

我目前有所有标题，然后选择下面的行，然后选择从选择在定义的名称下创建，然后选择top Row。这会创建名为范围的范围，指的是每个标头下方的单元格。然后，我通过在末尾添加＃来编辑名称所指的每个范围，因此它指的是溢出的数据，因为它是一个动态范围。

例如，从aa_120从= Sheet1！$ 3 = Sheet1！$ L $ 3＃

我一一做这件事对于100多个命名范围。有任何技巧还是有助于提高这一效率？

哦，我用于数据验证源的公式为=间接（C7）其中c7是项目/命名范围。这一切都很好...

TIA

编辑： 我已经制定了一些VBA代码，以在引用范围的末尾添加＃。所有命名范围的范围都从第3行开始，因此，

Sub RangeRename()

Dim n As Name
    For Each n In Names
        If Right(n.RefersTo, 1) = "3" Then n.RefersTo = n.RefersTo & "#"
    Next n
End Sub

如果某人可以改进我的VBA，以更新源数据更新何时更新范围，则可以一次更新所有相关范围。当前，我在name Manager中删除所有命名范围，然后选择所有已更新的item和单位数据的2行。然后，从定义的名称下从选择创建，然后选择top Row。然后我运行宏。但是，如果宏可以在上面做所有事情，那就太好了。我很难看到项目和对应单位的数量是否变化，因为它是动态的。另外，有3个不同的床单上有3个数据源，所有行2＆amp; 3，但列从l，n＆amp;开始。 t对于3张。

我所解决的问题将对我有用，但是如果有人可以改进我的代码，我将非常感激！

Answer 1

也许我完全不明白您要做什么...但是您无法使用简单的枢轴表获得想要的信息？我想您可以使用一些高级技巧来获取您想要的格式...

但是使用当前的Excel公式，我想我通过这样做可以得到您想要的东西：

1. 我将您的源数据粘贴到工作表中（ A1：b10）并将其纳入
   表（控制+T），选择“我的表具有标头”选项。 （我认为最好使用桌子，但是如果您不愿意，可以在下面的公式进行调整。）

   

2. 我在同一或另一个纸上选择了另一个单元格 - e2-和输入= transpose（unique（table1 [item]））。这给出了水平的独特物品溢出。

3. 在（IE，e3）下方的单元格中，我输入= ifError（sort（sorter（unique（filter（table1 [[units]）：[[units]]：[table1]，table1 [[item]]：[item]] = d15）），“”）。 （如果您想水平拖动事物，我们需要重复此类包裹的列名，我们需要。）如果您不需要/想对我们要创建的列中进行分类，则可以离开该单元公式的一部分。

4. 我从左侧水平拖动该单元格，现在超越了您现在需要的（如果您的列表中有任何新项目）。公式中的ifError将使表在当前没有数据显示的最正确的列上保持清洁。如果/当您在源数据表中添加更多行时，溢出的公式会进行相应调整，因此我认为您担心动态源会得到照顾。

结果如下所示。也许有一种方法可以设计一个单一公式，当将E3放入E3时会溢出到2D阵列中，因此您甚至不需要拖动东西...但是我会把它留给其他人。

Answer 2

以防万一任何人都想实现同一件事。

我使用过滤器创建项目列，然后transpose（unique（filter（g：g，b：b = t7，对于每个项目旁边的单位选项的行。

对于VBA，我添加了然后

Function xDAV(c As Range) As Range
Dim r As Range
 
With Sheets("LookUpList").Columns("T:T") 'Items
    
    Set r = .Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    'Units are in col U
    'col U is 1 column to the right from col T
    n = 1 'change to suit
    
    If Not r Is Nothing Then
        Set f = r.Offset(, n)
            If f.Offset(, 1) = "" Then
                Set xDAV = f 'single cell or blank cell
            Else
                rc = f.End(xlToRight).Column 'get last column with data
                Set xDAV = f.Resize(, rc + 1 - f.Column)
            End If
    End If

End With

End Function

在数据验证中，我输入= toxdav在源字段中。