I have a spreadsheet of data that needs to be regularly updated and edited. I use a number of detailed filters to examine different aspects of the data set, and I find it inconvenient to constantly re-enter the filter parameters when I want to switch from one filter to another. As far as I can tell, there is no method to store/save the parameters for multiple filters, and this wouldn’t be an ideal fix anyway.
I decided that a better solution would be to create other spreadsheets within the same document whose cell contents exactly mirror the cell contents of the first spreadsheet in real-time. I could then apply one of the filters I use to each of these additional spreadsheets and click over to them when I want to view that aspect of the original data. When I wanted to edit the data set, I could do so in the first (unfiltered) spreadsheet and the changes would be reflected in the other spreadsheets.
I tried to achieve the above by (absolutely) referencing the cell contents of the first sheet, using syntax like " =$Sheet1.$A$1". This accomplished most of my goals; if a cell’s contents are directly changed in the first spreadsheet, the other spreadsheets reflect the change. However, when I edit the contents of the first sheet, I often need to insert/delete rows/columns (particularly the drag-and-drop insert mode). Such changes are not reflected in the other sheets in the way I’d like, even when using an absolute reference. When I move a row in the first sheet, I’d like it to move in the other sheets.
For example, I define rows 1 and 2 of Sheet2 so that they reference rows 1 and 2 of Sheet1, i.e., A1 = Sheet1.A1, B2 = Sheet1.B2, etc. If I use the insert mode in Sheet1 and move row 1 below Row 2, essentially switching the contents of the two rows, then the row contents in the other sheets do not switch; instead, the reference itself changes. In Sheet2, I now see that A1 = Sheet1.A2, B2 = Sheet1.B1, etc. I assume that the same “updating” of absolute references occurs whenever rows are inserted/deleted or whenever cells are moved.
Is there a way to mimic the effect of referencing cell contents so that operations in which rows/columns/cells are inserted/deleted/moved are duplicated in the other sheets?