The COUNTA() function counts how many cells in a selection are not empty. This is the function that allows you to create a variable sized range. This allows you to reference a range that is a certain distance or size from a reference cell, which, as used above, is the first cell in the list. The first function is the OFFSET() function. In case you were curious, I will now explain the two functions used to create this dynamic named range. This is all you need to do to create a dynamically updating named range in Excel! Formulas Explained You can see that the formula was automatically updated to include the new number in the calculations. Going back to the worksheet, now we can use the name like this: Go to the Formulas tab and click Name Manager:Ĭhange the Name, and Refers to section (the others are optional):įor the Refers to section I just copy/pasted the formula that we created in the worksheet into there to avoid any potential errors. Now that we have the formula figured out, let's put it in a named range and test it. So, if it was in column D, you would use this: $D:$D Then just change $A:$A to the column where your list is located. So, if the first cell was C1, you would use this: $C$1 There are a few things that you need to change to make this work for you.įirst, where it says $A$2 in the formula above, you need to change that to the cell reference for the first cell in the list. You can see how putting this in the worksheet allows you to see where everything is pointing and to better troubleshoot any potential errors.
0 kommentar(er)
