As a life coach I use Excel for my bookkeeping. I created this formula that checks if a session is still to be billed to a client. In the attached file "Example.xlsx" you'll find the formula on the sheet "Hours".
The formula says:
=-((SUMPRODUCT(SUMIF(INDIRECT(Example.xlsx!Numbers&"!b3:b100");G1;INDIRECT(Example.xlsx!Numbers&"!i3:i100"))))-G4)
What it does is count the number of sessions on bills (i3:i100) for a certain company (G1) and compares it to the total number of sessions for this specific client (G4). The number of bills is checked by using a named area "Numbers" and a sheet with a list of the sent invoices.
It works brilliantly, but:
Since the number of invoices I send is increasing, the file becomes very slow. The reason behind this is that I still need all the sent bills in the file, because of the above formula. What I am now trying to do, is remove these invoices and place them in a new file. Then I would of course like the above formula to still work with the reference to the external file with the invoices. But I can't get this to work.
The formula breaks down on the reference to the externally named area "Numbers". I tried to replace numbers with the specific area (being A4:R23), but this doesn't work either.
How to solve this?
Thanks in advance, Peggy
