Macro For Change data source_Pivot table

[Balajisx]

Hi All,

i have a source data in sheet 3 in the name "System data", the rows might increase or decrease in system data. I have created two sheets by using the pivot table for a summary. The system data is in range and I do not want to convert this into table. Now I need a macro to change the pivot table datasource based on the system data. Can some one help me on this.

 

[Chihiro]

You don't need macro for that.

Just set up dynamic named range using INDEX/COUNTA or OFFSET/COUNTA functions. Then use that named range as your pivot table source.

Upload sample workbook if you need more detailed help.

 

[Monty]

Hello Bala.

STEP 1: We need to enter the Offset function

=OFFSET


STEP 2: 1st Offset argument – Where do we want to to start our reference?

This is at the start of the data source (i.e. the top left hand corner)

Make sure to add an absolute reference to the 1st argument by pressing F4

=OFFSET($A$10


STEP 3: 2nd Offset argument – How many Rows down do we want to go?

As we want the referenced range to start at cell A10, we simply enter 0

=OFFSET($A$10, 0,


STEP 4: 3rd Offset argument – How many Columns to the right/left do we want to move?

We do not want to move to any Columns, so we simply enter 0

=OFFSET($A$10, 0,0,

STEP 5: 4th Offset argument – How High do we want our referenced data to be?

This is where the COUNTA function is used to count how many transactions we have in our data vertically and return the [height] of our data.

So as we are starting at cell A10, we are going to capture 13 cells downwards ( COUNTA(A10:A1048576) ) i.e. The range up to the last active vertical cell of our data.

NB: It is always a good idea to enter an ending range that is more than your last data cell or up till the last row number i.e. 1,048,756. That way as your new data get entered, we will be sure to capture it!

Make sure to add an absolute reference to the 4th argument by pressing F4

=OFFSET($A$10, 0,0,COUNTA($A$10:$A$1048576




STEP 6: 5th Offset argument – How Wide do we want our referenced data to be?

This is where the COUNTA function is used to count how many transactions we have in our data horizontally and return the [width] of our data.

So as we are starting at cell A10, we are going to capture 3 cells to the right( COUNTA($A$10:$XFC$10) ) i.e. The range up to the last active horizontal cell of our data.

NB: It is always a good idea to enter an ending range that is more than your last data cell or up till the last column number i.e. XFC. That way as your new data get entered, we will be sure to capture it!

Make sure to add an absolute reference to the 4th argument by pressing F4




=OFFSET($A$10, 0,0,COUNTA($A$10:$A$1048576,),COUNTA($A$10:$XFC$10))



STEP 7: Now that we have our OFFSET function, we need to create a Named Range for it so we can use it as our data source in Step 9.

To do this we need to select the whole OFFSET function and Copy it…



STEP 8: In the New Name dialogue box we need to enter a custom name with no spaces e.g. Data_Range in the Name: area and paste the OFFSET function from Step 7 in the Refers to: area and press OK


STEP 9: We need to insert a Pivot Table by going to Insert > Pivot Table…
…and in the Create PivotTable dialogue box we need to manually enter the Named Range from Step 8 i.e. Data_Range into the Table/Range area, choose a New/Existing Worksheet and press OK:

STEP 10: In the Pivot Table, you will need to put the SALES field in the VALUES area, the YEAR field in the COLUMNS area and the MONTH field in the ROW area:

STEP 11: In your data source you can start adding new data in the empty rows:

STEP 12: To reflect this new data in the Pivot Table, all you need to do is Right Click inside the Pivot Table and choose Refresh:

The new data will now be shown in the Pivot Table:


STEP 13: To check that our Named Range called Data_Range captures the new data entered, we need to click in the Pivot Table and go to PivotTable Tools >Analyze/Options > Change Data

So we can confirm that our Named Range Data_Range captures any new data entered in our data source!


Hope this helps.

 

[Balajisx]

Thank you So much for helping me on this. you have helped me to make this database as dynamic

 

[Balajisx]

Hello Bala.

STEP 1: We need to enter the Offset function

=OFFSET


STEP 2: 1st Offset argument – Where do we want to to start our reference?

This is at the start of the data source (i.e. the top left hand corner)

Make sure to add an absolute reference to the 1st argument by pressing F4

=OFFSET($A$10


STEP 3: 2nd Offset argument – How many Rows down do we want to go?

As we want the referenced range to start at cell A10, we simply enter 0

=OFFSET($A$10, 0,


STEP 4: 3rd Offset argument – How many Columns to the right/left do we want to move?

We do not want to move to any Columns, so we simply enter 0

=OFFSET($A$10, 0,0,

STEP 5: 4th Offset argument – How High do we want our referenced data to be?

This is where the COUNTA function is used to count how many transactions we have in our data vertically and return the [height] of our data.

So as we are starting at cell A10, we are going to capture 13 cells downwards ( COUNTA(A10:A1048576) ) i.e. The range up to the last active vertical cell of our data.

NB: It is always a good idea to enter an ending range that is more than your last data cell or up till the last row number i.e. 1,048,756. That way as your new data get entered, we will be sure to capture it!

Make sure to add an absolute reference to the 4th argument by pressing F4

=OFFSET($A$10, 0,0,COUNTA($A$10:$A$1048576




STEP 6: 5th Offset argument – How Wide do we want our referenced data to be?

This is where the COUNTA function is used to count how many transactions we have in our data horizontally and return the [width] of our data.

So as we are starting at cell A10, we are going to capture 3 cells to the right( COUNTA($A$10:$XFC$10) ) i.e. The range up to the last active horizontal cell of our data.

NB: It is always a good idea to enter an ending range that is more than your last data cell or up till the last column number i.e. XFC. That way as your new data get entered, we will be sure to capture it!

Make sure to add an absolute reference to the 4th argument by pressing F4




=OFFSET($A$10, 0,0,COUNTA($A$10:$A$1048576,),COUNTA($A$10:$XFC$10))



STEP 7: Now that we have our OFFSET function, we need to create a Named Range for it so we can use it as our data source in Step 9.

To do this we need to select the whole OFFSET function and Copy it…



STEP 8: In the New Name dialogue box we need to enter a custom name with no spaces e.g. Data_Range in the Name: area and paste the OFFSET function from Step 7 in the Refers to: area and press OK


STEP 9: We need to insert a Pivot Table by going to Insert > Pivot Table…
…and in the Create PivotTable dialogue box we need to manually enter the Named Range from Step 8 i.e. Data_Range into the Table/Range area, choose a New/Existing Worksheet and press OK:

STEP 10: In the Pivot Table, you will need to put the SALES field in the VALUES area, the YEAR field in the COLUMNS area and the MONTH field in the ROW area:

STEP 11: In your data source you can start adding new data in the empty rows:

STEP 12: To reflect this new data in the Pivot Table, all you need to do is Right Click inside the Pivot Table and choose Refresh:

The new data will now be shown in the Pivot Table:


STEP 13: To check that our Named Range called Data_Range captures the new data entered, we need to click in the Pivot Table and go to PivotTable Tools >Analyze/Options > Change Data

So we can confirm that our Named Range Data_Range captures any new data entered in our data source!


Hope this helps.

This really helps a lot .. Thank you for helping me on this. But a quick question. when I use this formula the black row is also being selected as a range in my pivot. Is there any way to avoid blank rows and columns selection from the data range ?