How to stop formula's changing?

[4lowie]

Hi all

On my spreadsheet I have several formula's that reference data on another sheet that keep changing...
I run a macro on the sheet that has the data to remove some blank lines so a pivot table works as it should and hides a few columns, nothing special.

The trouble is that once I go back to the results page which has a summary of many text strings the "countif" formula keeps reducing.

=COUNTIF('Scheduled Work'!$R$4:$R$173,"Parts - BOM incorrect")

Each time I do a format and update the second R value decreases. Wouldn't be so bad if it went up! (There are several of these countif functions)

How do I lock these values so they never change?

Also if I protect a sheet with a pivot table in it will it still update ok?

Thanks for your help.
Dave

[FDibbins]

Try changing to full-column reference...
=COUNTIF('Scheduled Work'!$R:$R,"Parts - BOM incorrect")

[newdoverman]

If the range of data for the Pivot Table is in a table then as rows are added or subtracted from that table will cause the references that reference the columns of the table will also change to reflect the number of rows in the table. The references can either be in Table Nomenclature or actual cell references.

[4lowie]

Thanks guys
I've changed the countif reference to select the complete column but I still have an issue with the pivot table.

'Scheduled Work'!$A$3:$R$169
The data that I need my pivot table to reference has to start at row 3 down, can I have an open part of the reference? Eg 'Scheduled Work'!$A$3:$R

Cheers

[newdoverman]

To follow up on my last message, change the data source to be the table if the source data is in a table. The Pivot Table will then be in sync with the source table when the Pivot table is "Refreshed".

The reference that you propose is not acceptable to Excel.

As to why the summary of text ranges changes is not likely to become apparent to us without seeing the workbook.

Can you post your workbook with sensitive data removed?

[4lowie]

I've attached the workbook I've been working on...

The main issue now is the pivot table selection range changes.

We just hit the clear macro to clear the existing data then copy and paste from another sheet. We then have to format this, this removes a few lines of odd data (date headings) and hides some columns.

Trouble is the ranges of the reference table and cells change.

I also have make it bullet prof at some stage so our non excel people can use it without destroying it. Everyone thinks they're an expert and will try to mod it.....
So if you have any suggestions on how to protect it I'd be grateful.

Many thanks.

Working copy1.xlsm

[4lowie]

The other thing I've just found is the filter on the pivot table changes as well.

I need to lock the filter to:

"Not Started Yet" and "Not Achieved"

Can we fix this to please?

Cheers

[newdoverman]

I have converted the Pivot table source data to a table then assigned the Pivot Table data source to be Table1. In addition to that I changed the formulae on the Results worksheet to reference Table1 and not a cell range.
The formula in Results!F7 is now:

Please Login or Register  to view this content.

The formula in Results!F9 is

Please Login or Register  to view this content.

The formula in Results!F11 is now

Please Login or Register  to view this content.

I tried the macros and there is one dangerous one that wipes out the Results worksheet entirely. I Think that this may be part of your problem as there is nothing left below row 3. I think what you really want to do is to clear or update the data in Table1 and refresh the Pivot table. The Pivot table when refreshed will reflect the new data on the Scheduled Work worksheet and all of the calculations on the Results worksheet will be correct.

I enclose your workbook with the changes so that you can see if that addresses the problems that you are encountering. If you use the macro that wipes out the Results worksheet then everything that I have done will not work....I don't see the purpose of that macro and if it was mine, I would trash it.

[4lowie]

Thanks! Sounds like you've done fair bit of work for me. The clear macro clears all the data from columns A to P (i think its P), once this data is cleared we paste in the fresh data that will occupy columns A to P. This is the data we will be reporting on.
Maybe I'll start by explaining what happens with this sheet.
We start by clearing the data from the "scheduled work" sheet, all data except the status and excuse columns. Next we copy and paste a new set of data from another workbook, this comes from a locked template that we can't do anything with.The next macro we use hides some columns and removes some rows that are day headings so the pivot table works. Once this formatting is done its handed over to the supervisors who update the work status on the last 2 columns of the "schedule work " sheet. The results sheet then tracks pprogress.

So hopefully that explains it in a bit more detail. It basically did what I wanted apart from the problems mentioned at the start of this thread.
So thanks again for looking at this....
Dave

[newdoverman]

The macro that clears the data on the Scheduled Work worksheet from columns A to P may be a problem. That worksheet as I have given you is a table that extends only to row 478 because that is all the data that you presently have. If you go to R478 you will see a small triangle in the lower right of that cell. That is the end of the table. As I understand this table, the Status and Excuse column data will no longer be valid once the other data is removed. If that is the case, with the cursor, grab the little triangle at the bottom of the table and drag it up to be at the bottom of the first row of the table in column R. Don't remove the first row of the table data. There is no need to unhide column P as the contents of the column will be deleted with the "shrinkage" of the table to 1 row and will be replaced when you paste in new data. With the table now reduced to one row, when you paste in the new data, the table will increase in size to accommodate the new data and all references to the columns of the table will be updated properly.

Delete all the data that is now outside of the table.
Copy the new data that you have and click into the first cell of the table (upper left) and paste the new data into the table. The table will expand to the correct size. Right Click in the Pivot table and Refresh. The Pivot table will now be up to date. The Status and Excuse columns are now ready to be filled in and the Data Validation for those columns is still in place ready to be used.

[4lowie]

Thanks newdoverman!
I had a bit of trouble finding my way around the table...... Anyway ended up making the table 1000 rows as it will never exceed that. So as long as that 1000 rows doesn't change it will be sweet. Played around with the format of the excuse data and I'm pretty happy with the results now.
So, thanks again for your help. Much appreciated.

Dave

[newdoverman]

Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.