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.

  • 2
    Does =IF(INDEX(Data!$A:$A, ROW())<>0, INDEX(Data!$A:$A, ROW()), "") solve the problem with the Data worksheet?
    – user385793
    Jan 17, 2020 at 14:02
  • That's perfect! Much appreciated, thank you.
    – Nahmid
    Jan 17, 2020 at 14:11

1 Answer 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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .