work sheet coding

  • Thread starter Thread starter Dad
  • Start date Start date
D

Dad

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
 
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.
 
Thank you Max

Max said:
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.

---
Dad said:
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
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

code reuse 1
Search other sheets for numbers & color them 10
Automatically updating one sheet, from another 4
What is the code for the active worksheet? 12
macro from columns to rows but adding every time a new line 2
msgBox after update a cell 9
Impossible? Not plotting NA()s 2
Formula to calculate the data of the last order 1

Back
Top