Forum Discussion
cells in a workbook from cells in other workbooks
You might use the INDIRECT function. It will only work if the other workbooks are all open in Excel.
Lets say the formula ='[INV101.xlsx]Sheet1'!$F$17 is in a cell in row 2.
Change it to =INDIRECT("'[INV"&ROW()+99&".xlsx]Sheet1'!$F$17")
Adding 99 to the row number 2 results in 101.
This can be filled or copied to the cells below.
Thanks Hans - It's nearly there, I'm fine with the other workbooks needing to be open when I first create them and add them to the InvoiceList, but when I subsequently open InvoiceList it sort of defeats the objective having to open all the invoice workbooks.
I didn't know about the "& &" trick - that's useful - and instead of ROW()+99 I have been able to put a cell location (eg A2 - where A2 has a value of 101, then A3 would be 102 etc) - less confusing for my simple mind!!