Jul 05 2022 02:45 PM
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.
Jul 05 2022 10:01 PM
@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?
Jul 06 2022 07:05 AM
@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.
Jul 06 2022 07:26 AM
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!!
Jul 06 2022 07:26 AM
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!!