How to Autofill Excel cells relatively with absolute formulas

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.

Solution 1:

You can use the current row to establish the row reference in a column.

=IF(INDEX(Data!$A:$A, ROW())<>0, INDEX(Data!$A:$A, ROW()), "")

This should fill down and preserve the cross reference to the Data worksheet.