Search code examples
excelrangeformulanamed

Create 100+ Named ranges based on formula in cells

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

Solution

  • 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.