When working with a continuously changing data set, we may need to make our named range dynamic so that it automatically increases to accommodate newly combined entries or records to exclude eliminated data. In this article, we will see how to do a dynamic named range based on cell value in Excel. Show
Download Practice WorkbookYou can download the workbook used for the demonstration from the link below. 5 Easy Ways to Create Dynamic Named Range Based On Cell Value in ExcelNow we will go through a total of five different methods to create a dynamic named range based on cell value in Excel. The basic idea to name a range is the same- we will use the Name Manager from the Formula tab. In general, we can select a range from a spreadsheet and name it after that through this feature. Or we can select a formula to do that. As the former would result in a fixed range, it would not serve our purpose. So we will resort to using formulas to create dynamic named ranges in Excel. These methods differ only in the formulas we are using. For each method, we are going to use the same dataset.  1. Create Dynamic Named Range Based On Cell Value with Excel OFFSET FunctionIn the first method, we are going to be using the OFFSET function only for the formula. This function takes three primary arguments- a reference, and the number of rows and columns it will deviate. It can also take the number of rows and columns it will return as its 4th and 5th arguments, although they are not mandatory. It then returns a value or an array depending on the number of values and their type. So we can easily use this function to create a dynamic named range based on cell value in Excel. Follow these steps for a detailed guide. Steps:
This is one way we can name a dynamic range in Excel. Read More: OFFSET Function to Create & Use Dynamic Range in Excel 2. Combine OFFSET and MATCH Functions to Create Dynamic Named RangeAnother way, you can achieve a similar result is by a combination of the OFFSET and MATCH functions. The OFFSET function gives us an output depending on the set of variables and their nature. Meanwhile, the MATCH function returns the match of a value in an array. So we can create a formula out of those which can identify a set of numbers that we can use as the range. Keep in mind, this number identifying and isolation is based on a cell value and so we are creating a dynamic named range in Excel this way. Follow these steps to see how we use the formula and apply it. Steps:
Thus, we have created a dynamic named range based on cell value in Excel. Read More: Excel VBA: Dynamic Range Based on Cell Value (3 Methods) Similar Readings
3. Generate Named Range for Text Values by Joining OFFSET and MATCH FunctionsWe can also use the same OFFSET and MATCH functions to identify text values and thus create a dynamic named range. Usage of this formula in Excel can also be included in the dynamic named range based on cell value criteria. Follow these steps to see how we can achieve that. Steps:
This is how we can create a dynamic named range based on text cell value in Excel. Read More: Create Dynamic Named Range with VBA in Excel (Step-by-Step Guideline) 4. Apply FILTER Function to Create One Dimensional Dynamic Named Range in ExcelAnother way we can create a dynamic range is to use the FILTER function in the reference box. This function takes a range or an array as the first argument and filters values from them depending on the second argument. Follow these steps to see how we can use this to our advantage to create the dynamic named range in Excel. Steps:
It is another way to create a dynamic named range that can also be based on cell value in Excel. Read More: How to Use Dynamic Range VBA in Excel (11 Ways) 5. Create Two Dimensional Dynamic Named Range in ExcelAll of the ranges we have created up until now are single-dimensional arrays (one-column values). We can create such references using the INDIRECT, COUNTA, and ROW functions. The INDIRECT function provides us with a reference from a string it takes as an argument. Meanwhile, the COUNTA function returns the non-empty cells from a range. Whereas, the ROW function returns the row number from the reference argument. Here is how we can create a two-dimensional dynamic named range in Excel with the functions. Steps:
As you can see, the output has automatically been updated. So we can say we have created a dynamic named range in Excel as a result. Read More: Create Dynamic Sum Range Based on Cell Value in Excel (4 Ways) Insert INDEX Function in Excel for Creating Named Range Based On Cell ValueWe can also use the INDEX function to use as a reference. But unlike the previous ones, we will not get a dynamic output range as the result. Although we will get the output range depending on the formula. This function takes the intersection or rows and col it counts as a specific column (which it takes as the first argument). To see how we can achieve that, you need to follow the steps below. Steps:
As a result, we can create and use the named range in Excel. Use Name Cell in Excel to Generate Named RangeThe simplest way to create a named range is to use the Name Cell feature of Excel. You can find it on the left side of the formula box on the spreadsheet. Now, follow the steps below to perform the task. Steps:
Define Names from Selected RangeThere is yet another feature to define a name for a range. For that purpose, learn the following process. Steps:
We can also use this method to define names from a selected range in Excel. ConclusionSo these were all the methods to create named ranges, both dynamic and static based on the cell value in Excel. Hopefully, you have grasped the idea of using these methods and can use them accordingly. I hope you found this guide helpful and informative. If you have any questions or suggestions let us know in the comments below. For more guides like this visit Exceldemy.com. Further Readings
How do I get Excel to automatically define names?Define names from a selected range
Select Formulas > Create from Selection. In the Create Names from Selection dialog box, designate the location that contains the labels by selecting the Top row,Left column, Bottom row, or Right column check box. Select OK.
How do you create a range name based on values in Excel?Create a named range from selected cells in a worksheet. Select the range you want to name, including the row or column labels.. Click Formulas > Create from Selection.. In the Create Names from Selection dialog box, select the checkbox (es) depending on the location of your row/column header. ... . Click OK.. How do I create a dynamic name in Excel?How to create a dynamic named range in Excel. On the Formula tab, in the Defined Names group, click Define Name. Or, press Ctrl + F3 to open the Excel Name Manger, and click the New… ... . Either way, the New Name dialogue box will open, where you specify the following details: ... . Click OK.. |
How do I create a defined name in Excel based on values?
Pos Terkait
Copyright © 2024 ketiadaan Inc.
