Tracking Sheet help.

 Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the 
amount of time's that I've made an entry of time on sheet 2. The times I 
enter on sheet 2 are used to track vehicles that are passing a set location. 
On sheet 2 I enter the times in column D. The vehicle's I count have numbers 
to ID them in column F.
The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of 
the number I need to associate with my count is the first four digits, 401N, 
401S. 
Is it possible to do this?

0
DaleG (36)
7/27/2008 4:28:00 PM
excel.newusers 15348 articles. 1 followers. Follow

11 Replies
567 Views

Similar Articles

[PageSpeed] 30

Yep, use a sumproduct function to count using the left 4 digits as the 
criteria.  Here's a step-by-step.  The formula would look something like:

SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14)) 

where B14 was the 4 digits you wanted to count by.

http://www.kan.org/tips/excel_sumproduct_advanced2.php

-- 
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

>  Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the 
> amount of time's that I've made an entry of time on sheet 2. The times I 
> enter on sheet 2 are used to track vehicles that are passing a set location. 
> On sheet 2 I enter the times in column D. The vehicle's I count have numbers 
> to ID them in column F.
> The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of 
> the number I need to associate with my count is the first four digits, 401N, 
> 401S. 
> Is it possible to do this?
> 
0
Utf
7/27/2008 4:46:00 PM
 Example of sheet 3

  A            B            C           D           
 401n		
 401s	1	
 402n		
 402s	3	
 404n		
 404s		
 405n		
 405s		



 Example
 Run #     Vehicle 	Time	Actual	Diff 	Schedule	
 924	0	4:48			535S0448	
 208	0	5:13			401S0459	
 904	0	5:18		                535S0518	
 908	0	5:25			511S0518	
 706	0	5:36			401S0522	
 224	0	5:48			535S0548	
 708	0	5:49			401S0535	
 

 her are some examples. I'm not sure how to make that work. when I make an 
 entry in Sheet 2 column D, I need sheet 3 column B to count the entry, 
according 
 to the numers in column A of sheet 3 and column F of sheet 2. 401N, 401S.




"M Kan" wrote:

> Yep, use a sumproduct function to count using the left 4 digits as the 
> criteria.  Here's a step-by-step.  The formula would look something like:
> 
> SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14)) 
> 
> where B14 was the 4 digits you wanted to count by.
> 
> http://www.kan.org/tips/excel_sumproduct_advanced2.php
> 
> -- 
> Tips for Excel, Word, PowerPoint and Other Applications
> http://www.kan.org/tips
> 
> 
> "Dale G" wrote:
> 
> >  Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the 
> > amount of time's that I've made an entry of time on sheet 2. The times I 
> > enter on sheet 2 are used to track vehicles that are passing a set location. 
> > On sheet 2 I enter the times in column D. The vehicle's I count have numbers 
> > to ID them in column F.
> > The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of 
> > the number I need to associate with my count is the first four digits, 401N, 
> > 401S. 
> > Is it possible to do this?
> > 
0
DaleG (36)
7/27/2008 5:15:00 PM
I think:

SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(NOT(ISBLANK(Sheet2!$D$5:$D$100)))

I hope I got all of my parens right.  This would count cells that have 
entries and where the left 4 characters matches your tracking sheet

-- 
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

>  Example of sheet 3
> 
>   A            B            C           D           
>  401n		
>  401s	1	
>  402n		
>  402s	3	
>  404n		
>  404s		
>  405n		
>  405s		
> 
> 
> 
>  Example
>  Run #     Vehicle 	Time	Actual	Diff 	Schedule	
>  924	0	4:48			535S0448	
>  208	0	5:13			401S0459	
>  904	0	5:18		                535S0518	
>  908	0	5:25			511S0518	
>  706	0	5:36			401S0522	
>  224	0	5:48			535S0548	
>  708	0	5:49			401S0535	
>  
> 
>  her are some examples. I'm not sure how to make that work. when I make an 
>  entry in Sheet 2 column D, I need sheet 3 column B to count the entry, 
> according 
>  to the numers in column A of sheet 3 and column F of sheet 2. 401N, 401S.
> 
> 
> 
> 
> "M Kan" wrote:
> 
> > Yep, use a sumproduct function to count using the left 4 digits as the 
> > criteria.  Here's a step-by-step.  The formula would look something like:
> > 
> > SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14)) 
> > 
> > where B14 was the 4 digits you wanted to count by.
> > 
> > http://www.kan.org/tips/excel_sumproduct_advanced2.php
> > 
> > -- 
> > Tips for Excel, Word, PowerPoint and Other Applications
> > http://www.kan.org/tips
> > 
> > 
> > "Dale G" wrote:
> > 
> > >  Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the 
> > > amount of time's that I've made an entry of time on sheet 2. The times I 
> > > enter on sheet 2 are used to track vehicles that are passing a set location. 
> > > On sheet 2 I enter the times in column D. The vehicle's I count have numbers 
> > > to ID them in column F.
> > > The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of 
> > > the number I need to associate with my count is the first four digits, 401N, 
> > > 401S. 
> > > Is it possible to do this?
> > > 
0
Utf
7/27/2008 5:44:00 PM
That works very well, Thank You so much.  
I wonder if this could go a step further. I use column C of sheet 3 for the 
count, if the time I enter (on sheet 2, column D) = greater than 10+ minutes 
difference in column E sheet 2 (Diff). Is this possible?


 Example sheet 3

 Route	0-10	+10
 401n	0	
 401s	0	
 402n	0	
 402s	0	
 404n	0	
 404s	0	



 Example sheet 2

     A            B                 C            D             E             
 F
 Run 	Vehicle	Time	Actual	Diff 	Schedule
 924	0	4:48	4:59	0:11	535S0448
 208	0	5:13	 	 	401S0459
 904	0	5:18	 	 	535S0518
 908	0	5:25	 	 	511S0518
					
Maybe the formulas can be set to column E from sheet 2 ?					
					
					


"M Kan" wrote:

> I think:
> 
> SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(NOT(ISBLANK(Sheet2!$D$5:$D$100)))
> 
> I hope I got all of my parens right.  This would count cells that have 
> entries and where the left 4 characters matches your tracking sheet
> 
> -- 
> Tips for Excel, Word, PowerPoint and Other Applications
> http://www.kan.org/tips
> 
> 
> "Dale G" wrote:
> 
> >  Example of sheet 3
> > 
> >   A            B            C           D           
> >  401n		
> >  401s	1	
> >  402n		
> >  402s	3	
> >  404n		
> >  404s		
> >  405n		
> >  405s		
> > 
> > 
> > 
> >  Example
> >  Run #     Vehicle 	Time	Actual	Diff 	Schedule	
> >  924	0	4:48			535S0448	
> >  208	0	5:13			401S0459	
> >  904	0	5:18		                535S0518	
> >  908	0	5:25			511S0518	
> >  706	0	5:36			401S0522	
> >  224	0	5:48			535S0548	
> >  708	0	5:49			401S0535	
> >  
> > 
> >  her are some examples. I'm not sure how to make that work. when I make an 
> >  entry in Sheet 2 column D, I need sheet 3 column B to count the entry, 
> > according 
> >  to the numers in column A of sheet 3 and column F of sheet 2. 401N, 401S.
> > 
> > 
> > 
> > 
> > "M Kan" wrote:
> > 
> > > Yep, use a sumproduct function to count using the left 4 digits as the 
> > > criteria.  Here's a step-by-step.  The formula would look something like:
> > > 
> > > SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14)) 
> > > 
> > > where B14 was the 4 digits you wanted to count by.
> > > 
> > > http://www.kan.org/tips/excel_sumproduct_advanced2.php
> > > 
> > > -- 
> > > Tips for Excel, Word, PowerPoint and Other Applications
> > > http://www.kan.org/tips
> > > 
> > > 
> > > "Dale G" wrote:
> > > 
> > > >  Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the 
> > > > amount of time's that I've made an entry of time on sheet 2. The times I 
> > > > enter on sheet 2 are used to track vehicles that are passing a set location. 
> > > > On sheet 2 I enter the times in column D. The vehicle's I count have numbers 
> > > > to ID them in column F.
> > > > The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of 
> > > > the number I need to associate with my count is the first four digits, 401N, 
> > > > 401S. 
> > > > Is it possible to do this?
> > > > 
0
DaleG (36)
7/27/2008 6:39:18 PM
Not sure what the time format is, but yes.  just add another condition that 
evaluates the difference <= :10 for the first metric and modify this for the 
second column such that the difference is >10
-- 
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

> That works very well, Thank You so much.  
> I wonder if this could go a step further. I use column C of sheet 3 for the 
> count, if the time I enter (on sheet 2, column D) = greater than 10+ minutes 
> difference in column E sheet 2 (Diff). Is this possible?
> 
> 
>  Example sheet 3
> 
>  Route	0-10	+10
>  401n	0	
>  401s	0	
>  402n	0	
>  402s	0	
>  404n	0	
>  404s	0	
> 
> 
> 
>  Example sheet 2
> 
>      A            B                 C            D             E             
>  F
>  Run 	Vehicle	Time	Actual	Diff 	Schedule
>  924	0	4:48	4:59	0:11	535S0448
>  208	0	5:13	 	 	401S0459
>  904	0	5:18	 	 	535S0518
>  908	0	5:25	 	 	511S0518
> 					
> Maybe the formulas can be set to column E from sheet 2 ?					
> 					
> 					
> 
> 
> "M Kan" wrote:
> 
> > I think:
> > 
> > SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(NOT(ISBLANK(Sheet2!$D$5:$D$100)))
> > 
> > I hope I got all of my parens right.  This would count cells that have 
> > entries and where the left 4 characters matches your tracking sheet
> > 
> > -- 
> > Tips for Excel, Word, PowerPoint and Other Applications
> > http://www.kan.org/tips
> > 
> > 
> > "Dale G" wrote:
> > 
> > >  Example of sheet 3
> > > 
> > >   A            B            C           D           
> > >  401n		
> > >  401s	1	
> > >  402n		
> > >  402s	3	
> > >  404n		
> > >  404s		
> > >  405n		
> > >  405s		
> > > 
> > > 
> > > 
> > >  Example
> > >  Run #     Vehicle 	Time	Actual	Diff 	Schedule	
> > >  924	0	4:48			535S0448	
> > >  208	0	5:13			401S0459	
> > >  904	0	5:18		                535S0518	
> > >  908	0	5:25			511S0518	
> > >  706	0	5:36			401S0522	
> > >  224	0	5:48			535S0548	
> > >  708	0	5:49			401S0535	
> > >  
> > > 
> > >  her are some examples. I'm not sure how to make that work. when I make an 
> > >  entry in Sheet 2 column D, I need sheet 3 column B to count the entry, 
> > > according 
> > >  to the numers in column A of sheet 3 and column F of sheet 2. 401N, 401S.
> > > 
> > > 
> > > 
> > > 
> > > "M Kan" wrote:
> > > 
> > > > Yep, use a sumproduct function to count using the left 4 digits as the 
> > > > criteria.  Here's a step-by-step.  The formula would look something like:
> > > > 
> > > > SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14)) 
> > > > 
> > > > where B14 was the 4 digits you wanted to count by.
> > > > 
> > > > http://www.kan.org/tips/excel_sumproduct_advanced2.php
> > > > 
> > > > -- 
> > > > Tips for Excel, Word, PowerPoint and Other Applications
> > > > http://www.kan.org/tips
> > > > 
> > > > 
> > > > "Dale G" wrote:
> > > > 
> > > > >  Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the 
> > > > > amount of time's that I've made an entry of time on sheet 2. The times I 
> > > > > enter on sheet 2 are used to track vehicles that are passing a set location. 
> > > > > On sheet 2 I enter the times in column D. The vehicle's I count have numbers 
> > > > > to ID them in column F.
> > > > > The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of 
> > > > > the number I need to associate with my count is the first four digits, 401N, 
> > > > > 401S. 
> > > > > Is it possible to do this?
> > > > > 
0
Utf
7/27/2008 9:36:27 PM
That sounds good, but i'm not sure were to place those in the formula.

"M Kan" wrote:

> Not sure what the time format is, but yes.  just add another condition that 
> evaluates the difference <= :10 for the first metric and modify this for the 
> second column such that the difference is >10
> -- 
> Tips for Excel, Word, PowerPoint and Other Applications
> http://www.kan.org/tips
> 
> 
> "Dale G" wrote:
> 
> > That works very well, Thank You so much.  
> > I wonder if this could go a step further. I use column C of sheet 3 for the 
> > count, if the time I enter (on sheet 2, column D) = greater than 10+ minutes 
> > difference in column E sheet 2 (Diff). Is this possible?
> > 
> > 
> >  Example sheet 3
> > 
> >  Route	0-10	+10
> >  401n	0	
> >  401s	0	
> >  402n	0	
> >  402s	0	
> >  404n	0	
> >  404s	0	
> > 
> > 
> > 
> >  Example sheet 2
> > 
> >      A            B                 C            D             E             
> >  F
> >  Run 	Vehicle	Time	Actual	Diff 	Schedule
> >  924	0	4:48	4:59	0:11	535S0448
> >  208	0	5:13	 	 	401S0459
> >  904	0	5:18	 	 	535S0518
> >  908	0	5:25	 	 	511S0518
> > 					
> > Maybe the formulas can be set to column E from sheet 2 ?					
> > 					
> > 					
> > 
> > 
> > "M Kan" wrote:
> > 
> > > I think:
> > > 
> > > SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(NOT(ISBLANK(Sheet2!$D$5:$D$100)))
> > > 
> > > I hope I got all of my parens right.  This would count cells that have 
> > > entries and where the left 4 characters matches your tracking sheet
> > > 
> > > -- 
> > > Tips for Excel, Word, PowerPoint and Other Applications
> > > http://www.kan.org/tips
> > > 
> > > 
> > > "Dale G" wrote:
> > > 
> > > >  Example of sheet 3
> > > > 
> > > >   A            B            C           D           
> > > >  401n		
> > > >  401s	1	
> > > >  402n		
> > > >  402s	3	
> > > >  404n		
> > > >  404s		
> > > >  405n		
> > > >  405s		
> > > > 
> > > > 
> > > > 
> > > >  Example
> > > >  Run #     Vehicle 	Time	Actual	Diff 	Schedule	
> > > >  924	0	4:48			535S0448	
> > > >  208	0	5:13			401S0459	
> > > >  904	0	5:18		                535S0518	
> > > >  908	0	5:25			511S0518	
> > > >  706	0	5:36			401S0522	
> > > >  224	0	5:48			535S0548	
> > > >  708	0	5:49			401S0535	
> > > >  
> > > > 
> > > >  her are some examples. I'm not sure how to make that work. when I make an 
> > > >  entry in Sheet 2 column D, I need sheet 3 column B to count the entry, 
> > > > according 
> > > >  to the numers in column A of sheet 3 and column F of sheet 2. 401N, 401S.
> > > > 
> > > > 
> > > > 
> > > > 
> > > > "M Kan" wrote:
> > > > 
> > > > > Yep, use a sumproduct function to count using the left 4 digits as the 
> > > > > criteria.  Here's a step-by-step.  The formula would look something like:
> > > > > 
> > > > > SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14)) 
> > > > > 
> > > > > where B14 was the 4 digits you wanted to count by.
> > > > > 
> > > > > http://www.kan.org/tips/excel_sumproduct_advanced2.php
> > > > > 
> > > > > -- 
> > > > > Tips for Excel, Word, PowerPoint and Other Applications
> > > > > http://www.kan.org/tips
> > > > > 
> > > > > 
> > > > > "Dale G" wrote:
> > > > > 
> > > > > >  Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the 
> > > > > > amount of time's that I've made an entry of time on sheet 2. The times I 
> > > > > > enter on sheet 2 are used to track vehicles that are passing a set location. 
> > > > > > On sheet 2 I enter the times in column D. The vehicle's I count have numbers 
> > > > > > to ID them in column F.
> > > > > > The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of 
> > > > > > the number I need to associate with my count is the first four digits, 401N, 
> > > > > > 401S. 
> > > > > > Is it possible to do this?
> > > > > > 
0
DaleG (36)
7/27/2008 11:49:12 PM
Just as I thought, I don't know where to place <= :10 or >10. Could you 
please assist me with the placement. I definitely need to take some classes 
on Excel, I love how this stuff works.

"M Kan" wrote:

> Not sure what the time format is, but yes.  just add another condition that 
> evaluates the difference <= :10 for the first metric and modify this for the 
> second column such that the difference is >10
> -- 
> Tips for Excel, Word, PowerPoint and Other Applications
> http://www.kan.org/tips
> 
> 
> "Dale G" wrote:
> 
> > That works very well, Thank You so much.  
> > I wonder if this could go a step further. I use column C of sheet 3 for the 
> > count, if the time I enter (on sheet 2, column D) = greater than 10+ minutes 
> > difference in column E sheet 2 (Diff). Is this possible?
> > 
> > 
> >  Example sheet 3
> > 
> >  Route	0-10	+10
> >  401n	0	
> >  401s	0	
> >  402n	0	
> >  402s	0	
> >  404n	0	
> >  404s	0	
> > 
> > 
> > 
> >  Example sheet 2
> > 
> >      A            B                 C            D             E             
> >  F
> >  Run 	Vehicle	Time	Actual	Diff 	Schedule
> >  924	0	4:48	4:59	0:11	535S0448
> >  208	0	5:13	 	 	401S0459
> >  904	0	5:18	 	 	535S0518
> >  908	0	5:25	 	 	511S0518
> > 					
> > Maybe the formulas can be set to column E from sheet 2 ?					
> > 					
> > 					
> > 
> > 
> > "M Kan" wrote:
> > 
> > > I think:
> > > 
> > > SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(NOT(ISBLANK(Sheet2!$D$5:$D$100)))
> > > 
> > > I hope I got all of my parens right.  This would count cells that have 
> > > entries and where the left 4 characters matches your tracking sheet
> > > 
> > > -- 
> > > Tips for Excel, Word, PowerPoint and Other Applications
> > > http://www.kan.org/tips
> > > 
> > > 
> > > "Dale G" wrote:
> > > 
> > > >  Example of sheet 3
> > > > 
> > > >   A            B            C           D           
> > > >  401n		
> > > >  401s	1	
> > > >  402n		
> > > >  402s	3	
> > > >  404n		
> > > >  404s		
> > > >  405n		
> > > >  405s		
> > > > 
> > > > 
> > > > 
> > > >  Example
> > > >  Run #     Vehicle 	Time	Actual	Diff 	Schedule	
> > > >  924	0	4:48			535S0448	
> > > >  208	0	5:13			401S0459	
> > > >  904	0	5:18		                535S0518	
> > > >  908	0	5:25			511S0518	
> > > >  706	0	5:36			401S0522	
> > > >  224	0	5:48			535S0548	
> > > >  708	0	5:49			401S0535	
> > > >  
> > > > 
> > > >  her are some examples. I'm not sure how to make that work. when I make an 
> > > >  entry in Sheet 2 column D, I need sheet 3 column B to count the entry, 
> > > > according 
> > > >  to the numers in column A of sheet 3 and column F of sheet 2. 401N, 401S.
> > > > 
> > > > 
> > > > 
> > > > 
> > > > "M Kan" wrote:
> > > > 
> > > > > Yep, use a sumproduct function to count using the left 4 digits as the 
> > > > > criteria.  Here's a step-by-step.  The formula would look something like:
> > > > > 
> > > > > SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14)) 
> > > > > 
> > > > > where B14 was the 4 digits you wanted to count by.
> > > > > 
> > > > > http://www.kan.org/tips/excel_sumproduct_advanced2.php
> > > > > 
> > > > > -- 
> > > > > Tips for Excel, Word, PowerPoint and Other Applications
> > > > > http://www.kan.org/tips
> > > > > 
> > > > > 
> > > > > "Dale G" wrote:
> > > > > 
> > > > > >  Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the 
> > > > > > amount of time's that I've made an entry of time on sheet 2. The times I 
> > > > > > enter on sheet 2 are used to track vehicles that are passing a set location. 
> > > > > > On sheet 2 I enter the times in column D. The vehicle's I count have numbers 
> > > > > > to ID them in column F.
> > > > > > The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of 
> > > > > > the number I need to associate with my count is the first four digits, 401N, 
> > > > > > 401S. 
> > > > > > Is it possible to do this?
> > > > > > 
0
DaleG (36)
7/29/2008 12:09:00 AM
I had a little difficulty with the time.  One thing you can do is to enter 
the time in a separate cell and then refer to it in the sumproduct.
-- 
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

> Just as I thought, I don't know where to place <= :10 or >10. Could you 
> please assist me with the placement. I definitely need to take some classes 
> on Excel, I love how this stuff works.
> 
> "M Kan" wrote:
> 
> > Not sure what the time format is, but yes.  just add another condition that 
> > evaluates the difference <= :10 for the first metric and modify this for the 
> > second column such that the difference is >10
> > -- 
> > Tips for Excel, Word, PowerPoint and Other Applications
> > http://www.kan.org/tips
> > 
> > 
> > "Dale G" wrote:
> > 
> > > That works very well, Thank You so much.  
> > > I wonder if this could go a step further. I use column C of sheet 3 for the 
> > > count, if the time I enter (on sheet 2, column D) = greater than 10+ minutes 
> > > difference in column E sheet 2 (Diff). Is this possible?
> > > 
> > > 
> > >  Example sheet 3
> > > 
> > >  Route	0-10	+10
> > >  401n	0	
> > >  401s	0	
> > >  402n	0	
> > >  402s	0	
> > >  404n	0	
> > >  404s	0	
> > > 
> > > 
> > > 
> > >  Example sheet 2
> > > 
> > >      A            B                 C            D             E             
> > >  F
> > >  Run 	Vehicle	Time	Actual	Diff 	Schedule
> > >  924	0	4:48	4:59	0:11	535S0448
> > >  208	0	5:13	 	 	401S0459
> > >  904	0	5:18	 	 	535S0518
> > >  908	0	5:25	 	 	511S0518
> > > 					
> > > Maybe the formulas can be set to column E from sheet 2 ?					
> > > 					
> > > 					
> > > 
> > > 
> > > "M Kan" wrote:
> > > 
> > > > I think:
> > > > 
> > > > SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(NOT(ISBLANK(Sheet2!$D$5:$D$100)))
> > > > 
> > > > I hope I got all of my parens right.  This would count cells that have 
> > > > entries and where the left 4 characters matches your tracking sheet
> > > > 
> > > > -- 
> > > > Tips for Excel, Word, PowerPoint and Other Applications
> > > > http://www.kan.org/tips
> > > > 
> > > > 
> > > > "Dale G" wrote:
> > > > 
> > > > >  Example of sheet 3
> > > > > 
> > > > >   A            B            C           D           
> > > > >  401n		
> > > > >  401s	1	
> > > > >  402n		
> > > > >  402s	3	
> > > > >  404n		
> > > > >  404s		
> > > > >  405n		
> > > > >  405s		
> > > > > 
> > > > > 
> > > > > 
> > > > >  Example
> > > > >  Run #     Vehicle 	Time	Actual	Diff 	Schedule	
> > > > >  924	0	4:48			535S0448	
> > > > >  208	0	5:13			401S0459	
> > > > >  904	0	5:18		                535S0518	
> > > > >  908	0	5:25			511S0518	
> > > > >  706	0	5:36			401S0522	
> > > > >  224	0	5:48			535S0548	
> > > > >  708	0	5:49			401S0535	
> > > > >  
> > > > > 
> > > > >  her are some examples. I'm not sure how to make that work. when I make an 
> > > > >  entry in Sheet 2 column D, I need sheet 3 column B to count the entry, 
> > > > > according 
> > > > >  to the numers in column A of sheet 3 and column F of sheet 2. 401N, 401S.
> > > > > 
> > > > > 
> > > > > 
> > > > > 
> > > > > "M Kan" wrote:
> > > > > 
> > > > > > Yep, use a sumproduct function to count using the left 4 digits as the 
> > > > > > criteria.  Here's a step-by-step.  The formula would look something like:
> > > > > > 
> > > > > > SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14)) 
> > > > > > 
> > > > > > where B14 was the 4 digits you wanted to count by.
> > > > > > 
> > > > > > http://www.kan.org/tips/excel_sumproduct_advanced2.php
> > > > > > 
> > > > > > -- 
> > > > > > Tips for Excel, Word, PowerPoint and Other Applications
> > > > > > http://www.kan.org/tips
> > > > > > 
> > > > > > 
> > > > > > "Dale G" wrote:
> > > > > > 
> > > > > > >  Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the 
> > > > > > > amount of time's that I've made an entry of time on sheet 2. The times I 
> > > > > > > enter on sheet 2 are used to track vehicles that are passing a set location. 
> > > > > > > On sheet 2 I enter the times in column D. The vehicle's I count have numbers 
> > > > > > > to ID them in column F.
> > > > > > > The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of 
> > > > > > > the number I need to associate with my count is the first four digits, 401N, 
> > > > > > > 401S. 
> > > > > > > Is it possible to do this?
> > > > > > > 
0
Utf
7/29/2008 5:06:01 AM
So far this works in column B.

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10"))


And this for column C. 

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192>=--"0:10"))


On sheet 3 column B This counts all the left nubers that match from sheet 2, 
then if the difference is more then 10 minutes it subtracts 1 from that count 
and places 1 in column C. I like the way it works, but it defeats the purpose 
of (NOT(ISBLANK(.
Is there a way to keep the (NOT(ISBLANK in the formula. (Notice I've changed 
the column that the entry info comes from). Also column E in sheet 2 is set 
up to - the difference from D & C,(=D-C). So it has entry's of ### until the 
enrty is made in D.  



   
"M Kan" wrote:

> I had a little difficulty with the time.  One thing you can do is to enter 
> the time in a separate cell and then refer to it in the sumproduct.
> -- 
> Tips for Excel, Word, PowerPoint and Other Applications
> http://www.kan.org/tips
> 
> 
> "Dale G" wrote:
> 
> > Just as I thought, I don't know where to place <= :10 or >10. Could you 
> > please assist me with the placement. I definitely need to take some classes 
> > on Excel, I love how this stuff works.
> > 
> > "M Kan" wrote:
> > 
> > > Not sure what the time format is, but yes.  just add another condition that 
> > > evaluates the difference <= :10 for the first metric and modify this for the 
> > > second column such that the difference is >10
> > > -- 
> > > Tips for Excel, Word, PowerPoint and Other Applications
> > > http://www.kan.org/tips
> > > 
> > > 
> > > "Dale G" wrote:
> > > 
> > > > That works very well, Thank You so much.  
> > > > I wonder if this could go a step further. I use column C of sheet 3 for the 
> > > > count, if the time I enter (on sheet 2, column D) = greater than 10+ minutes 
> > > > difference in column E sheet 2 (Diff). Is this possible?
> > > > 
> > > > 
> > > >  Example sheet 3
> > > > 
> > > >  Route	0-10	+10
> > > >  401n	0	
> > > >  401s	0	
> > > >  402n	0	
> > > >  402s	0	
> > > >  404n	0	
> > > >  404s	0	
> > > > 
> > > > 
> > > > 
> > > >  Example sheet 2
> > > > 
> > > >      A            B                 C            D             E             
> > > >  F
> > > >  Run 	Vehicle	Time	Actual	Diff 	Schedule
> > > >  924	0	4:48	4:59	0:11	535S0448
> > > >  208	0	5:13	 	 	401S0459
> > > >  904	0	5:18	 	 	535S0518
> > > >  908	0	5:25	 	 	511S0518
> > > > 					
> > > > Maybe the formulas can be set to column E from sheet 2 ?					
> > > > 					
> > > > 					
> > > > 
> > > > 
> > > > "M Kan" wrote:
> > > > 
> > > > > I think:
> > > > > 
> > > > > SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(NOT(ISBLANK(Sheet2!$D$5:$D$100)))
> > > > > 
> > > > > I hope I got all of my parens right.  This would count cells that have 
> > > > > entries and where the left 4 characters matches your tracking sheet
> > > > > 
> > > > > -- 
> > > > > Tips for Excel, Word, PowerPoint and Other Applications
> > > > > http://www.kan.org/tips
> > > > > 
> > > > > 
> > > > > "Dale G" wrote:
> > > > > 
> > > > > >  Example of sheet 3
> > > > > > 
> > > > > >   A            B            C           D           
> > > > > >  401n		
> > > > > >  401s	1	
> > > > > >  402n		
> > > > > >  402s	3	
> > > > > >  404n		
> > > > > >  404s		
> > > > > >  405n		
> > > > > >  405s		
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > >  Example
> > > > > >  Run #     Vehicle 	Time	Actual	Diff 	Schedule	
> > > > > >  924	0	4:48			535S0448	
> > > > > >  208	0	5:13			401S0459	
> > > > > >  904	0	5:18		                535S0518	
> > > > > >  908	0	5:25			511S0518	
> > > > > >  706	0	5:36			401S0522	
> > > > > >  224	0	5:48			535S0548	
> > > > > >  708	0	5:49			401S0535	
> > > > > >  
> > > > > > 
> > > > > >  her are some examples. I'm not sure how to make that work. when I make an 
> > > > > >  entry in Sheet 2 column D, I need sheet 3 column B to count the entry, 
> > > > > > according 
> > > > > >  to the numers in column A of sheet 3 and column F of sheet 2. 401N, 401S.
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > > "M Kan" wrote:
> > > > > > 
> > > > > > > Yep, use a sumproduct function to count using the left 4 digits as the 
> > > > > > > criteria.  Here's a step-by-step.  The formula would look something like:
> > > > > > > 
> > > > > > > SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14)) 
> > > > > > > 
> > > > > > > where B14 was the 4 digits you wanted to count by.
> > > > > > > 
> > > > > > > http://www.kan.org/tips/excel_sumproduct_advanced2.php
> > > > > > > 
> > > > > > > -- 
> > > > > > > Tips for Excel, Word, PowerPoint and Other Applications
> > > > > > > http://www.kan.org/tips
> > > > > > > 
> > > > > > > 
> > > > > > > "Dale G" wrote:
> > > > > > > 
> > > > > > > >  Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the 
> > > > > > > > amount of time's that I've made an entry of time on sheet 2. The times I 
> > > > > > > > enter on sheet 2 are used to track vehicles that are passing a set location. 
> > > > > > > > On sheet 2 I enter the times in column D. The vehicle's I count have numbers 
> > > > > > > > to ID them in column F.
> > > > > > > > The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of 
> > > > > > > > the number I need to associate with my count is the first four digits, 401N, 
> > > > > > > > 401S. 
> > > > > > > > Is it possible to do this?
> > > > > > > > 
0
DaleG (36)
7/29/2008 11:45:01 PM
Thank you for your reply. Between you, & M KAN, & Bob P, & others I came up 
with something very close to your sugestion. 
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10"))
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192>=--"0:10"))
  Thanks for your help. As you might have notice I'm still working on it.   


"Roger Govier" wrote:

> Hi Dale
> 
> Try
> SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(Sheet2!$D$5:$D$100>TIME(0,10,0))
> 
> TIME specifies (hours,minutes,seconds)
> -- 
> Regards
> Roger Govier
> 
> "Dale G" <DaleG@discussions.microsoft.com> wrote in message 
> news:9A330518-0DF3-4D25-97AA-822D876DA19A@microsoft.com...
> > Just as I thought, I don't know where to place <= :10 or >10. Could you
> > please assist me with the placement. I definitely need to take some 
> > classes
> > on Excel, I love how this stuff works.
> >
> > "M Kan" wrote:
> >
> >> Not sure what the time format is, but yes.  just add another condition 
> >> that
> >> evaluates the difference <= :10 for the first metric and modify this for 
> >> the
> >> second column such that the difference is >10
> >> -- 
> >> Tips for Excel, Word, PowerPoint and Other Applications
> >> http://www.kan.org/tips
> >>
> >>
> >> "Dale G" wrote:
> >>
> >> > That works very well, Thank You so much.
> >> > I wonder if this could go a step further. I use column C of sheet 3 for 
> >> > the
> >> > count, if the time I enter (on sheet 2, column D) = greater than 10+ 
> >> > minutes
> >> > difference in column E sheet 2 (Diff). Is this possible?
> >> >
> >> >
> >> >  Example sheet 3
> >> >
> >> >  Route 0-10 +10
> >> >  401n 0
> >> >  401s 0
> >> >  402n 0
> >> >  402s 0
> >> >  404n 0
> >> >  404s 0
> >> >
> >> >
> >> >
> >> >  Example sheet 2
> >> >
> >> >      A            B                 C            D             E
> >> >  F
> >> >  Run Vehicle Time Actual Diff Schedule
> >> >  924 0 4:48 4:59 0:11 535S0448
> >> >  208 0 5:13 401S0459
> >> >  904 0 5:18 535S0518
> >> >  908 0 5:25 511S0518
> >> >
> >> > Maybe the formulas can be set to column E from sheet 2 ?
> >> >
> >> >
> >> >
> >> >
> >> > "M Kan" wrote:
> >> >
> >> > > I think:
> >> > >
> >> > > SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(NOT(ISBLANK(Sheet2!$D$5:$D$100)))
> >> > >
> >> > > I hope I got all of my parens right.  This would count cells that 
> >> > > have
> >> > > entries and where the left 4 characters matches your tracking sheet
> >> > >
> >> > > -- 
> >> > > Tips for Excel, Word, PowerPoint and Other Applications
> >> > > http://www.kan.org/tips
> >> > >
> >> > >
> >> > > "Dale G" wrote:
> >> > >
> >> > > >  Example of sheet 3
> >> > > >
> >> > > >   A            B            C           D
> >> > > >  401n
> >> > > >  401s 1
> >> > > >  402n
> >> > > >  402s 3
> >> > > >  404n
> >> > > >  404s
> >> > > >  405n
> >> > > >  405s
> >> > > >
> >> > > >
> >> > > >
> >> > > >  Example
> >> > > >  Run #     Vehicle Time Actual Diff Schedule
> >> > > >  924 0 4:48 535S0448
> >> > > >  208 0 5:13 401S0459
> >> > > >  904 0 5:18                 535S0518
> >> > > >  908 0 5:25 511S0518
> >> > > >  706 0 5:36 401S0522
> >> > > >  224 0 5:48 535S0548
> >> > > >  708 0 5:49 401S0535
> >> > > >
> >> > > >
> >> > > >  her are some examples. I'm not sure how to make that work. when I 
> >> > > > make an
> >> > > >  entry in Sheet 2 column D, I need sheet 3 column B to count the 
> >> > > > entry,
> >> > > > according
> >> > > >  to the numers in column A of sheet 3 and column F of sheet 2. 
> >> > > > 401N, 401S.
> >> > > >
> >> > > >
> >> > > >
> >> > > >
> >> > > > "M Kan" wrote:
> >> > > >
> >> > > > > Yep, use a sumproduct function to count using the left 4 digits 
> >> > > > > as the
> >> > > > > criteria.  Here's a step-by-step.  The formula would look 
> >> > > > > something like:
> >> > > > >
> >> > > > > SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14))
> >> > > > >
> >> > > > > where B14 was the 4 digits you wanted to count by.
> >> > > > >
> >> > > > > http://www.kan.org/tips/excel_sumproduct_advanced2.php
> >> > > > >
> >> > > > > -- 
> >> > > > > Tips for Excel, Word, PowerPoint and Other Applications
> >> > > > > http://www.kan.org/tips
> >> > > > >
> >> > > > >
> >> > > > > "Dale G" wrote:
> >> > > > >
> >> > > > > >  Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 
> >> > > > > > 3) I enter the
> >> > > > > > amount of time's that I've made an entry of time on sheet 2. 
> >> > > > > > The times I
> >> > > > > > enter on sheet 2 are used to track vehicles that are passing a 
> >> > > > > > set location.
> >> > > > > > On sheet 2 I enter the times in column D. The vehicle's I count 
> >> > > > > > have numbers
> >> > > > > > to ID them in column F.
> >> > > > > > The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. 
> >> > > > > > The part of
> >> > > > > > the number I need to associate with my count is the first four 
> >> > > > > > digits, 401N,
> >> > > > > > 401S.
> >> > > > > > Is it possible to do this?
> >> > > > > > 
0
DaleG (36)
7/30/2008 12:00:01 AM
I guess I spoke to soon, there is some problem with using this formula. when 
the difference = 10 it won't place the count in the right column. I tried to 
fix it, but was not successful. Any sugesstion? 

Example sheet 3 (OPT)


   A            B               C

 Route	0-10	+10
 401n	0                0	
 401s	0                0	
 402n	0                0	
 402s	0                0	
 404n	0                0	
 535s	0                1	



 Example sheet 2(LTC)

     A            B                 C            D             E             F
 

Run 	Vehicle	Time	Actual	Diff 	Schedule
 924	0	4:48	4:59	0:11	535S0448
 208	0	5:13	5:23         0:10 	401S0459
 904	0	5:18	5:28         0:10	535S0518
 908	0	5:25	 	 	511S0518

=IF(D3="","",D3-C3)
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10"),--(LTC!$E$3:$E$192<>""))
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192>=--"0:10"),--(LTC!$E$3:$E$192<>""))




"Dale G" wrote:

> Thank you for your reply. Between you, & M KAN, & Bob P, & others I came up 
> with something very close to your sugestion. 
> =SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10"))
> =SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192>=--"0:10"))
>   Thanks for your help. As you might have notice I'm still working on it.   
> 
> 
> "Roger Govier" wrote:
> 
> > Hi Dale
> > 
> > Try
> > SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(Sheet2!$D$5:$D$100>TIME(0,10,0))
> > 
> > TIME specifies (hours,minutes,seconds)
> > -- 
> > Regards
> > Roger Govier
> > 
> > "Dale G" <DaleG@discussions.microsoft.com> wrote in message 
> > news:9A330518-0DF3-4D25-97AA-822D876DA19A@microsoft.com...
> > > Just as I thought, I don't know where to place <= :10 or >10. Could you
> > > please assist me with the placement. I definitely need to take some 
> > > classes
> > > on Excel, I love how this stuff works.
> > >
> > > "M Kan" wrote:
> > >
> > >> Not sure what the time format is, but yes.  just add another condition 
> > >> that
> > >> evaluates the difference <= :10 for the first metric and modify this for 
> > >> the
> > >> second column such that the difference is >10
> > >> -- 
> > >> Tips for Excel, Word, PowerPoint and Other Applications
> > >> http://www.kan.org/tips
> > >>
> > >>
> > >> "Dale G" wrote:
> > >>
> > >> > That works very well, Thank You so much.
> > >> > I wonder if this could go a step further. I use column C of sheet 3 for 
> > >> > the
> > >> > count, if the time I enter (on sheet 2, column D) = greater than 10+ 
> > >> > minutes
> > >> > difference in column E sheet 2 (Diff). Is this possible?
> > >> >
> > >> >
> > >> >  Example sheet 3
> > >> >
> > >> >  Route 0-10 +10
> > >> >  401n 0
> > >> >  401s 0
> > >> >  402n 0
> > >> >  402s 0
> > >> >  404n 0
> > >> >  404s 0
> > >> >
> > >> >
> > >> >
> > >> >  Example sheet 2
> > >> >
> > >> >      A            B                 C            D             E
> > >> >  F
> > >> >  Run Vehicle Time Actual Diff Schedule
> > >> >  924 0 4:48 4:59 0:11 535S0448
> > >> >  208 0 5:13 401S0459
> > >> >  904 0 5:18 535S0518
> > >> >  908 0 5:25 511S0518
> > >> >
> > >> > Maybe the formulas can be set to column E from sheet 2 ?
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > "M Kan" wrote:
> > >> >
> > >> > > I think:
> > >> > >
> > >> > > SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(NOT(ISBLANK(Sheet2!$D$5:$D$100)))
> > >> > >
> > >> > > I hope I got all of my parens right.  This would count cells that 
> > >> > > have
> > >> > > entries and where the left 4 characters matches your tracking sheet
> > >> > >
> > >> > > -- 
> > >> > > Tips for Excel, Word, PowerPoint and Other Applications
> > >> > > http://www.kan.org/tips
> > >> > >
> > >> > >
> > >> > > "Dale G" wrote:
> > >> > >
> > >> > > >  Example of sheet 3
> > >> > > >
> > >> > > >   A            B            C           D
> > >> > > >  401n
> > >> > > >  401s 1
> > >> > > >  402n
> > >> > > >  402s 3
> > >> > > >  404n
> > >> > > >  404s
> > >> > > >  405n
> > >> > > >  405s
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > >  Example
> > >> > > >  Run #     Vehicle Time Actual Diff Schedule
> > >> > > >  924 0 4:48 535S0448
> > >> > > >  208 0 5:13 401S0459
> > >> > > >  904 0 5:18                 535S0518
> > >> > > >  908 0 5:25 511S0518
> > >> > > >  706 0 5:36 401S0522
> > >> > > >  224 0 5:48 535S0548
> > >> > > >  708 0 5:49 401S0535
> > >> > > >
> > >> > > >
> > >> > > >  her are some examples. I'm not sure how to make that work. when I 
> > >> > > > make an
> > >> > > >  entry in Sheet 2 column D, I need sheet 3 column B to count the 
> > >> > > > entry,
> > >> > > > according
> > >> > > >  to the numers in column A of sheet 3 and column F of sheet 2. 
> > >> > > > 401N, 401S.
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > > "M Kan" wrote:
> > >> > > >
> > >> > > > > Yep, use a sumproduct function to count using the left 4 digits 
> > >> > > > > as the
> > >> > > > > criteria.  Here's a step-by-step.  The formula would look 
> > >> > > > > something like:
> > >> > > > >
> > >> > > > > SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14))
> > >> > > > >
> > >> > > > > where B14 was the 4 digits you wanted to count by.
> > >> > > > >
> > >> > > > > http://www.kan.org/tips/excel_sumproduct_advanced2.php
> > >> > > > >
> > >> > > > > -- 
> > >> > > > > Tips for Excel, Word, PowerPoint and Other Applications
> > >> > > > > http://www.kan.org/tips
> > >> > > > >
> > >> > > > >
> > >> > > > > "Dale G" wrote:
> > >> > > > >
> > >> > > > > >  Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 
> > >> > > > > > 3) I enter the
> > >> > > > > > amount of time's that I've made an entry of time on sheet 2. 
> > >> > > > > > The times I
> > >> > > > > > enter on sheet 2 are used to track vehicles that are passing a 
> > >> > > > > > set location.
> > >> > > > > > On sheet 2 I enter the times in column D. The vehicle's I count 
> > >> > > > > > have numbers
> > >> > > > > > to ID them in column F.
> > >> > > > > > The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. 
> > >> > > > > > The part of
> > >> > > > > > the number I need to associate with my count is the first four 
> > >> > > > > > digits, 401N,
> > >> > > > > > 401S.
> > >> > > > > > Is it possible to do this?
> > >> > > > > > 
0
DaleG (36)
8/7/2008 1:48:01 AM
Reply:

Similar Artilces:

Background or Header picture should cover entire A4 sheet when pri
Hi, I don't even know if what I need help with can be done, but here goes: I have a picture, .bmp, that I would like to appear at the top of my printed Excelsheet. The challenge is to get the picture to cover the entire sheet with NO margins. The top of the printed paper should not show any white margins at all. I've tried to use both the Header and the Background-function but solve it... Does anyone know if and how it can be done? BR //Jenny I'm using the 2007 version //J "Jenny" wrote: > Hi, > > I don't even know if what I nee...

Template Wizard with Data Tracking
I have been using this add-in just fine for about a year, gathering data from about 20 users onto an Excel Database by having them all use the template. But I recently made some changes and then began having the following problem: After a worksheet has been created using the template, and saved as a new record in the database, and then we want to go back in to change the record and update it in the database, the option does not appear to update the database; the only options continue to be to create a new record or to proceed without saving to the database. It is as though the sys...

sort wrork sheets
i need to sort some work sheets by alpha, i know how to sort on a work sheet but not how to sort several work sheets in a file try these Sub SortALLSheetsbyName() 'McRitchie 'sort sheets within a workbook in Excel 7 -- Bill Manville 'modified to sort all sheets instead of just worksheets Dim iSheet As Integer, iBefore As Integer For iSheet = 1 To ActiveWorkbook.Sheets.Count Sheets(iSheet).Visible = True For iBefore = 1 To iSheet - 1 If UCase(Sheets(iBefore).Name) > UCase(Sheets(iSheet).Name) Then ActiveWorkbook.Sheets(iSheet).Move Before:=Ac...

Message Tracking #6
What is the best way to check if your exchange sometimes doesn't accepts mail (delay) or doesn't send mails correctly (delay) Message Tracking ? But how do you actually do this Kind regards Guillaume Coign� "Guillaume Coign�" <guillaume.coigne@trust-it.be> wrote: >What is the best way to check if your exchange sometimes doesn't accepts >mail (delay) or doesn't send mails correctly (delay) Message Tracking ? >But how do you actually do this Assuming you're asking about messages leaving your organization, the SMTP protocol logs are the place to ...

2003 Style Sheets in 2007?
I received a new computer from work, which has Office 2K7 on it. I copied Normal.dot off my backup disc onto this machine, and renamed Normal.dotm to Normal.dotmold. My Macros and keyboard shortcuts came over fine. My "Normal" style is fine. But I had a whole whole bunch of other styles, along with keyboard shortcuts for those styles, that seem to have disappeared! I thought I'd try the Organizer and move the styles over from old documents, but that did not work either. I have been fine-tuning Normal.dot since I gave up MacWrite in 1994... But this, this, t...

I want to email only 1 page of my excel sheet how do I do that
I have an excel sheet that has three pages to it. I want to be able to email only one of the pages to a client. Is there any way I can do that? You can send the selection or range Maureen See my site for a example http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Maureen" <Maureen@discussions.microsoft.com> wrote in message news:EB8883BB-90FB-424D-AC6A-D7899C1C5F96@microsoft.com... >I have an excel sheet that has three pages to it. I want to be able to email > only one of the pages to a client. Is there any way I can do that? ...

Tracking GST/VAT in MSMoney Standard
I'm new to Money & processing small business accounts. Had no problems tracking & reporting VAT in Quicken & havegot Money to calculate VAT with each relevant entry. Somehow just not finding a report that shows VAT totals, taxable income etc. Please HELP!!!!! ...

I Need Help in how to alter Item Quantity display
This is what we want to do. during the transaction, when the same item is scanned more than once, RMS will automatically increase the quantity of the item instead of creating a new line everytime the item is scanned. I will be happy for any response. and i will be able to keep my job. thank you! Hello RMS Slave for life, I am just a slave like you that has been looking for some time for a few add ons that would make our life a little easier, that been said you may want to take a look at http://syssolutionsllc.com and look for "Same item control" add on that looks like it will...

Need Outlook Help
OK, I'm a new user from XP to 7. I used Outlook Express and it was fine for me needs. However, I'm finding Windows 7 to be overbearing and I can't seem to figure out what to do for my email needs. Here's the issue... I'm not setting up various user accounts, which runs counter to Microsofts heavy hand. The problem I'm having is that one normal user account (I have an admin account for managing the computer only) and Outlook for numerous profiles doesn't work. I don't want to have Outlook open directly into my wife's inbox and then ask for a pas...

Track Changes compatibility between versions of Excel
I'm planning to undertake a project which involves Excel sheets created in Excel 2003 and tracking the changes made to those sheets via Excel 97. Does anybody know of any problems with the compatibility of this function? This would help me out a lot. Thanks. ...

How do I track half days in the attendance tracking template?
I've just created an attendance tracking template in Excel, however realized that there isn't an option to track half or quarter days taken off. Does anyone have any suggestions? A lot depends on how you've laid out the data. Instead of tracking whole days, can you track hours? 8 for a full day, 4 for a half day, etc. ...

Help
Hi- I am trying to use the "save as" command to save my Publisher 2002 file as an image file (I've tried jpg, tif, gif and bmp) which will be acceptable to an advertisement firm. Unfortunately, they cannot accept MS Publisher files, so I must save it as an image. I have two problems/questions that may actually be the same underlying problem.The first is that when I save the file as one of the image files above, it heavily distorts/blurs some of the fonts to the point that it is unusable. Curiously, in some places the fonts convert fine whereas in other parts of the document the ...

Outlook 2000: Any way to track Calendar access
I've opened up my Calendar for anyone to read in Outlook 2000. Is there any way to track who accesses it? ...

VBE
First of all, I'd like to say how great this board is, and how well it has given me help over the last 6 or 7 months with building small programs with vbe. My problem is this, I need a program that is far in advance of my own skills, its basically a stock control program for steel coils. The Fields are: Material - ComboBox1 :- This will choose from categories CR, GALV, IZ, Zintec, ALuminised etc. Width - Textbox1 :- This is where you type in the width you are looking for or wanting to add. Gauge - Textbox2 :- This is where you type in a gauge (Something like 0.95 or 1.15) and it show...

.mny password issue (Help!)
I copied my MS Money file onto a USB jump drive from my desktop PC to bring it over to my laptop. When I try to open the file on my laptop, I get a prompt to enter password. I did not (knowingly anyway) set a password to protect this file. Now I'm on the road, in need of accessing this file being asked for a password and, without having set one, I seem to be out of luck. Has anyone heard of a password being set by itself and/or do you have any suggestions for any workarounds? Thanks for your help You must have the same version of Money on both PCs. If your desktop is M2004 and your ...

Textbox object event
HI Everyone... I've been struggling with this for awhile and still don't understand why this won't work. Any help in making it work would be appreciate... 1. Code adds frame containing textbox with events for the textbox to worksheet. This works. 2. Class changes text property of textbox to "aaa" when textbox added. Text gets displayed using TB_Change() event. This works. 3. If I change the text in textbox to something else, the textbox change event doesn't fire to display changed text. --------- Worksheet ----------- Private mcolEvents...

How to track meetings for a task?
How would you suggest to charge meeting time to a my Timesheet task? TIA! Depends on if it's a planned meeting or informal. I typically just create a bucket task through the entire schedule for "Meetings" or book it to PM time. You may also opt to create a distinct line item bucket task for status meetings as opposed to other ad hoc meetings. If it's a big phase gate meeting, I may list that specifically as a line item, but for anything less than that, it becomes too granular for effective management. - Andrew Lavinsky Blog: http://blogs.catapultsystems...

Hiding and unhiding sheets
I want to know how to unhide multiple sheets. I know how to highlight and hide multiple sheets, but how do you highlight more than one sheet to unhide it. Is that even posible You'll need to use a macro. Sub Macro1() For Each Sheet In Sheets On Error Resume Next Sheet.Visible = True Next End Sub If you need to know how to make the macro work get back to us. -- Greetings from New Zealand Bill K "D.Hay" <D.Hay@discussions.microsoft.com> wrote in message news:2602EEA4-0E8E-4EA2-B069-F4C85DA1CD94@microsoft.com... >I want to know how to unhide multiple sheets. I k...

Merging different sheets
hello everybody, I have to work with 2 sheets. In the 1st one my data are arranged as follows: Payment Date Type 15/02/2010 XS 15/02/2010 LS 16/02/2010 XS .... ... and in the 2nd sheet: Task Activity XS Activity1 XS Activity2 XS Activity3 LS Activity4 LS Activity5 .... .... and I want to create in a new sheet the following set of data Payment Date Type Activity 15/02/2010 XS Activity1 15/02/2010 XS Activity2 15/02/2010 XS Activity3 15/02/2010 LS Activity4 15/02/2010 LS Activity5 16/02/2010 XS Activity1 16/02/2010 XS Ac...

Box Plot Help
Hello!!!, I have a table with two columns.. I want to box plot this table in excel. Could anyone help me about the steps involved in plotting this box plot in Excel. Any help would be grealy appreciated... Thank you, Swetha Jon Peltier has some instructions on his web site: http://www.geocities.com/jonpeltier/Excel/Charts/ComboCharts.html#BoxWhisker Swetha wrote: > I have a table with two columns.. I want to box plot this > table in excel. Could anyone help me about the steps > involved in plotting this box plot in Excel. -- Debra Dalgleish Excel FAQ, Tips & Book ...

HELP needed with Exchange 2003
I need to clean up and maybe reinstall but not sure...here's the situation: We had an Exchange Server 2003 installed on a DC. This was in our LAB's LAN. The ONLY thing on this DC was Exchange. The server died. Kaput. We went on with life for a while then decided to revisit testing Exchange (this is a native Windows 2003 server LAN). A new server was brought in with Windows 2003 server installed. It was promoted to a DC. The name is DIFFERENT from the old server (now called Exchg1) The OLD server was named EXCHANGE1. After running NTDSUTIL to remove references to EXCHANGE1 we reinsta...

Help! I'm losing one series of data in an overlay chart.
I have 5 series of data, one is different from the first four. I create them all in a column chart then edit the one different series to be a combination line on the column chart with a secondary axis. When I do that I lose one whole data series of the 4 similar ones. It is recognized by excel but not plotted. It seems almost like excel only wants to plot 3 series as a column and the others have to be a line, but I know this chart is possible, I've seen it elsewhere. Help! Instead of changing the chart to a combination line, try changing the series that you want to show as a l...

Hook Help
I am trying to figure out if there is a way to find out what the tenderentryid after a transaction is posted to the database. Any help would be greatly appreciated. Thanks, Dan ...

Entering data from a userform into multiple sheets
I have created a userform. And depending on the value of a particular drop down menu, the data will be entered into multiple sheets. I have used an if statement to attempt this, but i get an error when i run it. (run-time error '438': Object doesn't support this property or method) Please assist me with this, i am brand new to programming. Thanks in Advance. My code: Private Sub savdata_Click() ' set form to workbook ActiveWorkbook.Sheets("Feb2010").Activate Range("A1").Select 'find and empty cell...

track Shipment
Is it possible to set up the Track Shipment so when you click on TRACK it is automatically bring up a browser with the tracking information? For UPS what will be the URL to set up? ...