workbook reference

[benjii19]

Ok I have a workbook called PRODUCT LIST and one called STOCKTAKE.

We used to have a sheet called PRODUCT LIST in the STOCKTAKE workbook. Where the stocktake sheet referenced cells within PRODUCT LIST. I'm looking at the next logical step by having PRODUCT LIST a master workbook. Basically instead of cell referencing, workbook referencing.

I've got this working slightly for example I reference A1 to A10 from PRODUCT LIST into the STOCKTAKE work book. However in the STOCKTAKE workbook i need to start it at A3 for example. It doesn't like this and wants to start at A1.


Is there a way of referencing info in column A to Column B on the next workbook?

[ZERKproject]

If you want to reference A1 of the product list workbook in cell B3 of the stocktake workbook for example,
go into the stocktake workbook and enter this formula into B3:
='[workbookname.xls]sheetname'!$A$1

note the absolute references, if you wanted to be able to fill the formula down for example just remove the absolute reference ($) for the number, so:
='[workbookname.xls]sheetname'!$A1

[benjii19]

Thank you so much for that, Worked a treat.

I know that when a product needs to be added to the product list it won't mess up the stocktake as such. If that product needs to be on the stocktake sheet i just insert a row and put in the formulae.

One concern is if somebody were to SORT the PRODUCT LISTspreadsheet, this would then sort the STOCKTAKE sheet and mean the figures wouldn't match. Any ideas on how to avoid this??