⇒
Excel Forumlas
Formula:
=IF(SUMPRODUCT((Make_List<>"")*
ISERROR(MATCH(Make_List,$F$2:$F2,0)))<>0,
INDEX(Make_List,MATCH(TRUE,ISERROR(IF(ISBLANK(Make_List),FALSE,MATCH(Make_List,$F$2:$F2,0))),0),1),"")
Make_List =Control_Data!$A3:$A1030 (Make_List is the Defined name in the workbook for cell A3:A1030)
Make |
Start |
Count |
Canon |
1 |
47 |
Orion |
48 |
1 |
1 |
Canon |
iR C2570 PCL5c |
'Canon iR C2570 PCL5c' 'Canon' 'PRINT1:ndps/resdir/ENGLISH/Prndrv/winXP/Canon iR C2570 PCL5c/ndps.inf' |
P5 |
2 |
Canon |
iR C2570 PS3 |
'Canon iR C2570 PS3' 'Canon' 'PRINT1:ndps/resdir/ENGLISH/Prndrv/winXP/Canon iR C2570 PS3/ndps.inf' |
PS |
3 |
Canon |
iR C2880/C3380 |
'Canon iR C2880/C3380 PCL5c' 'Canon' 'PRINT1:ndps/resdir/ENGLISH/Prndrv/winXP/Canon iR C2880_C3380 PCL5c/ndps.inf' |
P5 |
48 |
Eltron |
Orion |
'Eltron Orion' 'Eltron' 'PRINT1:ndps/resdir/ENGLISH/Prndrv/winXP/Eltron Orion/ndps.inf' |
VD |
This example of a complex Excel function was requested to lookup through a list of supplied print drivers and workout the starting row for each Make, and how many of the Make there were.
First we define a Name List in the workbook from the worksheet containing the list, we are calling it Make_List
Make_List is a dynamic list in the $A column. From this will make an array.
The following formula, will read the Make_List file match any found same MAKE and count and populate the Two columns Start & Count.
As shown in the last table.
The resulting array
For more information and help on Excel Spreadsheet formula's, please contact us.
Stay there,
we will come to you.
+61 412 003 338
• |
Computer Networking |
• |
Internet Firewall & Security |
• |
Hardware Repairs |
• |
On Going Maintenance |
• |
One Stop Computing |
• |
SOHO Network Setup |
• |
Virus & Spyware Removal |
• |
Upgrading / Buying a Computer |
• |
Website Design & Development |
Our Services:
Term & Conditions | Privacy Policy | Sitemap | Terms of Use | Web Design | Excel Solutions
Copyright © 2012