How can I make a formula in Excel 2007 work that requires inputs from several files?

atul December 19, 2012
Pinterest Stumbleupon Whatsapp

I am using Excel 2007.

My intention is for our calculation file to be separate from our data record files. In data record files I have store data Financial Year wise, i.e. 2009-2010 / 2010-2011 / 2011-2012 / 2012-2013 because our data record is very very huge.

Now I have to work in one file. But due to huge data our file & calculation (processing time) run time is very slow.
Total calculation customer list is more than 2500.

In sheet1 in column a=customer code
b= customer name
c= district
d= engine number
e= count number
f= formula =SUMIF(DATA!$AI:$AI,”:”&$A4&”::”&$F$2&”:”,DATA!$N:$N)
g=formula =IF(ISNA(INDEX(DATA!$B$2:$B$50000,MATCH(1,INDEX(($A4=DATA!$C$2:$C$50000)*(Sheet1!K$2=DATA!$Q$2:$Q$50000),0),0))),””,INDEX(DATA!$B$2:$B$50000,MATCH(1,INDEX(($A4=DATA!$C$2:$C$50000)*(Sheet1!K$2=DATA!$Q$2:$Q$50000),0),0)))

Our data record in “DATA” sheet.
In view of above please let us know to how to use above formula if i separated financial year wise data record in different file.

File path is : d:\fox26p\abad\part_data_analysis_abad.xlsx (this is present file, in sheet1 i have calculate)
If in above path i save Financial year wise files for example…
d:\fox26p\abad\PART_DATA0910_ABAD.XLSX
d:\fox26p\abad\PART_DATA1011_ABAD.XLSX
d:\fox26p\abad\PART_DATA1112_ABAD.XLSX
d:\fox26p\abad\PART_DATA1213_ABAD.XLSX

I don’t want using sumproduct formula this please be noted.

Ads by Google

  1. Amit Sinha
    December 22, 2012 at 12:02 pm

    Just open all the documents simultaneously and enter the function with selecting table array with mouse in different documents and press F4 twice after selecting table array and hit enter. Now your function is ready whenever any value changes in any document function related to it changes itself. Whenever you opens the documents it referesh the the function and checks the availability of link and if it broken it warns you. When you send your one documents to anyone which has function related to other doucments'value then the most updated value ill be reflected in others computer and formula will show an error that the link is not found and the last value will remain.

  2. Rob Hindle
    December 20, 2012 at 12:16 am

    Review your overall approach to the problem. It sounds as if you're trying to use Excel for a task better suited to a database. Migrate the whole job to Access if you have it, otherwise MySQL (free).