Reference a sequence of filenames

[Firegirl614]

I have a spreadsheet for our running list of invoices. The purpose is not only to keep track of what number I am on but so I can add that weeks invoice totals to see the totals. I had been manually doing it, but now I have it set up to "Reference" each Invoice. I.e. Invoice 1 Cell D is location, Cell E is work order number, Cell F is Amount. & those each reference that Cell number from the file name "Invoice 1". The problem is when I copy and paste or fill down, unlike how excel auto fills a sequence, all the rows will reference "Invoice 1" How can I make the next row Reference filename "Invoice 2" "Invoice 3" and so on. It will not let me "ADD" for example, if I want to say B3+1 it returns B4. So I can't say Invoice 1+1 to get Invoice 2. I've tried parenthesis, comma's I've tried all the help things here, videos, 3-D reference help etc. Sorry if this is already a string. I tried searching & couldn't find anything.

[#2]

@Firegirl614 What you describe would work with INDIRECT and if all the invoice files were to be open. But I believe that's not the case. Consider using Power Query and connect to all files in the folder where you store the invoices and pick-up the information that is relevant to your summary report. Difficult to explain exactly how without having and example of an invoice file. Are you familiar with Power Query?

[#3]

@Riny_van_Eekelen  I'm not completely familiar with power query. I started reading about it yesterday thinking that might be it, but then after reading thought maybe not so stopped.  I will go back and read up on that.  Thanks. Just FYI, the invoices are all in one folder called ex: "ABC Company". They are not open, but future unused ones are already created.

So In my spreadsheet called "Running Invoices", ex: Column A all say "Invoice #",

Column B is the actual invoice #, i.e. B1 = 100, B2 =B1+1 which returns 101, B3= B2+1 which returns 102. That you can fill down obviously.   

Then D1  ='C:\ABC Company\[Invoice 100.xlsx]Invoice'!$B$5, Returns the Job Address for invoice 100,  E1 ='C:\ABC Companyt\[Invoice 100.xlsx]Invoice'!$F$17, Returns the total invoiced for Invoice 100., 

D2  ='C:\ABC Company\[Invoice 101.xlsx]Invoice'!$B$5, Returns the Job Address for invoice 101, 

E2 ='C:\ABC Companyt\[Invoice 101.xlsx]Invoice'!$F$17, Returns the total invoiced for Invoice 101. And so on.

The prob is when I fill down, that column (Column D) will ALL say: 

D3='C:\ABC Company\[Invoice 101.xlsx]Invoice'!$B$5, Returns the Job Address for invoice 101

D4='C:\ABC Company\[Invoice 101.xlsx]Invoice'!$B$5, Returns the Job Address for invoice 101 and I have to go manually and change 101 to 102, 103 etc.

I cannot say, ='C:\ABC Company\[Invoice 101(+1).xlsx]Invoice'!$B$5 or anything to that effect to get a return of  Invoice 102 for the next line. That is what I'm trying to do to not have to manually change it. it gives me a REF error.  Hope that makes it more clear.  However, I WILL go read more about power queries.  and Thanks again.

[#4 · Accepted solution]

@Firegirl614 The link below would be a good place to start. When you come to chapter 9, you'll learn about importing files in a folder. You will NOT regret learning PQ!!

https://exceloffthegrid.com/power-query-introduction/ 

[#5]

Thank you SO much!!