Browsing all articles tagged with Named Ranges

How to Create Dynamic Named Ranges

Author Zaheer    Category Guides     Tags ,

Dynamic named ranges are one of the strongest aspects of Excel. It is commonly used in listing the controls, PivotTables, and charts. First thin is to formulate a list in column of A of your worksheet.

Before anything else, there are still problems that could surface such as the updating or redefining of the named ranges every time there is an update on the records on the table.

Before creating dynamic named ranges, you must first settle with the OFFSET functions. This is the cell where you will be anchoring first and everything will eventually in relation to this cell. Most of the time, it is advised to make the header of the field be first on your record.

Next is to decide on the number of rows which is to be moved origination from the anchor address to start the range. In creating dynamic named ranges, it is advisable to start at 0 (zero) on the header to automatically begin at 1 (one). After, designate or decide on the number of columns which will be moved from the anchor address to start with the range.

After deciding on the number of columns, the number of extended rows where the range will be extended must be decided. Instead of hardcoding a value, a solution would be the substitution of CAOUNTA function for the selected primary or first field. In this manner, if you have decided or planning to add or delete items on certain fields the range will automatically grow or shrink in accordance to the changes. Also, you may need to deduct 1 from the result of the CAOUNTA in relation to the elimination of a row in the header. It is also required that you do not have any surplus data on the table range you are planning to evaluate.

After the above-mentioned, it is now safe to decide on the number of columns you will be using in your dynamic range. Take heed that the dynamic named ranges is dependent on a workbook’s capacity to calculate.

Also, another method to create dynamic named ranges is to open a new worksheet and enter your data. Click on the Formulas tab and in the Defined Names group click on the Name Manager. After, choose and click on New and in the New box type the Date. Click on the Refers to dialogue box and enter the formula.

After inputting the formula, click on New and type on the Name box the label. Again, click on the Refers to box ad input the formula after which hit Close. The formula being referred to uses the volatile RAND function.

Also, you can create names in Excel that could refer to the cell itself, the range of the cells, the constant values of the cell, of the formula itself. After giving names to the cell in the Excel, you can now use those names in creating or using formulas in replacement of certain values and other references. These names can also be used for navigation purposes to easily select the named cells and its range. To easily create an Excel with name ranged input in the Excel Name box the name. After inputting, select the specific cell pr cells to be named click the Excel name box, input a one-word name on the list and hit enter.

After making the Excel names which will eventually refer to the range you can now select an Excel name in the Name Box dropdown list. Also, you can use the Excel names for formulas.

Likewise, you can create a dynamic named range this is defined as when an item is added the range will automatically expand. To create a dynamic named ranged select and click on Insert then select Name and then click on Define. After selecting Define type on the name for the chosen range. In the Refers To dialogue box, enter an Offset formula that could define the range basing on the number of values in the column you have selected and click on Ok.

There are also available online tutorials for Excel Dynamic Ranged Named in using the range in formula. Also, there are tutorials in the sole creation of the Dynamic Ranged Named.

VLOOKUP Excel Formula

A Brief Overview of VLOOKUP

The truth is that, VLOOKUP is difficult yet useful for several applications. Microsoft Excel is not just a simple spreadsheet since it contains numerous built-in functions. VLOOKUP is one of the most useful functions of MS Excel. It searches for a data contained in a lookup table. You can utilize it to search the postal code, a tax value, the cost of an item or even some other often used information.

The lookup value is located in the left-most column of the lookup table. The lookup table can contain several columns, but the lookup value should always be contained in the first column of the table.

The syntax for the VLOOKUP function is:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Lookup_value – This is the cell that contains the value that is searched for. VLOOKUP works by comparing the value of this cell with the values contained in the lookup table.

Table_array – This is the array of columns and rows which is covered by the lookup table.

Col_index_num – This is the number of the column where the values being searched for can be found.

Range_lookup – This argument is optional. When omitted, VLOOKUP searches for the value which is closest to the lookup_value. If you require an exact value, you need to assign a FALSE value to the range_lookup.

The VLOOKUP function is a very helpful MS Excel function. It permits you to take data from another worksheet that is connected to the data you are entering. It helps in speeding up the entry of data while reducing errors. But this function is useful only for a fixed master list. How about if the worksheet you are working with is increasing or decreasing? For example, you have a list of products. Your current worksheet includes all the products that you stock so that in another worksheet, when you type in the product ID, you can copy the product price and name automatically.

If these are the only products you will every store, or if the catalogue only increases infrequently, then that’s alright. You can do some editing of the formulas and worksheets when needed. However, what if you add products frequently? Luckily, there is an excellent way that you can utilize the VLOOKUP function but with a dynamic range. How you will do that? Read further for more information.

Using Named Ranges in VLOOKUP

Normally, when you utilize the VLOOKUP function, you identify a precise range of data, like =VLOOKUP (A3, A3:C11, 4) where the specified range is from A3 to C11. Then if you are going to add a new product, you are required to make it C12 and so on, every time the list of products is altered.

The first thing to do to simplify this process is to use a “named range”. So, you could use the name “product” instead of using A3:C11. The following are the steps to create a named range.

Select the range of cells that you want to name and from the Name Manager, choose and click Define Name. Then the New Name dialog box will appear.

In the dialog box, enter the name you want to use but remember to check that the range is proper.

After doing these things, your VLOOKUP formula will transform in such a way that it looks like this, VLOOKUP(A3,Product,4). You can simply change the range name in the Name Manager.

How to Make Dynamic Named Ranges

Among the features of Microsoft Excel, named ranges are one of the most powerful, particularly when utilized as the source range for charts, PivotTables or list of controls. However, a problem occurs when the contents of a list alter frequently. It would be difficult to redefine the name of the range every time a record is removed or added to the list. The best answer is to make a range that adjusts automatically based on the number of records contained in the list.

Most probably, you don’t want to go to the Name Manager repeatedly. Maybe, you want the list to correct itself automatically and just go on working. Fortunately, there is a solution for that. You can do that by using the OFFSET function. The OFFSET function requests for a row count, but it would work better by using the COUNTA function. The count will increase every time you add new products, which gives you the dynamic range.