Forum Discussion
cells in a workbook from cells in other workbooks
Hi,
I have a workbook called 'InvoiceList' the cells are populated with information from my invoices which are each a separate workbook.
Up to now I have populated the cells either by using '=' then navigating to the cell I want the information from, or by using the same method then dragging the cell in the InvoiceList workbook and editing the new cells to reference the next invoice number.
example:
A cell in the invoiceList worksheet contains the following: ='[INV101.xlsx]Sheet1'!$F$17
where the F17 cell contains the customers name.
The next cell (to the right) in the InvoiceList work sheet contains: ='[INV101.xlsx]Sheet1'!$H$20
where the H20 cell contains the date of the invoice.
There are more cells with more information so I end up with a summary of that invoice.
In the next row, I want the same information but for my next invoice number.
Here's my question - is there an easy way to populate the cells in my InvoiceList workbook? If I drag down a cell or cells, ideally the '101' in the example would become 102, 103 etc.
I'm sure this must be easy for you guru's - but I'm a 'needs-must' user 🙂
Cheers
Mark
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.
- PondMukCopper Contributor
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!!