Index Function Problem

I need a function that will return all information for "truck 1" on another 
worksheet. The information below is an example of information on my 
worksheet. I need a function that will search the data and return all 
instances of "truck 1" on a separate worksheet. 
    
     B               C                           D                  E        
 F
   Grade         Mill                       Driver          Weight   Rate

1   ZONE 2	      BATSON	WILSON	     25.00	
2   ZONE 3	      CAMDEN	TROY	     26.00	
3   ZONE 4	      CLW	                TRUCK 1	     27.00	   7
4   18 - 20 top  CORRIGAN	TRUCK 2	     28.00	   8
5   Dead Logs   EVADALE	TRUCK 3	     29.00	   9
6   PW	       GP                      WILSON	     30.00	
7   CNS	       LUFKIN	 TROY	     31.00	
8   HW Logs      NAPCO	 TRUCK 1	     32.00	   10
9   ZONE 2        STONEHAM	 TRUCK 2	     33.00	   11
10  ZONE 3       BATSON	  WILSON       34.00	   12
11  ZONE 4       CAMDEN	  WILSON	     35.00	
12  18 - 20 top  CLW	                  TROY	     36.00	
13  Dead Logs   CORRIGAN	  TRUCK 1	     37.00	    13
14  PW	        EVADALE	  TRUCK 2	     38.00	    14
15  CNS	        GP	                  TRUCK 3	     39.00	      7

I have an idex function on a separate worksheet and it works for the first 
line but I need a function that will move to the second line and return the 
next instance until all instances are returned so I can calculate pay for the 
driver. Here's what I have:
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
=INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)

Please help!
0
Utf
12/24/2009 12:35:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

7 Replies
628 Views

Similar Articles

[PageSpeed] 41

Try this...

Sheet2 A1 = Truck 1

Enter this formula on Sheet2 B1:

=COUNTIF(DRIVER_NAME,A1)

Enter this array formula** on Sheet2 A2:

=IF(ROWS(A$2:A2)>B$1,"",INDEX(LEASE_DRIVERPAY,SMALL(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

Copy down until you get blanks.

-- 
Biff
Microsoft Excel MVP


"indyjojo" <indyjojo@discussions.microsoft.com> wrote in message 
news:EF17F864-6175-438D-A1E1-C2004CC6D4EF@microsoft.com...
>I need a function that will return all information for "truck 1" on another
> worksheet. The information below is an example of information on my
> worksheet. I need a function that will search the data and return all
> instances of "truck 1" on a separate worksheet.
>
>     B               C                           D                  E
> F
>   Grade         Mill                       Driver          Weight   Rate
>
> 1   ZONE 2       BATSON WILSON      25.00
> 2   ZONE 3       CAMDEN TROY      26.00
> 3   ZONE 4       CLW                 TRUCK 1      27.00    7
> 4   18 - 20 top  CORRIGAN TRUCK 2      28.00    8
> 5   Dead Logs   EVADALE TRUCK 3      29.00    9
> 6   PW        GP                      WILSON      30.00
> 7   CNS        LUFKIN TROY      31.00
> 8   HW Logs      NAPCO TRUCK 1      32.00    10
> 9   ZONE 2        STONEHAM TRUCK 2      33.00    11
> 10  ZONE 3       BATSON   WILSON       34.00    12
> 11  ZONE 4       CAMDEN   WILSON      35.00
> 12  18 - 20 top  CLW                   TROY      36.00
> 13  Dead Logs   CORRIGAN   TRUCK 1      37.00     13
> 14  PW         EVADALE   TRUCK 2      38.00     14
> 15  CNS         GP                   TRUCK 3      39.00       7
>
> I have an idex function on a separate worksheet and it works for the first
> line but I need a function that will move to the second line and return 
> the
> next instance until all instances are returned so I can calculate pay for 
> the
> driver. Here's what I have:
> =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
> =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
> =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)
>
> Please help! 


0
T
12/24/2009 3:15:02 AM
Thanks so much for your time. I got the blanks but the first five lines 
return an error. I changed the range names to what I thought would give me 
the mill name but instead I got "truck 1". I changed the formula to this: 
 
=IF(ROWS(A$5:A5)>B$1,"",INDEX(DRIVER_NAME,SMALL(IF(DRIVER_NAME=A$1,ROW(MILL_NAME)),ROWS(A$5:A5))-MIN(ROW(MILL_NAME))+1))

The range names are as follows:

colC Mill_Name
colD Driver_Name
colC:F Lease_Driverpay

All the information in the range name Lease_Driverpay needs to go over to 
worksheet 2 but only for truck 1. Once I get a functional formula, can this 
be accomplished by copying the formula to the right? or will I have to do a 
different formula for each column? 

Again thank you for your time and assistance.

"T. Valko" wrote:

> Try this...
> 
> Sheet2 A1 = Truck 1
> 
> Enter this formula on Sheet2 B1:
> 
> =COUNTIF(DRIVER_NAME,A1)
> 
> Enter this array formula** on Sheet2 A2:
> 
> =IF(ROWS(A$2:A2)>B$1,"",INDEX(LEASE_DRIVERPAY,SMALL(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))
> 
> ** array formulas need to be entered using the key combination of 
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
> key then hit ENTER.
> 
> Copy down until you get blanks.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "indyjojo" <indyjojo@discussions.microsoft.com> wrote in message 
> news:EF17F864-6175-438D-A1E1-C2004CC6D4EF@microsoft.com...
> >I need a function that will return all information for "truck 1" on another
> > worksheet. The information below is an example of information on my
> > worksheet. I need a function that will search the data and return all
> > instances of "truck 1" on a separate worksheet.
> >
> >     B               C                           D                  E
> > F
> >   Grade         Mill                       Driver          Weight   Rate
> >
> > 1   ZONE 2       BATSON WILSON      25.00
> > 2   ZONE 3       CAMDEN TROY      26.00
> > 3   ZONE 4       CLW                 TRUCK 1      27.00    7
> > 4   18 - 20 top  CORRIGAN TRUCK 2      28.00    8
> > 5   Dead Logs   EVADALE TRUCK 3      29.00    9
> > 6   PW        GP                      WILSON      30.00
> > 7   CNS        LUFKIN TROY      31.00
> > 8   HW Logs      NAPCO TRUCK 1      32.00    10
> > 9   ZONE 2        STONEHAM TRUCK 2      33.00    11
> > 10  ZONE 3       BATSON   WILSON       34.00    12
> > 11  ZONE 4       CAMDEN   WILSON      35.00
> > 12  18 - 20 top  CLW                   TROY      36.00
> > 13  Dead Logs   CORRIGAN   TRUCK 1      37.00     13
> > 14  PW         EVADALE   TRUCK 2      38.00     14
> > 15  CNS         GP                   TRUCK 3      39.00       7
> >
> > I have an idex function on a separate worksheet and it works for the first
> > line but I need a function that will move to the second line and return 
> > the
> > next instance until all instances are returned so I can calculate pay for 
> > the
> > driver. Here's what I have:
> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)
> >
> > Please help! 
> 
> 
> .
> 
0
Utf
12/24/2009 5:35:02 AM
What version of Excel are you using?

Unless you're using Excel 2007 your named range can't refer to entire 
columns and be used in an array formula.

-- 
Biff
Microsoft Excel MVP


"indyjojo" <indyjojo@discussions.microsoft.com> wrote in message 
news:2489A99C-95DE-49BE-84D4-C1F137B4B43F@microsoft.com...
> Thanks so much for your time. I got the blanks but the first five lines
> return an error. I changed the range names to what I thought would give me
> the mill name but instead I got "truck 1". I changed the formula to this:
>
> =IF(ROWS(A$5:A5)>B$1,"",INDEX(DRIVER_NAME,SMALL(IF(DRIVER_NAME=A$1,ROW(MILL_NAME)),ROWS(A$5:A5))-MIN(ROW(MILL_NAME))+1))
>
> The range names are as follows:
>
> colC Mill_Name
> colD Driver_Name
> colC:F Lease_Driverpay
>
> All the information in the range name Lease_Driverpay needs to go over to
> worksheet 2 but only for truck 1. Once I get a functional formula, can 
> this
> be accomplished by copying the formula to the right? or will I have to do 
> a
> different formula for each column?
>
> Again thank you for your time and assistance.
>
> "T. Valko" wrote:
>
>> Try this...
>>
>> Sheet2 A1 = Truck 1
>>
>> Enter this formula on Sheet2 B1:
>>
>> =COUNTIF(DRIVER_NAME,A1)
>>
>> Enter this array formula** on Sheet2 A2:
>>
>> =IF(ROWS(A$2:A2)>B$1,"",INDEX(LEASE_DRIVERPAY,SMALL(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
>> SHIFT
>> key then hit ENTER.
>>
>> Copy down until you get blanks.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "indyjojo" <indyjojo@discussions.microsoft.com> wrote in message
>> news:EF17F864-6175-438D-A1E1-C2004CC6D4EF@microsoft.com...
>> >I need a function that will return all information for "truck 1" on 
>> >another
>> > worksheet. The information below is an example of information on my
>> > worksheet. I need a function that will search the data and return all
>> > instances of "truck 1" on a separate worksheet.
>> >
>> >     B               C                           D                  E
>> > F
>> >   Grade         Mill                       Driver          Weight 
>> > Rate
>> >
>> > 1   ZONE 2       BATSON WILSON      25.00
>> > 2   ZONE 3       CAMDEN TROY      26.00
>> > 3   ZONE 4       CLW                 TRUCK 1      27.00    7
>> > 4   18 - 20 top  CORRIGAN TRUCK 2      28.00    8
>> > 5   Dead Logs   EVADALE TRUCK 3      29.00    9
>> > 6   PW        GP                      WILSON      30.00
>> > 7   CNS        LUFKIN TROY      31.00
>> > 8   HW Logs      NAPCO TRUCK 1      32.00    10
>> > 9   ZONE 2        STONEHAM TRUCK 2      33.00    11
>> > 10  ZONE 3       BATSON   WILSON       34.00    12
>> > 11  ZONE 4       CAMDEN   WILSON      35.00
>> > 12  18 - 20 top  CLW                   TROY      36.00
>> > 13  Dead Logs   CORRIGAN   TRUCK 1      37.00     13
>> > 14  PW         EVADALE   TRUCK 2      38.00     14
>> > 15  CNS         GP                   TRUCK 3      39.00       7
>> >
>> > I have an idex function on a separate worksheet and it works for the 
>> > first
>> > line but I need a function that will move to the second line and return
>> > the
>> > next instance until all instances are returned so I can calculate pay 
>> > for
>> > the
>> > driver. Here's what I have:
>> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
>> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
>> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)
>> >
>> > Please help!
>>
>>
>> .
>> 


0
T
12/24/2009 5:53:32 AM
Hi indyjojo,

For some working examples, see the attachments to my posts at:
http://www.techsupportforum.com/microsoft-support/microsoft-office-support/375768-excel-formula-issues.html

-- 
Cheers
macropod
[Microsoft MVP - Word]


"indyjojo" <indyjojo@discussions.microsoft.com> wrote in message news:EF17F864-6175-438D-A1E1-C2004CC6D4EF@microsoft.com...
>I need a function that will return all information for "truck 1" on another 
> worksheet. The information below is an example of information on my 
> worksheet. I need a function that will search the data and return all 
> instances of "truck 1" on a separate worksheet. 
>    
>     B               C                           D                  E        
> F
>   Grade         Mill                       Driver          Weight   Rate
> 
> 1   ZONE 2       BATSON WILSON      25.00 
> 2   ZONE 3       CAMDEN TROY      26.00 
> 3   ZONE 4       CLW                 TRUCK 1      27.00    7
> 4   18 - 20 top  CORRIGAN TRUCK 2      28.00    8
> 5   Dead Logs   EVADALE TRUCK 3      29.00    9
> 6   PW        GP                      WILSON      30.00 
> 7   CNS        LUFKIN TROY      31.00 
> 8   HW Logs      NAPCO TRUCK 1      32.00    10
> 9   ZONE 2        STONEHAM TRUCK 2      33.00    11
> 10  ZONE 3       BATSON   WILSON       34.00    12
> 11  ZONE 4       CAMDEN   WILSON      35.00 
> 12  18 - 20 top  CLW                   TROY      36.00 
> 13  Dead Logs   CORRIGAN   TRUCK 1      37.00     13
> 14  PW         EVADALE   TRUCK 2      38.00     14
> 15  CNS         GP                   TRUCK 3      39.00       7
> 
> I have an idex function on a separate worksheet and it works for the first 
> line but I need a function that will move to the second line and return the 
> next instance until all instances are returned so I can calculate pay for the 
> driver. Here's what I have:
> =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
> =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
> =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)
> 
> Please help!
0
macropod
12/24/2009 6:22:15 AM
2003

"T. Valko" wrote:

> What version of Excel are you using?
> 
> Unless you're using Excel 2007 your named range can't refer to entire 
> columns and be used in an array formula.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "indyjojo" <indyjojo@discussions.microsoft.com> wrote in message 
> news:2489A99C-95DE-49BE-84D4-C1F137B4B43F@microsoft.com...
> > Thanks so much for your time. I got the blanks but the first five lines
> > return an error. I changed the range names to what I thought would give me
> > the mill name but instead I got "truck 1". I changed the formula to this:
> >
> > =IF(ROWS(A$5:A5)>B$1,"",INDEX(DRIVER_NAME,SMALL(IF(DRIVER_NAME=A$1,ROW(MILL_NAME)),ROWS(A$5:A5))-MIN(ROW(MILL_NAME))+1))
> >
> > The range names are as follows:
> >
> > colC Mill_Name
> > colD Driver_Name
> > colC:F Lease_Driverpay
> >
> > All the information in the range name Lease_Driverpay needs to go over to
> > worksheet 2 but only for truck 1. Once I get a functional formula, can 
> > this
> > be accomplished by copying the formula to the right? or will I have to do 
> > a
> > different formula for each column?
> >
> > Again thank you for your time and assistance.
> >
> > "T. Valko" wrote:
> >
> >> Try this...
> >>
> >> Sheet2 A1 = Truck 1
> >>
> >> Enter this formula on Sheet2 B1:
> >>
> >> =COUNTIF(DRIVER_NAME,A1)
> >>
> >> Enter this array formula** on Sheet2 A2:
> >>
> >> =IF(ROWS(A$2:A2)>B$1,"",INDEX(LEASE_DRIVERPAY,SMALL(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))
> >>
> >> ** array formulas need to be entered using the key combination of
> >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
> >> SHIFT
> >> key then hit ENTER.
> >>
> >> Copy down until you get blanks.
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "indyjojo" <indyjojo@discussions.microsoft.com> wrote in message
> >> news:EF17F864-6175-438D-A1E1-C2004CC6D4EF@microsoft.com...
> >> >I need a function that will return all information for "truck 1" on 
> >> >another
> >> > worksheet. The information below is an example of information on my
> >> > worksheet. I need a function that will search the data and return all
> >> > instances of "truck 1" on a separate worksheet.
> >> >
> >> >     B               C                           D                  E
> >> > F
> >> >   Grade         Mill                       Driver          Weight 
> >> > Rate
> >> >
> >> > 1   ZONE 2       BATSON WILSON      25.00
> >> > 2   ZONE 3       CAMDEN TROY      26.00
> >> > 3   ZONE 4       CLW                 TRUCK 1      27.00    7
> >> > 4   18 - 20 top  CORRIGAN TRUCK 2      28.00    8
> >> > 5   Dead Logs   EVADALE TRUCK 3      29.00    9
> >> > 6   PW        GP                      WILSON      30.00
> >> > 7   CNS        LUFKIN TROY      31.00
> >> > 8   HW Logs      NAPCO TRUCK 1      32.00    10
> >> > 9   ZONE 2        STONEHAM TRUCK 2      33.00    11
> >> > 10  ZONE 3       BATSON   WILSON       34.00    12
> >> > 11  ZONE 4       CAMDEN   WILSON      35.00
> >> > 12  18 - 20 top  CLW                   TROY      36.00
> >> > 13  Dead Logs   CORRIGAN   TRUCK 1      37.00     13
> >> > 14  PW         EVADALE   TRUCK 2      38.00     14
> >> > 15  CNS         GP                   TRUCK 3      39.00       7
> >> >
> >> > I have an idex function on a separate worksheet and it works for the 
> >> > first
> >> > line but I need a function that will move to the second line and return
> >> > the
> >> > next instance until all instances are returned so I can calculate pay 
> >> > for
> >> > the
> >> > driver. Here's what I have:
> >> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
> >> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
> >> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)
> >> >
> >> > Please help!
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
12/24/2009 6:55:01 AM
Thanks for all your help. Hours later, I finally got it to work.

"T. Valko" wrote:

> What version of Excel are you using?
> 
> Unless you're using Excel 2007 your named range can't refer to entire 
> columns and be used in an array formula.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "indyjojo" <indyjojo@discussions.microsoft.com> wrote in message 
> news:2489A99C-95DE-49BE-84D4-C1F137B4B43F@microsoft.com...
> > Thanks so much for your time. I got the blanks but the first five lines
> > return an error. I changed the range names to what I thought would give me
> > the mill name but instead I got "truck 1". I changed the formula to this:
> >
> > =IF(ROWS(A$5:A5)>B$1,"",INDEX(DRIVER_NAME,SMALL(IF(DRIVER_NAME=A$1,ROW(MILL_NAME)),ROWS(A$5:A5))-MIN(ROW(MILL_NAME))+1))
> >
> > The range names are as follows:
> >
> > colC Mill_Name
> > colD Driver_Name
> > colC:F Lease_Driverpay
> >
> > All the information in the range name Lease_Driverpay needs to go over to
> > worksheet 2 but only for truck 1. Once I get a functional formula, can 
> > this
> > be accomplished by copying the formula to the right? or will I have to do 
> > a
> > different formula for each column?
> >
> > Again thank you for your time and assistance.
> >
> > "T. Valko" wrote:
> >
> >> Try this...
> >>
> >> Sheet2 A1 = Truck 1
> >>
> >> Enter this formula on Sheet2 B1:
> >>
> >> =COUNTIF(DRIVER_NAME,A1)
> >>
> >> Enter this array formula** on Sheet2 A2:
> >>
> >> =IF(ROWS(A$2:A2)>B$1,"",INDEX(LEASE_DRIVERPAY,SMALL(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))
> >>
> >> ** array formulas need to be entered using the key combination of
> >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
> >> SHIFT
> >> key then hit ENTER.
> >>
> >> Copy down until you get blanks.
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "indyjojo" <indyjojo@discussions.microsoft.com> wrote in message
> >> news:EF17F864-6175-438D-A1E1-C2004CC6D4EF@microsoft.com...
> >> >I need a function that will return all information for "truck 1" on 
> >> >another
> >> > worksheet. The information below is an example of information on my
> >> > worksheet. I need a function that will search the data and return all
> >> > instances of "truck 1" on a separate worksheet.
> >> >
> >> >     B               C                           D                  E
> >> > F
> >> >   Grade         Mill                       Driver          Weight 
> >> > Rate
> >> >
> >> > 1   ZONE 2       BATSON WILSON      25.00
> >> > 2   ZONE 3       CAMDEN TROY      26.00
> >> > 3   ZONE 4       CLW                 TRUCK 1      27.00    7
> >> > 4   18 - 20 top  CORRIGAN TRUCK 2      28.00    8
> >> > 5   Dead Logs   EVADALE TRUCK 3      29.00    9
> >> > 6   PW        GP                      WILSON      30.00
> >> > 7   CNS        LUFKIN TROY      31.00
> >> > 8   HW Logs      NAPCO TRUCK 1      32.00    10
> >> > 9   ZONE 2        STONEHAM TRUCK 2      33.00    11
> >> > 10  ZONE 3       BATSON   WILSON       34.00    12
> >> > 11  ZONE 4       CAMDEN   WILSON      35.00
> >> > 12  18 - 20 top  CLW                   TROY      36.00
> >> > 13  Dead Logs   CORRIGAN   TRUCK 1      37.00     13
> >> > 14  PW         EVADALE   TRUCK 2      38.00     14
> >> > 15  CNS         GP                   TRUCK 3      39.00       7
> >> >
> >> > I have an idex function on a separate worksheet and it works for the 
> >> > first
> >> > line but I need a function that will move to the second line and return
> >> > the
> >> > next instance until all instances are returned so I can calculate pay 
> >> > for
> >> > the
> >> > driver. Here's what I have:
> >> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
> >> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
> >> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)
> >> >
> >> > Please help!
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
12/24/2009 8:11:01 AM
Good deal. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"indyjojo" <indyjojo@discussions.microsoft.com> wrote in message 
news:8C107865-8210-4CFC-9C74-CFD0045E1EEA@microsoft.com...
> Thanks for all your help. Hours later, I finally got it to work.
>
> "T. Valko" wrote:
>
>> What version of Excel are you using?
>>
>> Unless you're using Excel 2007 your named range can't refer to entire
>> columns and be used in an array formula.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "indyjojo" <indyjojo@discussions.microsoft.com> wrote in message
>> news:2489A99C-95DE-49BE-84D4-C1F137B4B43F@microsoft.com...
>> > Thanks so much for your time. I got the blanks but the first five lines
>> > return an error. I changed the range names to what I thought would give 
>> > me
>> > the mill name but instead I got "truck 1". I changed the formula to 
>> > this:
>> >
>> > =IF(ROWS(A$5:A5)>B$1,"",INDEX(DRIVER_NAME,SMALL(IF(DRIVER_NAME=A$1,ROW(MILL_NAME)),ROWS(A$5:A5))-MIN(ROW(MILL_NAME))+1))
>> >
>> > The range names are as follows:
>> >
>> > colC Mill_Name
>> > colD Driver_Name
>> > colC:F Lease_Driverpay
>> >
>> > All the information in the range name Lease_Driverpay needs to go over 
>> > to
>> > worksheet 2 but only for truck 1. Once I get a functional formula, can
>> > this
>> > be accomplished by copying the formula to the right? or will I have to 
>> > do
>> > a
>> > different formula for each column?
>> >
>> > Again thank you for your time and assistance.
>> >
>> > "T. Valko" wrote:
>> >
>> >> Try this...
>> >>
>> >> Sheet2 A1 = Truck 1
>> >>
>> >> Enter this formula on Sheet2 B1:
>> >>
>> >> =COUNTIF(DRIVER_NAME,A1)
>> >>
>> >> Enter this array formula** on Sheet2 A2:
>> >>
>> >> =IF(ROWS(A$2:A2)>B$1,"",INDEX(LEASE_DRIVERPAY,SMALL(IF(DRIVER_NAME=A$1,ROW(LEASE_DRIVERPAY)),ROWS(A$2:A2))-MIN(ROW(LEASE_DRIVERPAY))+1))
>> >>
>> >> ** array formulas need to be entered using the key combination of
>> >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
>> >> SHIFT
>> >> key then hit ENTER.
>> >>
>> >> Copy down until you get blanks.
>> >>
>> >> -- 
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "indyjojo" <indyjojo@discussions.microsoft.com> wrote in message
>> >> news:EF17F864-6175-438D-A1E1-C2004CC6D4EF@microsoft.com...
>> >> >I need a function that will return all information for "truck 1" on
>> >> >another
>> >> > worksheet. The information below is an example of information on my
>> >> > worksheet. I need a function that will search the data and return 
>> >> > all
>> >> > instances of "truck 1" on a separate worksheet.
>> >> >
>> >> >     B               C                           D                  E
>> >> > F
>> >> >   Grade         Mill                       Driver          Weight
>> >> > Rate
>> >> >
>> >> > 1   ZONE 2       BATSON WILSON      25.00
>> >> > 2   ZONE 3       CAMDEN TROY      26.00
>> >> > 3   ZONE 4       CLW                 TRUCK 1      27.00    7
>> >> > 4   18 - 20 top  CORRIGAN TRUCK 2      28.00    8
>> >> > 5   Dead Logs   EVADALE TRUCK 3      29.00    9
>> >> > 6   PW        GP                      WILSON      30.00
>> >> > 7   CNS        LUFKIN TROY      31.00
>> >> > 8   HW Logs      NAPCO TRUCK 1      32.00    10
>> >> > 9   ZONE 2        STONEHAM TRUCK 2      33.00    11
>> >> > 10  ZONE 3       BATSON   WILSON       34.00    12
>> >> > 11  ZONE 4       CAMDEN   WILSON      35.00
>> >> > 12  18 - 20 top  CLW                   TROY      36.00
>> >> > 13  Dead Logs   CORRIGAN   TRUCK 1      37.00     13
>> >> > 14  PW         EVADALE   TRUCK 2      38.00     14
>> >> > 15  CNS         GP                   TRUCK 3      39.00       7
>> >> >
>> >> > I have an idex function on a separate worksheet and it works for the
>> >> > first
>> >> > line but I need a function that will move to the second line and 
>> >> > return
>> >> > the
>> >> > next instance until all instances are returned so I can calculate 
>> >> > pay
>> >> > for
>> >> > the
>> >> > driver. Here's what I have:
>> >> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),1)
>> >> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),3)
>> >> > =INDEX(LEASE_DRIVERPAY,MATCH("TRUCK 1",DRIVER_NAME,0),4)
>> >> >
>> >> > Please help!
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>> 


0
T
12/24/2009 5:30:27 PM
Reply:

Similar Artilces:

"not count" certain cells in a SUM function
Hi, I have a spreadsheet (attached) that our users use to input numbers such as those in cells BR14:BR19). As you can see, a subtotal is displayed in BR22. Is there a way to do a sum in cell BR41 without manually subtractin out BR22? For example, could I "Not count" BR22 if it were RED or BOLD? I am open to other options. Cell CW41 is particularly onerous as I must subtract out 2 cells. Thx much. lindas Attachment filename: training schedule by programs-short.xls Download attachment: http://www.excelforum.com/attachment.php?postid=65760 -- Message posted from http://www.E...

How do I access a function in a namespace from the main MFC app file?
There is a high chance that I have overlooked something simple in my quest to make this program work. Here is the sitrep.... I have a class that contains functions to interact with a USB hardware interface kit. The class creates an object of the interface kit type and has all of the functions to open & close the device, pull data from it, and set data to it. The class itself is derived from an abstract class with a bunch of virtual functions in it (included for future upgrades). All of this is no problem. Now what I am trying to do is create an MFC GUI to control it and display data. I&#...

Is there a search tool that will index internet headers on Outlook email?
I'd like to be able to search on strings in the internet header as well as on strings in Subject, Message, etc. The otherwise admirable X1 doesn't do it. Does anybody know of a search/indexing tool that will? Thanks! Frank Frank Denman Denman Systems news@denmansystemsx.com [Please delete the "x" from my email address] ...

4 way postcard, right side print problems
I selected the 4 cards per sheet option. the 2 left side cards are perfect, the 2 right side cards cut the far right edge off in print preview and in actual print. I'm using Publisher 2003. thanks, seuss This is a limitation of your printer margins. Determine what your printer margins are and create your postcards, both left and right within that margin if you want them to all look the same. Tutorial: Finding your Maximum Print Area http://ed.mvps.org/Static.aspx?=Publisher/horidiag -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft...

Problem creating contract in MS CRM 3
Hi, For the development for a customer a connection must be made between an administration program and MS CRM 3. >From the administration program I receive xml that I can easily parse. >From that XML I have to create accounts, resellers (new entity I created), contracts and contractdetails. Creating the accounts and resellers works well. I used DynamicEntity for this, because I declare the mapping of the fields in a seperate XML file. When I try to create contracts, I receive the following Exception: {System.Web.Services.Protocols.SoapException: Server was unable to process request. ...

Outlook2002 snych problem
I havelooked error on microsoft site but to no avail have i corrected problem. trying to synch outlook 2002 to my offline folder hit F9 and get the follow error. I have change macro security to medium, i know there is a limit for offline synch but i'm not even close.....appreciate the help..your fried boritech1 Task "Microsoft Exchange Server' reported error (0x00040820): Error in background synchronization" Do you have a Synch Issues folder in your Folder List that you can open to troubleshoot? -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://w...

CStatic and painting problems : bis
Hi, I am rewriting a very simple CStatic derived control when I can display a bitmap that fits the control so I started with the code below : The problem is I cannot see my text or my bitmap in function I declare one before the other. It seems I am mixing some DC but I cannot find where // CxStatic.cpp : implementation file // #include "stdafx.h" #include "TestCxStatic.h" #include "CxStatic.h" // CxStatic IMPLEMENT_DYNAMIC(CxStatic, CStatic) CxStatic::CxStatic():CStatic(), m_hBitmap(NULL) { m_eImgMode = FitControl; m_pFont = NULL; m_clrText = :...

Recipient Sync problem
Hi, I have about 12 Exchange 5.5 servers cross the WAN. I have created a test mailbox on server A, but I cannot see the new mailbox in the global recipients container when I connected to 4 servers. I have created another test mailbox on server B, then I can see it in global recipients container when I connected to all the servers. I did IS/DS consistency, CKC, manually updated directory the problems remained. What could be wrong? How to fix it? Thanks Are all these servers in the same site? If not...you need to setup a directory replication connector between the sites. Good luck...

Which Function(s) should I use?
Hi All, I want to generate 5 lists using the preference column from raw data which is in the following format: Preference Name Club 1 A Smith NY 2 G Murphy WN 3 S Rogers MA 4 B Wall CN 5 C Hill DW 1 D Thomas ON 2 W Shatner CA 3 E Jones MS 4 L Long SC 5 J Downe NC Can you please advise which functions I should use to collate the data, also allowing me to cha...

Excel import problems
How do you get Excel 2003 to import more han 16,351 lines ...

Call a local function from a property page?
I would like to give my users a "test" button on a property page to allow them to try a set of parameters before commiting to them. How can I call a function from a property page? There would be no interaction with the function but if possible, I would like to get a returned parameter. Ron H. It depends where the function is. If it's in the page class then just call it. If it's in the document class then you'll need a pointer to that. You'll have to give more details if you need further help. "Ron H" <rnh@no.spamm.net> wrote in message news:B...

Problem with running Office:Mac 2008 Installer
I'm curious about what gets installed by the Office 2008 Special Media Edition installer AFTER it runs the installer script near the end that puts icons of the applications in the Dock. My Office Mac 2008 installer crashed viciously at that point and took out the Finder with it so I had to do a hard restart to get my computer running again. (2.8 GHz Intel iMac, Tiger 10.4.11). I don't want to do a complete re-install of Office 2008 but I'm wondering if anything important might be missing. The applications all seem to work OK. When I installed Office: mac 2008, I got an error messa...

Function Help #5
I have a spreadsheet that has the following columns and rows: ReceiptId ReceiptOwner TransactionDescription Debit Credit 62470 Person Name Bank Draft Payment 64 62470 Person Name Unity Transaction 64 Person Name Count 2 1 I would like to write a function or macro or something that tests to see that both the Bank Draft Payment and Unity Transaction are available to the "Person Name Count". If both are not available, then I would like to write the Debit value into a separate column to the right o...

Still Having CControlBar Problems
I am still having a lot of problems with some CControlBar windows in my MDI application. I derived a window class (CDockingWnd) from the CSizingControlBar class available from http://www.datamekanix.com/. CDockingWnd contains a child window (CDialogTabCtrl) derived from CTabCtrl. The CDialogTabCtrl has several child windows derived from CDialog. Each of the child dialog windows are shown or hidden depending one which tab is selected in the CDialogTabCtrl class. Everything is working okay except for the following problems: 1. My application's keyboard accelerators do not work when one o...

Publisher Color Printing problem
My brochure which I have printed many times before now will only print in B/W. It shows in color but not in Priint preview or in actual print. Can anyone help? Thanks Janet Might be a corrupted driver. Go to the manufacturer's web site and see if there is an updated printer driver. If not, uninstall your printer and reinstall It is always better to completely uninstall your printer before reinstalling/installing. Bruce Sanderson's Windows Web How to clean up printer drivers http://members.shaw.ca/bsanders/CleanPrinterDrivers.htm -- Mary Sauer MSFT MVP http://office.micros...

Problems with report formatting ing SSRS 2005
Hi, I haven't been using SSRS for more than a few months, so please forgive me if these are old questions... but I haven't been able to figure them out for myself as yet. 1) I'm trying to use the Table Property "Header should remain visible while scrolling". However the data rows remain visible through the header as they scroll up behind it. Does anyone know how to stop this? 2) I have several money fields in the detail row of the table, and I want to subtotal them in the group footer. However its not subtotalling, rather its just repeating the am...

Access 2003 Form Wizard and Toggle Button Confusion/Problems
I am trying to use the Access 2003 Form Wizard to create a subform that will response to a toggle button that I created and to display that subform within my main form. However, I do not get the wizard window that is suppose to allow me to indicate that I want this to be a linked form so that it will appear within my main form (I see this example in a book that I have). Nor can I figure out how to manually create a link to that subform so that it is associated to the toggle buttion that I created so that it will appear within my main form. (I figured out how to write simple code so ...

Problem downloading Great Plains Standard Release 8.0 Service Pack 3
Just curious if anyone is having problems downloading GP80 Service Pack 3? I'll be upgrading our 7.0 to 8.0 soon and I'm trying to get the latest service packs. I did leave a message on their email: VOICE@microsoft.com but no response from them. https://mbs.microsoft.com/customersource/support/downloads/servicepacks/GP80ServicePack3.htm Thanks. Kevin Turned out to be a browser setting. I did hear from 'VOICE' and received the instructions I needed. Thanks 'VOICE'! Keivn "Kevin R" <russellk@darden.virginia.edu> wrote in message news:euU...

exch 2000 Problem sending internal email HELP!!!!!!!!!
for some reason everybody in my company can not send emails to each other. Also when i check the Spam filter and release all of the good mail it comes into our server but never goes to the person it is supposed to go to. any ideas? Thanks, Jason. Turn on message tracking and find out where it is getting "stuck" Nue "Jason" <Jason@discussions.microsoft.com> wrote in message news:909404AD-9A75-4CA3-A4DC-ADB00A4C7643@microsoft.com... > for some reason everybody in my company can not send emails to each other. > Also when i check the Spam filter and release...

Problem "Going Offline" with Dynamics CRM 3.0, SBS 2003sp1 and XPs
My Configuration - all with the latest updates applied: Small Business Server 2003sp1 Exchange 2003sp2 SQL Server 2000sp4 Dynamics CRM 3.0 - with update rollup 1 Windows XPsp2 Desktop Windows XPsp2 Laptop Outlook 2003sp2 Hotfix KB_931270 installed - needed to stop Outlook from crashing on both the Desktop & Laptop. This is a fresh install of Dynamics CRM with 1 client and 33 contacts - NO other data. Problem: Attempts to have CRM "Go Offline" are unsuccessfull. "Failed move data for entity 'Note' during action salnsert, countRows=1. Contact your system adm...

Publishing to the Web Problem
I have created a web site approx 400 pages using Publisher 2003. It will not load during its creation I was saving to a cd so that I could work on it on the lap top. Saved it back on to main pc to load on to the web and it will not load. If I try to load it as ftp it comes up as cannot save read only file followed by another error message check url and connection. If I try to load it as http it comes up it comes up as check url and connection. Frontpage extensions are on and I have 500mb of space. I have managed to load smaller sites as soon as I cut and paste any of the original s...

Mail merge problems with Outlook security update
Hi there, I've recently installed some security updates to Office 2000 via windows Update, and now when running an (email) Mail Merge, it askes me if I want to allow this action *for each & every recipient*! even if I check the 'allow this action' box for 10 mins. I've changed the Macro security setting, but this makes no difference. Can anyone help? thanks in advance, Jamie Your options are all outlined here: http://www.slipstick.com/outlook/esecup.htm#autosec -- Russ Valentine [MVP-Outlook] "Work Hard" <enquiries@workhardpr.com> wrote in message news:...

Problem on Outlook 2000 send email using contact
Some of my contacts had more then one email address. Any method I can send my email to all three email address on one contact person? Thanks a lot, Amon ...

VB range name problem!
hi everyone, I have a problem where I have macro code where i want to refer to a range name but excel won't recognise what i want it to do unless i put the cell reference in there. The code is listed below, cell I175 is range named "cash". any ideas? If Target.Address = ("$I$175") Then If Range("cash").Value <> 1 Then Range("debt_terms").Select Selection.EntireRow.Hidden = False Range("cash").Select Else If Range("cash").Value = 1 Then Range("debt_terms").Select Selection.EntireRow.Hidden = Tru...

problem with CDatabase, CRecordset, stored procedure
Hi; i have a problem with this sequence 1 CRecordset is used to open a database and retrieve records. 2 Database is closed. 3. PreparedStatement StoredProcedure is executed in same database and works. Output parameters are used. 4. CRecordset is used to open same database and retrieve records 4. fails on .Open() (Unknown ODBC exception) , it works however if the StoredProcedure is not executed. (The StoredProcedure involves complex column binding code to retrieve output columns, as explained on some web sites.). Do you have hints on what to look at. best regards bav ...