IF/OFFSET Problem

Hi,

I have a problem with the following IF/OFFSET formula which is dragged down the length of a worksheet:

=IF(ISERROR(IF(A,DataLink!D1550)),"",IF(A,DataLink!D1550))
Where A =OFFSET(DataLink!$A$1,0,0,COUNTA(DataLink!$A:$A),1)

The DataLink worksheet is fed from a database and exists in the same spreadsheet as the worksheet which contains the above formula. When the spreadsheet is opened, DataLink is refreshed, invariably with new rows of data.

The problem I am facing is that when DataLink is refreshed, the IF formula in the first new row of data changes its row reference to the last new row of data and each subsequent row reference follows in numerical order – the result being that blank fields are referenced and no data is being pulled through.

Any ideas? Your help would be gratefully appreciated.

Thank you

Hi Richard,

Thank you for your reply. I'm not entirely sure what you are advising?

In response to your question though, DataLink is refreshed from a database on opening. If a new record of data has been entered in the database, this record is entered in Datalink as a new row of data and is entered at the bottom of the existing list. So the number of rows of data in DataLink increases everytime a new record is entered in the source database.

Sheet2 with the IF/OFFSET formula is dragged from row1 to row60,000 and includes the dynamic OFFSET reference to account for the variable number of rows in DataLink.

If DataLink is refreshed with 20 new rows from rows' 500:520 then the IF formula in Sheet2/row 500 which was referenced to DataLink/row 500 will unintentionally change its row reference to Datalink/row 520 which is the last row of new data. Each subsequent row will then follow suit i.e from 501 to 521, from 502 to 522 and so on which, given the last new row of data in DataLink is row 520, pulls through blanks.

I hope this helps clarify my problem.

Thank you.

Hi,

Any ideas? I can't really upload an example as the problem is caused when the spreadsheet is refreshed by the data sourc - an external database.

Any suggestions would be greatly appreciated.

Thank you

DonkeyOte's solution works perfectly.

Thank you