work sheet coding
 

Hi all,
I am using Sum + If functions to get results but it is lengthy formula( may
it can be shorten that I don't know how) .... it's taking much more time to
get update because it has reference from another file. I want to lessen the
time by coding in work sheet. here is the formula
=IF(D5=SUM(IF(('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV Retics
for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$F$2:$F$2920=B5)*('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$O$2:$O$2920="NRAO")+('G:\MKT\NAS\LV Retics for Operations\[Plotting of
LV Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$F$2:$F$2920=B5)*('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$O$2:$O$2920="VRAO")+('G:\MKT\NAS\LV Retics for Operations\[Plotting of
LV Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$F$2:$F$2920=B5)*('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$O$2:$O$2920="NA"),1)),"comp","yts/pend")

if anyone want me to send files I will send two excels.

Please help me how to code in work sheet? are there any sites to learn how
to code?

thanks in advance.
dad

work sheet coding
 

One thought ..

Try this shorter, non-array rendition using SUMPRODUCT.

Just press ENTER to confirm the formula:
=IF(D5=SUMPRODUCT(('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$F$2:$F$2920=B5)*('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$O$2:$O$2920={"NRAO","VRAO","NA"})),"comp"," yts/pend")

The above is essentially, if represented simply
w/o the longish file path, book name & sheetname:
=IF(D5=
SUMPRODUCT((Col_F_range=B5)*(Col_O_range={"NRAO"," VRAO","NA"}))
,"comp","yts/pend")

I'm not really sure whether the above simplification will speed up recalc
appreciably (you gotta test it out over there), but it's certainly easier on
the eyes and easier to understand what's going on. That said, I do recollect
having read that formulas are generally faster than code.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dad" wrote:
Hi all,
I am using Sum + If functions to get results but it is lengthy formula( may
it can be shorten that I don't know how) .... it's taking much more time to
get update because it has reference from another file. I want to lessen the
time by coding in work sheet. here is the formula
=IF(D5=SUM(IF(('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV Retics
for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$F$2:$F$2920=B5)*('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$O$2:$O$2920="NRAO")+('G:\MKT\NAS\LV Retics for Operations\[Plotting of
LV Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$F$2:$F$2920=B5)*('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$O$2:$O$2920="VRAO")+('G:\MKT\NAS\LV Retics for Operations\[Plotting of
LV Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$F$2:$F$2920=B5)*('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$O$2:$O$2920="NA"),1)),"comp","yts/pend")

if anyone want me to send files I will send two excels.

Please help me how to code in work sheet? are there any sites to learn how
to code?

thanks in advance.
dad

work sheet coding
 

Thank you Max

"Max" wrote:

One thought ..

Try this shorter, non-array rendition using SUMPRODUCT.

Just press ENTER to confirm the formula:
=IF(D5=SUMPRODUCT(('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$F$2:$F$2920=B5)*('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$O$2:$O$2920={"NRAO","VRAO","NA"})),"comp"," yts/pend")

The above is essentially, if represented simply
w/o the longish file path, book name & sheetname:
=IF(D5=
SUMPRODUCT((Col_F_range=B5)*(Col_O_range={"NRAO"," VRAO","NA"}))
,"comp","yts/pend")

I'm not really sure whether the above simplification will speed up recalc
appreciably (you gotta test it out over there), but it's certainly easier on
the eyes and easier to understand what's going on. That said, I do recollect
having read that formulas are generally faster than code.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dad" wrote:
Hi all,
I am using Sum + If functions to get results but it is lengthy formula( may
it can be shorten that I don't know how) .... it's taking much more time to
get update because it has reference from another file. I want to lessen the
time by coding in work sheet. here is the formula
=IF(D5=SUM(IF(('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV Retics
for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$F$2:$F$2920=B5)*('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$O$2:$O$2920="NRAO")+('G:\MKT\NAS\LV Retics for Operations\[Plotting of
LV Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$F$2:$F$2920=B5)*('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$O$2:$O$2920="VRAO")+('G:\MKT\NAS\LV Retics for Operations\[Plotting of
LV Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$F$2:$F$2920=B5)*('G:\MKT\NAS\LV Retics for Operations\[Plotting of LV
Retics for Operations_STATUS.xls]KIOSK & INDOOR SUB
LIST'!$O$2:$O$2920="NA"),1)),"comp","yts/pend")

if anyone want me to send files I will send two excels.

Please help me how to code in work sheet? are there any sites to learn how
to code?

thanks in advance.
dad

work sheet coding
 

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rod" wrote in message
...
Thank you Max