November 5, 2021
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
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.
July 16, 2010
Welcome to our forum!
You should avoid linking workbooks using formulas because they often break unless the working you're linking to isn't open at the same time.
You can use Power Query to get the data from the Itemlist.xlsx file and bring it into the stock and revenue files. In Power Query you can filter out the data you don't want in each file before loading it to the workbook. You can also refresh the queries to get new data from the Itemlist.xlsx file without having to open Itemlist.xlsx file or edit the query.
Files containing queries can be saved as .xlsx
You can see an introduction to Power Query here. In this video I get data from files in a folder, but you would only need to use Get Data > From File > From Workbook. If you'd like to learn Power Query, please consider my Power Query course.
I hope that points you in the right direction.