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?Â
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.
