I have a list in Sheet3 populated using contents of a column in Sheet1. It uses simple cell referencing to do so. However sometimes I move entire rows from Sheet1 to Sheet2 when the task in that row is completed. So once moved, I no longer want the list in Sheet3 to list the item that I've just moved to Sheet2. However, when I move the cells from Sheet1 to Sheet2, excel automatically changes the reference from Sheet1 to Sheet2 in the formula in Sheet3 hence failing my objective.
How can I use absolute reference to my worksheet so that when I move cells, the formulas remains unchanged? I know such a feature exists in Libreoffice where you can simply use a $ symbol before the sheet name. But that doesn't work in Excel.
My formula in row 1 column 1 of Sheet3 is:
=Sheet1!C6
And this is copied down to all the rows below making it Sheet1!C7, Sheet1!C8 and so on in the rows below.
Thanks to @mitch-Nz and @Naresh Bhope for the solution which worked:
=INDIRECT("Sheet1!C"&ROW())
I simply used this in Sheet3 and now if I move rows to a different worksheet, the reference will stay as Sheet1 itself in sheet3.