cells in a workbook from cells in other workbooks

[PondMuk]

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

[#2]

@PondMuk 

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.

[#3]

@HansVogelaar

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!!