Copying a dynamic item list from one sheet to another - without adding spare rows.|General Excel Questions & Answers|Excel Forum|My Online Training Hub

I have a workbook with an inventory list with hundreds of items, a unique identifier, full product description, price and so on, and items are frequently added to the list.  Then I have separate workbooks to deal with forecast usage and predicted stock levels (that I share with a customer) and yet another (that I only share with my boss) that deals with predicted revenue & margin.

let's call them

itemlist.xlsx

stock.xlsx

revenue.xlsx

All three files have the unique identifier as column A

I want the stock & revenue workbooks to automatically pick up all the unique identifiers, but without any additional rows.  Obviously I can copy a formula like [indexlist.xlsx]Sheet1!A:A  , adding a few extra rows, and even make any additional rows appear to be blank by preceding the formula with an =IF(ISBLANK( statement.

But what I want is the last row of the stock and revenue worksheets to be the last row with item data in them.  Any spare rows (whether they contain zeros or a formula which returns a "" mess things up later.  

I've a way of doing this in VBA, but my customer doesn't like .xlsm files, and whilst I could resave it after each update as a macro-free workbook that's awfully clunky.  

Is there a way of doing this with a formula?  maybe one of new ones ending with a # - which I confess I have yet to fully get to grips with.