pivot table advice

    I just took over a new position in my business and have some questions about pivot tables. The job position already has many pivot tables created and have been used for months. I have about 3 questions:
    1) all pivot tables reference a text sheet about 4500 rows and 28 columns. I need to change this data weekly. when I change it I can do is several ways but hte best is to delete and cut and paste to replace data. The problem is the new data will have 4700 rows but column will always stay at 28. when I do this will I ruin the pivot tables or will I have to go to each pivot table and change the ref cells individually.
    2) can this whole process be automated?
    3) The sheet that I replace since the parameters will vary a little more or a little less every time, so would it be prudent to always reference a round figure like 4800 and just deselect blanks. I think this will work.
    4) lastly, when I change the whole sheet will this corrupt the pivot tables. I've tested and didn't noticed anything wrong but not sure if it was a proper way of doing business.


    Thanks in advance appreciate your patience


    Billy

    Re: pivot table advice


    Well, the first thing I would do is create a dynamically named range to house the data. Then if you add rows or delete rows, the range will still be correct. Then adjust the existing pivot tables to reference the range. You would then not need to use empty rows in the pivot tables.

    Re: pivot table advice


    when you mention a dynamic named range would this be like a$1:z$4800. If I did this how would it not use blank rows? The reason I ask is because I first attempted this and blanks showed up so deselected and it seemed to work but not sure if my thought process was correct.


    Thanks again


    BillyJ

    Re: pivot table advice


    A dynamic named range would work like this:


    Say your data is in a1:ab4600 on Sheet1. Click Insert|Name|Define, name the range something, like MyData and in the refers to box at the bottom of the input form, type this:


    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),28)


    Then reference MyData in your pivot tables and if you add or delete rows, you just need to refresh the pivot table.

    Re: pivot table advice


    Thanks didn't know it was this easy. will this still work if I replace the sheet? I will usually cut and paste the new document. I understand the refresh of pivot tables. The reason I ask is because the deletion and addition is done by another department and I use the end product. I appreciate your help I'm doing well now thanks. Have a great holiday.


    BillyJ

    Re: pivot table advice


    Hi BillyJ,


    Seti's advice on Dynamic Ranges is spot on. As long as the Column that you have chosen (Column A in Seti's example) does not have any blanks between the data. Use a column that will always have data in it as your count column.


    You can replace all the data in the sheet, but the Pivot Table is referenced to a Range within a sheet, so if you delete the sheet, then the Pivot Table will lose its reference. A quick way to delete all the data and leave the headings is to put a CommandButton on your sheet (I have assumed sheet1, change if necessary) and then, in a module, place the following code and attach it to the CommandButton.


    Code
    Sub Clear()
    Sheet1.UsedRange.Offset(1, 0).ClearContents
End Sub


    Regards,


    Bill

    Re: pivot table advice


    Thanks for the help little confusing. Didn't undynamic ranges and I'm studying really fast. You nailed when you stated leaving the headers I beleive thats what was making this difficult. I appreciate the help and I'm starting to think Pivot Tables can be your friend if you know what your doing.


    Billyj

    Re: pivot table advice


    can anybody explain what each segment of this dynamic range means. I'm not sure what 0,0 means and the "countA(sheet1!$A:$A),28. I do know it works but trying to understand so I can use on my own a little easier.


    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),28


    Thanks
    Billyj

    Re: pivot table advice


    The first zero means the offset for the row, the second means the offset for the column. I.e. the range returned by the formula should be in the same place as the first argument. The thing you want to change is the size of the range returned, so these are controlled by the final two arguments.


    After a great deal of experimentation with dynamic ranges, I tend to avoid them, as it is not possible to make a fully-reliable dynamic range formula.


    If I were you, I would try the following (some members of this board will hate this suggestion, but it does work):


    1) Make the PivotTable refer to entire columns.
    2) Amend the PivotTable to hide blanks (the unused rows).
    3) Don't save data with the PivotTable layout (by default, PivotTables save a full copy of all the data they are using, which will make the file large if you are using enitire columns). On the PivotTable wizard, click options and remove the check from the "Save data with PivotTable layout" option.

    Re: pivot table advice


    Thanks pivot tables are something I inherited via an existing project. Thanks I'm learning and finding pivot tables can be really helpful. THe issue I had was I have about 25 separate pivot tables off data that changes daily/weekly/monthly. THe issue I was struggling with is one week I may have 4600 rows of info and the next I would have 4700 and I didn't want to change the ref area every time. Dynamic range is what I thought would work but how would I reference the column and ignore blanks? thanks

    Re: pivot table advice


    Select a PivotTable and call up the wizard (Data -> PivotTable and PivotChart Report).


    Click the back button on the wizard until you get to the stage where it asks you to select the ranges in question.


    At this stage, don't select a range of cells - select the grey column letters at the top of the screen. This will appear in the wizard as something like


    'Name of Sheet'!$A:$N


    This means it will use the whole of columns A through to N for the PivotTable.


    The PivotTable, once updated for these changes, will probably have a "(blank)" item at the bottom. Simply click on the dropdown arrow attached to the header for the column containing the (blank) item. Depending on your version of Excel, you then need to either highlight the (blank) item to exclude it (Excel 97) or remove the check from beside the (blank) item to exclude it.

    Re: pivot table advice


    You may also want to have the pivot tables reference each other in the wizard, instead of always using the dynamic range. This will cut down on the size of your excel file.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login