Problems with tables and calculated columns

Copper Contributor

Hey people,

 

I am currently building a spreadsheet to which people can add information in 1 worksheet which will then get pulled to a centralised worksheet for overviewing. My main issue has been the fact that it will be used by some people who are not great at using excel and so I am trying to make it as fool proof as possible so that they cannot break it and I don't have to constantly fix them. 

 

To do so I have created tables which means when they add extra rows into the individual tables, it automatically updates on the larger table which is on a different sheet. What I cannot for the life of me work out is how to make it so they can delete rows on the centralised table and not have it ruin the rest of the document. In hinges on the fact that I have a structured reference in row 36 which is referring to a table in a different sheet, and it will only let me pull in the information from row 36 instead of being able to start at row 2, where my information is.

 

I have tried offset but it doesn't work due to the fact that if they delete information above then it will ruin the number I offset it by. I can't add rows before the original tables and hide due to this issue. I could just lock the rows so they cannot delete them, but I wanted to check is there anyway to make it start at row 2 instead of 36 like the structured reference wants to?

0 Replies