I have a list in Sheet3 that is filled out with data from a Sheet1 column. To do this, it just uses standard cell referencing. When the work in a row is finished, I sometimes move full rows from Sheet1 to Sheet2. As a result, after moving something to Sheet 2, I no longer want that item to be listed in Sheet 3's list. However, Excel automatically switches the reference from Sheet1 to Sheet2 in the calculation in Sheet3 when I move the cells from Sheet1 to Sheet2, defeating my goal.
How can I utilize an absolute reference in my spreadsheet so that formulas don't change when cells are moved? I'm aware that Libreoffice has a feature where the sheet name can just be prefixed with the $ sign. Excel, however, doesn't support that.
My formula in row 1 column 1 of Sheet3 is:
=Sheet1!C6