External link to named area in other worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1235267%22%20slang%3D%22en-US%22%3EExternal%20link%20to%20named%20area%20in%20other%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1235267%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20a%20life%20coach%20I%20use%20Excel%20for%20my%20bookkeeping.%20I%20created%20this%20formula%20that%20checks%20if%20a%20session%20is%20still%20to%20be%20billed%20to%20a%20client.%20In%20the%20attached%20file%20%22Example.xlsx%22%20you'll%20find%20the%20formula%20on%20the%20sheet%20%22Hours%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20says%3A%3C%2FP%3E%3CP%3E%3D-((SUMPRODUCT(SUMIF(INDIRECT(Example.xlsx!Numbers%26amp%3B%22!b3%3Ab100%22)%3BG1%3BINDIRECT(Example.xlsx!Numbers%26amp%3B%22!i3%3Ai100%22))))-G4)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20it%20does%20is%20count%20the%20number%20of%20sessions%20on%20bills%20(i3%3Ai100)%20for%20a%20certain%20company%20(G1)%20and%20compares%20it%20to%20the%20total%20number%20of%20sessions%20for%20this%20specific%20client%20(G4).%20The%20number%20of%20bills%20is%20checked%20by%20using%20a%20named%20area%20%22Numbers%22%20and%20a%20sheet%20with%20a%20list%20of%20the%20sent%20invoices.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20brilliantly%2C%20but%3A%3C%2FP%3E%3CP%3ESince%20the%20number%20of%20invoices%20I%20send%20is%20increasing%2C%20the%20file%20becomes%20very%20slow.%20The%20reason%20behind%20this%20is%20that%20I%20still%20need%20all%20the%20sent%20bills%20in%20the%20file%2C%20because%20of%20the%20above%20formula.%20What%20I%20am%20now%20trying%20to%20do%2C%20is%20remove%20these%20invoices%20and%20place%20them%20in%20a%20new%20file.%20Then%20I%20would%20of%20course%20like%20the%20above%20formula%20to%20still%20work%20with%20the%20reference%20to%20the%20external%20file%20with%20the%20invoices.%20But%20I%20can't%20get%20this%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20breaks%20down%20on%20the%20reference%20to%20the%20externally%20named%20area%20%22Numbers%22.%20I%20tried%20to%20replace%20numbers%20with%20the%20specific%20area%20(being%20A4%3AR23)%2C%20but%20this%20doesn't%20work%20either.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%20solve%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%20Peggy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1235267%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1235289%22%20slang%3D%22en-US%22%3ERe%3A%20External%20link%20to%20named%20area%20in%20other%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1235289%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F585397%22%20target%3D%22_blank%22%3E%40pegpeg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Peggy%20-%20you%20forgot%20to%20attach%20sample%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

1 Reply

@pegpeg 

Hi Peggy - you forgot to attach sample file