I've set up the first sheet in a work book to accept data entry and the
column entries can vary from 1 to an undetermined number of rows.

I want worksheet two to automatically copy a particular cell from the first
worksheet. The following works as long as the data is manually entered on
the first worksheet, but when much data exists, the user copies the cells
from somewhere else. As a result, the formula reference no longer works. I
would like it to work no matter what is in the cell. Here is my
formula...your advise is much appreciated. Thanks.

=IF((ISBLANK('TABNAME'!$AI$4)), "None", 'TABNAME'!$AI$4)

I also want A4 through A15500 to be populated on several other worksheets as
the user enters data, but here again, manual entry works but the reference
goes away when the user understandably copies chunks of data. The formula I
was using is just a direct cell reference...

='TABNAME'!A4