Forum Discussion
Reference a sequence of filenames
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!!
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?
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.
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!!
- Thank you SO much!!