Create 100+ Named ranges based on formula in cells

Question

been looking for quite a while now, due lack of distinctive terminology couldn't find any solution, so maybe the experts out here can help.

So I got this table of 300+ collumns that are populated like this

row 1 Header/Name.

row 2 Range formula ment to be in the "Refers to" input area when a "New Name" for a range is created.

row 3/22 The information used in the range formula.

To use the range formula's in a data validation on another sheet I need to Name these ranges. If I manually enter a "New Name" I can copy the range formula from row 2 into the "refers to" input area, only with 300 columns that would be a long day of labor. That's when I found out about the CRTL+SHIFT+F3 combo which makes it possible to create a lot of named ranges at once based on a header/name and selection. Unfortunately this uses the location of selection as the source and in my case it should be the formula inside the locations's cell which would have to be the source...

So is there a way to use the "Create Names From Selection" tool that uses a formula inside a cell as the source instead of the location?

here's an image to help describe the problem screenshot to show problem

Are you sure that you need 300 different named ranges for the validation? More information about the construction of the second sheet could be useful. Maybe you could solve the problem with just a single named range. — BrakNicku
300 could be a bit less, but has to be above 200 lines. In this document I'm creating an order form. We have around 400 products in different color combinations. — user3155347
I've constructed the sheet that when a product name or code is entered it autofills the corresponding name/code, price, MOQ, and a soon to be drop down list data validation of the colors available. All the cells are dependent on the name or code entered. So when for example "product 1" is entered in row, it searches corresponding colors in another sheet, than turns those into a dynamic list which should populate the data validation. When a different product is entered in row 1 it should return those corresponding colors. — user3155347
The data validation in row1 is always based on dynamic range 1, row2 dynamic range2, etc... Hope this clears things up. Thank you for your reply! — user3155347

BrakNicku BrakNicku · Accepted Answer · 2014-08-27T08:08:46

You should be able to solve this problem with 1 named range for every validation (plus 2 additional to make the formula less complicated).

The first named range (all_headers) should be defined as:

=OFFSET('C'!$A$1,0,0,1,COUNTA('C'!$1:$1))

It returns a range with the headers (product names or codes) from the C sheet. We assume that the first column is A and there are no empty columns between them.

Next we need to choose the right column. Here it gets a little tricky. In the row where you want to validate colors, you need to have exactly the same product name or code that is used in the C sheet headers. If this information is in cell A2, you should:

select the cell in the same row and in the column where the color validation is supposed to be (for example B2)
define new named range col_header with the following formula:
```
=INDEX(all_headers,1,MATCH(A2,all_headers,0))
```

The above dynamic named range is relative, that's why selecting the proper cell before defining it is very important.

The last step is to define named range val_list with reference to the list of colors from the chosen column:

=OFFSET(col_header,2,0,COUNTA(OFFSET(col_header,2,0,50,1)),1)

You mentioned that the second row does not contain data, that's why there is 2 parameter twice in the formula. If you remove it, use 1 instead. 50 is the maximum number of colors - you can adjust it.

Now you can use val_list for validation in any cell. It should give you the right list if the cell on the left contains a valid product name/code from the C sheet header.

Create 100+ Named ranges based on formula in cells

1 Answers