Formula placement where data rows are constantly changing

Chris P

Hi All, 

I am trying to build a report or sight that captures the count of various columns based on other columns. {PLEASE add the funciontality of formulas referencing other sheets}.

The count and countifs formulas work well in isolation. I can add the formula in a new column and then hide this column. 

The problem I have is that my data rows are constantly changing. That is, new rows being added and old rows being deleted/archived.

The formulas relate to the whole sheet therefore if I place the formula in a cell in any row, this will work fine but if this row gets deleted I loose the summary data. What is the best practice for this issue? Do you create a row of data in your sheet that is just used to capture formulas? Whilst this will work it would make the data set inaccurate. 

Any suggestions would be appreciated. 

Thanks,

Chris

Mike Wilday

I've seen people put the summary data at the top or bottom of their sheet depending on where new rows are added. You can lock the rows to prevent other users from deleting them. Just make sure your reference column is on rows that no other data exists. You could put information like "DO NOT DELETE" on the primary column and lock the rows. You might have to name the column so that when sorted the data remains together. You also want to be sure that the other users don't have access to delete locked rows. Their role should be editors. Locking the rows will prevent editors from deleting locked rows. 

Perhaps those thoughts will help.