Creation of simple dynamic table like pivot table, when data are changed

Creation of simple dynamic table like pivot table, when data are changed

Hi Debbie,

You may have a named range that must be extended to include new information. This article describes a method to create a dynamic defined name.

1. In a new worksheet, enter the following data.

2. Click the Formulas tab.

3. In the Defined Names group, click Name Manager.

4. Click New.

5. In the Name box, type Date.

6. In the Refers to box, type the following text, and then click OK:

=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

7. Click New.

8. In the Name box, type Sales.

9. In the Refers to box, type the following text, and then click OK:

=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

10. Click Close.

11. Clear cell B2, and then type the following formula:

=RAND()*0+10

Note In this formula, COUNT is used for a column of numbers. COUNTA is used for a column of text values.

This formula uses the volatile RAND function. This formula automatically updates the OFFSET formula that is used in the defined name "Sales" when you enter new data in column B. The value 10 is used in this formula because 10 is the original value of cell B2.