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

Asked By 0 points N/A Posted on -

I want a simple dynamic table in spreadsheet which looksÂ similar to pivot table. I would like to know whether table can be created if the data are changed dynamically, when filtering has done, just like a pivot table which has dropdown filter. Can anyone help me out?Â

SHARE
Answered By 0 points N/A #108589

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

 Â A B 1 Month Sales 2 Jan 10 3 Feb 20 4 Mar 30

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.