In Excel 365, currently I manually update multiple named ranges based on a dynamic header row, with dynamic values under each header. I hoping someone can help me with using VBA code to update the names and ranges all together based on the current data/table.
The goal is to have a drop down list with the relevant Units
values, without blanks or duplicates, for each Item.
The source data comes from an external workbook that is updated weekly. The data is in columns of Item
and Units
, but there is often multiple instances of each Item
and Units
and each week there may be new Items or some removed, same with the associated Units
values. The source is as per below example:
ITEM | UNITS |
---|---|
AA 120 | 100 |
AA 120 | 100 |
AA 120 | 150 |
AA 60 | 350 |
BB 200 | 36 |
BB 200 | 30 |
BB 200 | 30 |
SH 1001 | 55 |
SH 1001 | 55 |
The unique headers are pulled using the formula
=IFERROR(INDEX($B$4:$B$600, MATCH(0, COUNTIF($K$2:K2, $B$4:$B$600), 0)),"")
The following formula is in the row below the headers, to pull the unique values for each header
=UNIQUE(FILTER($C$4:$C$600,$B$4:$B$600=L2))
The resulting table is as per below example:
AA 120 | AA 60 | BB 200 | SH 1001 |
---|---|---|---|
100 | 350 | 36 | 55 |
150 | 30 |
Currently I have highlight all the headers and the row below and select Create from Selection
under Defined Names
and select Top row
. This creates named ranges that refer to the cell below each header. I then edit each range that the name refers to, by adding #
at the end, so it refers to the spilled data, as it is a dynamic range.
e.g. Update the named range reference for AA_120
from =SHEET1!$L$3
to =SHEET1!$L$3#
I do this one by one for 100+ named ranges. Any tips or help to make this more efficient?
oh and the formula I'm using for the source of the Data Validation is =INDIRECT(C7)
where C7
is the Item/named range. This all works well...
TIA
EDIT:
I worked out some VBA code to add the #
at the end of the referred range. The range for all Named Ranges starts on row 3, so this worked to update all relevant ranges at once...
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
Would still really appreciate it if someone could improve on my VBA, to update all the ranges from when the source data is updated. Currently I delete all named ranges in the Name Manager
, then select the 2 rows with all the updated Item
and Units
data. Then Create from Selection
under Defined Names
and select Top row
. Then I run my macro. But if the Macro could do all above, that would be great. The difficulty I see if that the amount of Items
and corresponding Units
vary as it is dynamic. Plus there are 3 data sources on 3 different sheets, all rows 2&3 but columns start at L, N & T for the 3 sheets.
What I have worked out will work for me, but if anyone can improve on my code, I'd be extremely grateful!
Just in case anyone is wanting to achieve the same thing.
I used a filter to create the column of items, then
TRANSPOSE(UNIQUE(FILTER(G:G,B:B=T7
for the rows of unit options next to each item.For the VBA I added the following to a module
Then in the data validation I entered
=toXDAV
in the source field.