Excel - MSF Computing (Aust) P/L

⇒

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 Issues? System running slow? Can't remove a virus, network not running correctly ..... Call Us

Home

•	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: