maksinx
03-06-2007, 03:24 PM
Hello everyone,
I have a file called analysis and it has some formulas in it,which is linked to other data sources, where it gets data from company server,
i have nearly 40 rows of formula for each supplier and each customer
therefore below stated formula is used nearly 80 times,
what i want to do is to change the main data excel file, name sheet name as well but that time my all working formulas will return #ref.
is there any quicker way to change the link other source of data rather than changing the whole cell formulas manualy.
if you need further information please let me know.
thanks
sample formula is stated as below;
SUMPRODUCT(('C:\2007\Share\[ TRACKING 2007.xls]JUN 2007'!$J$2:$J$500="n/a")*('C:\2007\Share\[ TRACKING 2007.xls]JUN 2007'!$R$2:$R$500="not shipped")*(('C:\2007\Share\[ TRACKING 2007.xls]JUN 2007'!$AE$2:$AE$500="shirts")*('C:\2007\Share\[ TRACKING 2007.xls]JUN 2007'!$W$2:$W$500)))-I9
I have a file called analysis and it has some formulas in it,which is linked to other data sources, where it gets data from company server,
i have nearly 40 rows of formula for each supplier and each customer
therefore below stated formula is used nearly 80 times,
what i want to do is to change the main data excel file, name sheet name as well but that time my all working formulas will return #ref.
is there any quicker way to change the link other source of data rather than changing the whole cell formulas manualy.
if you need further information please let me know.
thanks
sample formula is stated as below;
SUMPRODUCT(('C:\2007\Share\[ TRACKING 2007.xls]JUN 2007'!$J$2:$J$500="n/a")*('C:\2007\Share\[ TRACKING 2007.xls]JUN 2007'!$R$2:$R$500="not shipped")*(('C:\2007\Share\[ TRACKING 2007.xls]JUN 2007'!$AE$2:$AE$500="shirts")*('C:\2007\Share\[ TRACKING 2007.xls]JUN 2007'!$W$2:$W$500)))-I9