Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
work sheet coding
Welcome
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rod" wrote in message ... Thank you Max |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting totals from 1 work sheet to another work work sheet | Excel Discussion (Misc queries) | |||
GETTING SELECTED DATA FROM A WORK SHEET TO OTHER WORK SHEET | Excel Discussion (Misc queries) | |||
Populating work sheet combox with another work sheet values | Excel Discussion (Misc queries) | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
Spin button in a work sheet - how do I make it work? | Excel Worksheet Functions |