I have two sheets "Data" and "Main" where the "Data" spreadsheet reads from an external source and automatically updates. The "Main" is set up right now so that each cell references the corresponding cell in the "Data" sheet, i.e. A1 in "Main" reads
=IF(data!A1<>0, data!A1, ""), and I've extended these formulas down the A column.
The problem is that when the "Data" sheet reads from its external data source, it can change size, which ends up changing some of the cell references in the "Main" sheet. As a result, I ideally want a setup where the following is true:
main!A1 = IF(data!$A$1<>0, data!$A$1, "")
main!A2 = IF(data!$A$2<>0, data!$A$2, "")
main!A3 = IF(data!$A$3<>0, data!$A$3, "")
- and so on and so on...
However, it appears that autofill no longer works once I introduce absolute references in my formulas. Is there anyway for me to alter how this operates so I can get autofill to work? I ideally do not want to use macros/VBA to solve this.