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? – Jeeped Jan 17 at 14:02
  • That's perfect! Much appreciated, thank you. – Nahmid Jan 17 at 14:11

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.