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.