Matching data in different sheets but slight difference in time va

Hi 
Please help.
I have two sheets as follows:

Sheet 1:
Date             / Time          / Number Dailled         / Duration      
/Cost
22/1/10         10:00           01234123456               00:01:57       0.789

Sheet 2:
Date             / Time          / Number Dailled         / Duration      
/Extension 
22/1/10         10:02           01234123456               00:01:56       1501

I am trying to put the cost of the phone call against the relevant extension 
on sheet 2 that has dialled the number dialled.  Obviously I can match the 
number dialled in both cases, and the date, but there may be calls made to 
that same number by that same extension on that same date but at a different 
time, so I need to incorporate the time into the formula, to say if the date 
matches and the number dialled matches, and the time difference between the 
two is (say) less than 3 minutes (as that is about the difference I am 
looking at) then give me the cost of that call in the column next to the 
extension number on Sheet 2.

Sorry for the waffle but can anybody help.
Thank you

0
Utf
2/19/2010 11:40:01 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
633 Views

Similar Articles

[PageSpeed] 57

If I understand the question, this might work for you
=SUMPRODUCT(--(Sheet1!A2:A10=A2),--(Sheet1!C2:C10=C2),--(Sheet1!B2:B10>=B2-TIME(0,3,0)),--(Sheet1!B2:B10<=B2+TIME(0,3,0)),Sheet1!E2:E10)

I put you data in row 1 for the two sheets  (with headers) and made up some 
extra data down to row 10. You will need to adjust the ranges. Unless you 
have Excel 2007, SUMPRODUCT cannot use full column ranges as in A:A. The 
third and fourth terms specify that the times in Sheet1 must be within 
plus/minus 3 mins of the time in Sheet2

best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Twiggy" <Twiggy@discussions.microsoft.com> wrote in message 
news:0F84B24F-E1F2-458B-861C-7A22F828A9E7@microsoft.com...
> Hi
> Please help.
> I have two sheets as follows:
>
> Sheet 1:
> Date             / Time          / Number Dailled         / Duration
> /Cost
> 22/1/10         10:00           01234123456               00:01:57 
> 0.789
>
> Sheet 2:
> Date             / Time          / Number Dailled         / Duration
> /Extension
> 22/1/10         10:02           01234123456               00:01:56 
> 1501
>
> I am trying to put the cost of the phone call against the relevant 
> extension
> on sheet 2 that has dialled the number dialled.  Obviously I can match the
> number dialled in both cases, and the date, but there may be calls made to
> that same number by that same extension on that same date but at a 
> different
> time, so I need to incorporate the time into the formula, to say if the 
> date
> matches and the number dialled matches, and the time difference between 
> the
> two is (say) less than 3 minutes (as that is about the difference I am
> looking at) then give me the cost of that call in the column next to the
> extension number on Sheet 2.
>
> Sorry for the waffle but can anybody help.
> Thank you
> 
0
Bernard
2/19/2010 12:43:18 PM
Hi Bernard

Many thanks for your help but I am getting an #Value!?

Sheet 1
Call start date	Call start time  Number	Duration	Cost
28/01/2010	11:47:16	0800100607	00:01:58	£0.15
22/01/2010	13:15:57	0800420421	00:00:07	£0.00
22/01/2010	16:52:15	0800602090	00:00:44	£0.00
26/01/2010	16:12:08	0800800160	00:00:54	£0.00
15/01/2010	11:21:28	01132223222	00:00:52	£0.01

Sheet 2 (there is a sum total in row 2)
Start	   Time	Telephone No	Duration   First Rang	
28/01/2010  11:49:46	0800100607	117	1011	#VALUE!
22/01/2010  13:19:26	0800420421	6	1510	
22/01/2010  16:55:40	0800602090	44	1545	
26/01/2010  16:15:44	0800800160	53	1513	
15/01/2010  11:24:41	01132221234	51	1032	
29/01/2010  12:16:31	01132345678	41	1557

This is my formula:
=SUMPRODUCT(--(Sheet1!A3:A4350=A2),--(Sheet1!C3:C4350=C2),--(Sheet1!B2:B4350>=B2-TIME(0,3,0)),--(Sheet1!B2:B4350<=B2+TIME(0,3,0)),Sheet1!E2:E4350)	

where am i going wrong. There is a lot more data in Sheet2 so they dont 
follow in the same order on both sheets.

"Bernard Liengme" wrote:

> If I understand the question, this might work for you
> =SUMPRODUCT(--(Sheet1!A2:A10=A2),--(Sheet1!C2:C10=C2),--(Sheet1!B2:B10>=B2-TIME(0,3,0)),--(Sheet1!B2:B10<=B2+TIME(0,3,0)),Sheet1!E2:E10)
> 
> I put you data in row 1 for the two sheets  (with headers) and made up some 
> extra data down to row 10. You will need to adjust the ranges. Unless you 
> have Excel 2007, SUMPRODUCT cannot use full column ranges as in A:A. The 
> third and fourth terms specify that the times in Sheet1 must be within 
> plus/minus 3 mins of the time in Sheet2
> 
> best wishes
> -- 
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> 
> "Twiggy" <Twiggy@discussions.microsoft.com> wrote in message 
> news:0F84B24F-E1F2-458B-861C-7A22F828A9E7@microsoft.com...
> > Hi
> > Please help.
> > I have two sheets as follows:
> >
> > Sheet 1:
> > Date             / Time          / Number Dailled         / Duration
> > /Cost
> > 22/1/10         10:00           01234123456               00:01:57 
> > 0.789
> >
> > Sheet 2:
> > Date             / Time          / Number Dailled         / Duration
> > /Extension
> > 22/1/10         10:02           01234123456               00:01:56 
> > 1501
> >
> > I am trying to put the cost of the phone call against the relevant 
> > extension
> > on sheet 2 that has dialled the number dialled.  Obviously I can match the
> > number dialled in both cases, and the date, but there may be calls made to
> > that same number by that same extension on that same date but at a 
> > different
> > time, so I need to incorporate the time into the formula, to say if the 
> > date
> > matches and the number dialled matches, and the time difference between 
> > the
> > two is (say) less than 3 minutes (as that is about the difference I am
> > looking at) then give me the cost of that call in the column next to the
> > extension number on Sheet 2.
> >
> > Sorry for the waffle but can anybody help.
> > Thank you
> > 
> .
> 
0
Utf
2/20/2010 9:11:01 PM
Hi Bernard

I think I have done it, but I cannot get it to calculate!
The worksheet is about 2665kb in size but when I press the f9 to calculate 
the "calculating:(2 processor(s)):0%" doesnt move - left it for 15 mins.
Is that my memory on my machine or something else?

"Twiggy" wrote:

> Hi Bernard
> 
> Many thanks for your help but I am getting an #Value!?
> 
> Sheet 1
> Call start date	Call start time  Number	Duration	Cost
> 28/01/2010	11:47:16	0800100607	00:01:58	£0.15
> 22/01/2010	13:15:57	0800420421	00:00:07	£0.00
> 22/01/2010	16:52:15	0800602090	00:00:44	£0.00
> 26/01/2010	16:12:08	0800800160	00:00:54	£0.00
> 15/01/2010	11:21:28	01132223222	00:00:52	£0.01
> 
> Sheet 2 (there is a sum total in row 2)
> Start	   Time	Telephone No	Duration   First Rang	
> 28/01/2010  11:49:46	0800100607	117	1011	#VALUE!
> 22/01/2010  13:19:26	0800420421	6	1510	
> 22/01/2010  16:55:40	0800602090	44	1545	
> 26/01/2010  16:15:44	0800800160	53	1513	
> 15/01/2010  11:24:41	01132221234	51	1032	
> 29/01/2010  12:16:31	01132345678	41	1557
> 
> This is my formula:
> =SUMPRODUCT(--(Sheet1!A3:A4350=A2),--(Sheet1!C3:C4350=C2),--(Sheet1!B2:B4350>=B2-TIME(0,3,0)),--(Sheet1!B2:B4350<=B2+TIME(0,3,0)),Sheet1!E2:E4350)	
> 
> where am i going wrong. There is a lot more data in Sheet2 so they dont 
> follow in the same order on both sheets.
> 
> "Bernard Liengme" wrote:
> 
> > If I understand the question, this might work for you
> > =SUMPRODUCT(--(Sheet1!A2:A10=A2),--(Sheet1!C2:C10=C2),--(Sheet1!B2:B10>=B2-TIME(0,3,0)),--(Sheet1!B2:B10<=B2+TIME(0,3,0)),Sheet1!E2:E10)
> > 
> > I put you data in row 1 for the two sheets  (with headers) and made up some 
> > extra data down to row 10. You will need to adjust the ranges. Unless you 
> > have Excel 2007, SUMPRODUCT cannot use full column ranges as in A:A. The 
> > third and fourth terms specify that the times in Sheet1 must be within 
> > plus/minus 3 mins of the time in Sheet2
> > 
> > best wishes
> > -- 
> > Bernard Liengme
> > Microsoft Excel MVP
> > http://people.stfx.ca/bliengme
> > 
> > "Twiggy" <Twiggy@discussions.microsoft.com> wrote in message 
> > news:0F84B24F-E1F2-458B-861C-7A22F828A9E7@microsoft.com...
> > > Hi
> > > Please help.
> > > I have two sheets as follows:
> > >
> > > Sheet 1:
> > > Date             / Time          / Number Dailled         / Duration
> > > /Cost
> > > 22/1/10         10:00           01234123456               00:01:57 
> > > 0.789
> > >
> > > Sheet 2:
> > > Date             / Time          / Number Dailled         / Duration
> > > /Extension
> > > 22/1/10         10:02           01234123456               00:01:56 
> > > 1501
> > >
> > > I am trying to put the cost of the phone call against the relevant 
> > > extension
> > > on sheet 2 that has dialled the number dialled.  Obviously I can match the
> > > number dialled in both cases, and the date, but there may be calls made to
> > > that same number by that same extension on that same date but at a 
> > > different
> > > time, so I need to incorporate the time into the formula, to say if the 
> > > date
> > > matches and the number dialled matches, and the time difference between 
> > > the
> > > two is (say) less than 3 minutes (as that is about the difference I am
> > > looking at) then give me the cost of that call in the column next to the
> > > extension number on Sheet 2.
> > >
> > > Sorry for the waffle but can anybody help.
> > > Thank you
> > > 
> > .
> > 
0
Utf
2/21/2010 7:41:01 AM
I believe it is the sumproduct - is there another formula I can use?
Also see the following revised worksheets.....

I have changed the formula to be in Sheet1 so that the formula represents 
the relevant extension in sheet1 that is in sheet2 using the following formula

=SUMPRODUCT(--(Sheet2!B:B=A2995),--(Sheet2!F:F=H2995),--(B2995+TIME(0,4,0)>=Sheet2!C:C),--(B2995-TIME(0,2,0)<=Sheet2!C:C),Sheet2!L:L)

Here is a sample of records from sheet 1 with the resulting extension at the 
end. The third column is duration (in seconds).  Why am I getting 3095 and 
3098's - they should go no higher that 1599?  Its adding two columns for some 
reason as the calls are quite close together:
                                                                             
                       
14/01/2010	15:53:24	40	07899941346	£0.035	1549
14/01/2010	15:55:48	609	07899941346	£0.533	3095
14/01/2010	17:04:37	52	07899941346	£0.046	1549
15/01/2010	08:27:49	229	07899941346	£0.200	1549
15/01/2010	08:34:16	11	07899941346	£0.010	1547
15/01/2010	08:38:25	5	07899941346	£0.004	1547
15/01/2010	08:45:37	6	07899941346	£0.005	3098
15/01/2010	08:46:14	30	07899941346	£0.026	3098

This is sheet 2 with the call data- duration here is column 4:

14/01/2010	15:55:52	07899941346	38	1549
14/01/2010	15:58:28	07899941346	608	1546
14/01/2010	17:06:59	07899941346	51	1549
15/01/2010	08:30:38	07899941346	228	1549
15/01/2010	08:37:15	07899941346	12	1547
15/01/2010	08:41:36	07899941346	4	1547
15/01/2010	08:48:37	07899941346	5	1549
15/01/2010	08:49:11	07899941346	29	1549

I have tried incorporating the duration Sheet1 - 1 second is not necessarily 
sheet 2 - it could be Sheet1 plus 1 second to match Sheet 2.

Sorry about the waffle but hope making sense



"Twiggy" wrote:

> Hi Bernard
> 
> I think I have done it, but I cannot get it to calculate!
> The worksheet is about 2665kb in size but when I press the f9 to calculate 
> the "calculating:(2 processor(s)):0%" doesnt move - left it for 15 mins.
> Is that my memory on my machine or something else?
> 
> "Twiggy" wrote:
> 
> > Hi Bernard
> > 
> > Many thanks for your help but I am getting an #Value!?
> > 
> > Sheet 1
> > Call start date	Call start time  Number	Duration	Cost
> > 28/01/2010	11:47:16	0800100607	00:01:58	£0.15
> > 22/01/2010	13:15:57	0800420421	00:00:07	£0.00
> > 22/01/2010	16:52:15	0800602090	00:00:44	£0.00
> > 26/01/2010	16:12:08	0800800160	00:00:54	£0.00
> > 15/01/2010	11:21:28	01132223222	00:00:52	£0.01
> > 
> > Sheet 2 (there is a sum total in row 2)
> > Start	   Time	Telephone No	Duration   First Rang	
> > 28/01/2010  11:49:46	0800100607	117	1011	#VALUE!
> > 22/01/2010  13:19:26	0800420421	6	1510	
> > 22/01/2010  16:55:40	0800602090	44	1545	
> > 26/01/2010  16:15:44	0800800160	53	1513	
> > 15/01/2010  11:24:41	01132221234	51	1032	
> > 29/01/2010  12:16:31	01132345678	41	1557
> > 
> > This is my formula:
> > =SUMPRODUCT(--(Sheet1!A3:A4350=A2),--(Sheet1!C3:C4350=C2),--(Sheet1!B2:B4350>=B2-TIME(0,3,0)),--(Sheet1!B2:B4350<=B2+TIME(0,3,0)),Sheet1!E2:E4350)	
> > 
> > where am i going wrong. There is a lot more data in Sheet2 so they dont 
> > follow in the same order on both sheets.
> > 
> > "Bernard Liengme" wrote:
> > 
> > > If I understand the question, this might work for you
> > > =SUMPRODUCT(--(Sheet1!A2:A10=A2),--(Sheet1!C2:C10=C2),--(Sheet1!B2:B10>=B2-TIME(0,3,0)),--(Sheet1!B2:B10<=B2+TIME(0,3,0)),Sheet1!E2:E10)
> > > 
> > > I put you data in row 1 for the two sheets  (with headers) and made up some 
> > > extra data down to row 10. You will need to adjust the ranges. Unless you 
> > > have Excel 2007, SUMPRODUCT cannot use full column ranges as in A:A. The 
> > > third and fourth terms specify that the times in Sheet1 must be within 
> > > plus/minus 3 mins of the time in Sheet2
> > > 
> > > best wishes
> > > -- 
> > > Bernard Liengme
> > > Microsoft Excel MVP
> > > http://people.stfx.ca/bliengme
> > > 
> > > "Twiggy" <Twiggy@discussions.microsoft.com> wrote in message 
> > > news:0F84B24F-E1F2-458B-861C-7A22F828A9E7@microsoft.com...
> > > > Hi
> > > > Please help.
> > > > I have two sheets as follows:
> > > >
> > > > Sheet 1:
> > > > Date             / Time          / Number Dailled         / Duration
> > > > /Cost
> > > > 22/1/10         10:00           01234123456               00:01:57 
> > > > 0.789
> > > >
> > > > Sheet 2:
> > > > Date             / Time          / Number Dailled         / Duration
> > > > /Extension
> > > > 22/1/10         10:02           01234123456               00:01:56 
> > > > 1501
> > > >
> > > > I am trying to put the cost of the phone call against the relevant 
> > > > extension
> > > > on sheet 2 that has dialled the number dialled.  Obviously I can match the
> > > > number dialled in both cases, and the date, but there may be calls made to
> > > > that same number by that same extension on that same date but at a 
> > > > different
> > > > time, so I need to incorporate the time into the formula, to say if the 
> > > > date
> > > > matches and the number dialled matches, and the time difference between 
> > > > the
> > > > two is (say) less than 3 minutes (as that is about the difference I am
> > > > looking at) then give me the cost of that call in the column next to the
> > > > extension number on Sheet 2.
> > > >
> > > > Sorry for the waffle but can anybody help.
> > > > Thank you
> > > > 
> > > .
> > > 
0
Utf
2/21/2010 8:17:01 PM
Not sure how we get to A2995 !
Want to send me a sample file? Get my email from my website
You are using Excel 2007, I hope since full column references like B:B are 
not legal in earlier versions
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Twiggy" <Twiggy@discussions.microsoft.com> wrote in message 
news:A25743AE-3580-4162-909E-74B68AFBE691@microsoft.com...
> I believe it is the sumproduct - is there another formula I can use?
> Also see the following revised worksheets.....
>
> I have changed the formula to be in Sheet1 so that the formula represents
> the relevant extension in sheet1 that is in sheet2 using the following 
> formula
>
> =SUMPRODUCT(--(Sheet2!B:B=A2995),--(Sheet2!F:F=H2995),--(B2995+TIME(0,4,0)>=Sheet2!C:C),--(B2995-TIME(0,2,0)<=Sheet2!C:C),Sheet2!L:L)
>
> Here is a sample of records from sheet 1 with the resulting extension at 
> the
> end. The third column is duration (in seconds).  Why am I getting 3095 and
> 3098's - they should go no higher that 1599?  Its adding two columns for 
> some
> reason as the calls are quite close together:
>
>
> 14/01/2010 15:53:24 40 07899941346 £0.035 1549
> 14/01/2010 15:55:48 609 07899941346 £0.533 3095
> 14/01/2010 17:04:37 52 07899941346 £0.046 1549
> 15/01/2010 08:27:49 229 07899941346 £0.200 1549
> 15/01/2010 08:34:16 11 07899941346 £0.010 1547
> 15/01/2010 08:38:25 5 07899941346 £0.004 1547
> 15/01/2010 08:45:37 6 07899941346 £0.005 3098
> 15/01/2010 08:46:14 30 07899941346 £0.026 3098
>
> This is sheet 2 with the call data- duration here is column 4:
>
> 14/01/2010 15:55:52 07899941346 38 1549
> 14/01/2010 15:58:28 07899941346 608 1546
> 14/01/2010 17:06:59 07899941346 51 1549
> 15/01/2010 08:30:38 07899941346 228 1549
> 15/01/2010 08:37:15 07899941346 12 1547
> 15/01/2010 08:41:36 07899941346 4 1547
> 15/01/2010 08:48:37 07899941346 5 1549
> 15/01/2010 08:49:11 07899941346 29 1549
>
> I have tried incorporating the duration Sheet1 - 1 second is not 
> necessarily
> sheet 2 - it could be Sheet1 plus 1 second to match Sheet 2.
>
> Sorry about the waffle but hope making sense
>
>
>
> "Twiggy" wrote:
>
>> Hi Bernard
>>
>> I think I have done it, but I cannot get it to calculate!
>> The worksheet is about 2665kb in size but when I press the f9 to 
>> calculate
>> the "calculating:(2 processor(s)):0%" doesnt move - left it for 15 mins.
>> Is that my memory on my machine or something else?
>>
>> "Twiggy" wrote:
>>
>> > Hi Bernard
>> >
>> > Many thanks for your help but I am getting an #Value!?
>> >
>> > Sheet 1
>> > Call start date Call start time  Number Duration Cost
>> > 28/01/2010 11:47:16 0800100607 00:01:58 £0.15
>> > 22/01/2010 13:15:57 0800420421 00:00:07 £0.00
>> > 22/01/2010 16:52:15 0800602090 00:00:44 £0.00
>> > 26/01/2010 16:12:08 0800800160 00:00:54 £0.00
>> > 15/01/2010 11:21:28 01132223222 00:00:52 £0.01
>> >
>> > Sheet 2 (there is a sum total in row 2)
>> > Start    Time Telephone No Duration   First Rang
>> > 28/01/2010  11:49:46 0800100607 117 1011 #VALUE!
>> > 22/01/2010  13:19:26 0800420421 6 1510
>> > 22/01/2010  16:55:40 0800602090 44 1545
>> > 26/01/2010  16:15:44 0800800160 53 1513
>> > 15/01/2010  11:24:41 01132221234 51 1032
>> > 29/01/2010  12:16:31 01132345678 41 1557
>> >
>> > This is my formula:
>> > =SUMPRODUCT(--(Sheet1!A3:A4350=A2),--(Sheet1!C3:C4350=C2),--(Sheet1!B2:B4350>=B2-TIME(0,3,0)),--(Sheet1!B2:B4350<=B2+TIME(0,3,0)),Sheet1!E2:E4350)
>> >
>> > where am i going wrong. There is a lot more data in Sheet2 so they dont
>> > follow in the same order on both sheets.
>> >
>> > "Bernard Liengme" wrote:
>> >
>> > > If I understand the question, this might work for you
>> > > =SUMPRODUCT(--(Sheet1!A2:A10=A2),--(Sheet1!C2:C10=C2),--(Sheet1!B2:B10>=B2-TIME(0,3,0)),--(Sheet1!B2:B10<=B2+TIME(0,3,0)),Sheet1!E2:E10)
>> > >
>> > > I put you data in row 1 for the two sheets  (with headers) and made 
>> > > up some
>> > > extra data down to row 10. You will need to adjust the ranges. Unless 
>> > > you
>> > > have Excel 2007, SUMPRODUCT cannot use full column ranges as in A:A. 
>> > > The
>> > > third and fourth terms specify that the times in Sheet1 must be 
>> > > within
>> > > plus/minus 3 mins of the time in Sheet2
>> > >
>> > > best wishes
>> > > -- 
>> > > Bernard Liengme
>> > > Microsoft Excel MVP
>> > > http://people.stfx.ca/bliengme
>> > >
>> > > "Twiggy" <Twiggy@discussions.microsoft.com> wrote in message
>> > > news:0F84B24F-E1F2-458B-861C-7A22F828A9E7@microsoft.com...
>> > > > Hi
>> > > > Please help.
>> > > > I have two sheets as follows:
>> > > >
>> > > > Sheet 1:
>> > > > Date             / Time          / Number Dailled         / 
>> > > > Duration
>> > > > /Cost
>> > > > 22/1/10         10:00           01234123456               00:01:57
>> > > > 0.789
>> > > >
>> > > > Sheet 2:
>> > > > Date             / Time          / Number Dailled         / 
>> > > > Duration
>> > > > /Extension
>> > > > 22/1/10         10:02           01234123456               00:01:56
>> > > > 1501
>> > > >
>> > > > I am trying to put the cost of the phone call against the relevant
>> > > > extension
>> > > > on sheet 2 that has dialled the number dialled.  Obviously I can 
>> > > > match the
>> > > > number dialled in both cases, and the date, but there may be calls 
>> > > > made to
>> > > > that same number by that same extension on that same date but at a
>> > > > different
>> > > > time, so I need to incorporate the time into the formula, to say if 
>> > > > the
>> > > > date
>> > > > matches and the number dialled matches, and the time difference 
>> > > > between
>> > > > the
>> > > > two is (say) less than 3 minutes (as that is about the difference I 
>> > > > am
>> > > > looking at) then give me the cost of that call in the column next 
>> > > > to the
>> > > > extension number on Sheet 2.
>> > > >
>> > > > Sorry for the waffle but can anybody help.
>> > > > Thank you
>> > > >
>> > > .
>> > > 
0
Bernard
2/23/2010 1:01:53 PM
Reply:

Similar Artilces:

Pasting data from Excel
Hello everyone, I'm not sure if I should be posting this question here or in the Excel forum but here goes. Is it possible to copy data from multiple cells in Excel and then paste them into multiple lines of the criteria section of an Access query? For example, Given cells and values: A1- 1 A2- 2 A3- 3 I would like to be able to copy this data from Excel and paste it into an Access query like : Criteria: 1 or: 2 3 I am using Access 2002 SP3 and Exc...

Printing data forms
I would like to be able to print Excel data forms. Is there a way? In some event of the form (a button, a click of the form itself, whatever): Me.PrintForm "Curious" <anonymous@discussions.microsoft.com> wrote in message news:170101c47bf9$f9191420$a301280a@phx.gbl... >I would like to be able to print Excel data forms. Is > there a way? If you mean the form you get when you do Data=>Form with the form displayed, do Alt+Print Screen. then drop the form and go to a new worksheet and do Edit=>Paste This will put an image of the form on the worksheet and yo...

Consolidation Worksheet Data Sequence
I'm consolidating 3 worksheets into one by using Excel 2007 data consolidation. The worksheets have a product number in column A and sales figures in columns B, C & D for Jan, Feb and Mar. The worksheets are in product number sequence. Not all product numbers appear on each sheet, so I consolidate by category using "labels in left column" and "create links to source data" to create a consolidation sheet in outline form. The problem is the consolidated worksheet is not in product number sequence as I need it to be. I have books on Excel 2007 and Excel 2003 and their ...

Free Data Process/Query Tool
If you use Excel everyday and spend lots of time on data/text processing, then you will find the this tool (UDQ Add-in) is very powerful and can speed up your daily data processing work. Currently the tool is free for everyone. Please feel free to distribute this tool to your friends/colleagues if you think it is helpful to your work. The following is a list of features/functionality of the tool: 1> Query/Import Data from Multiple External Data/Text/ASCII Files (Can useful import data from hundreds of files within minutes) 2> Query/Import Data from Multiple Closed Excel Files (Can...

How do i navigate between sheet tabs from my keyboard?
It would help my data entry immensly by not having to use the mouse. Ryan CTRL + PageUp and CTRL + PageDown to cycle through sheet tabs. Gord Dibben Excel MVP On Mon, 2 May 2005 17:02:02 -0700, Ryan T <Ryan T@discussions.microsoft.com> wrote: >It would help my data entry immensly by not having to use the mouse. ctrl-pgup and ctrl-pgdn will cycle through the worksheets. ctrl-F6 and ctrl-shift-F6 will cycle through the open windows. (As will ctrl-tab and shift-ctrl-tab.) Ryan T wrote: > > It would help my data entry immensly by not having to use the mouse. -- Da...

calculate time elapsed
Hello, I am using the function indicated below (which I found on one of these threads), but I'm getting small pop-up window with two dates on it and an OK button. I click OK and the function continues to run, but then it happens again and again and again. I think it might be because some EndDate values are null. Can someone help me modify this function so that it stops doing this? Thanks Here is the function I'm referring to. Function Minus_Non_Work_Time(BegDate As Variant, EndDate As Variant) As Integer ' Note that this function does not account for holidays. ' MODI...

Limits on Exporting Outlook Contact Data
Anyone know how to get custom fields, follow-up flags, and/or the "contacts" (links between contacts) from MS Outlook 2003 to Excel, CSV, Goldmine, or Act? The standard export utility in Outlook does not offer export of these fields. Most important is the links between contacts. You'd have to write your own code to do this. A key issue would be what information from the linked contact(s) you want to include. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode....

Lost cause and waste of time...
I submitted my question hoping to be directed to someone where my case could be heard and maybe resolved... Instead I was told: 1. I could not load on a second machine (I already know this and it was not the question). 2. I was told to "get an updated virus checker and told to keep it updated!!!!" (I already know this and it was not the question). 3. I was told she has "not been forced to reformat in 4 years !!" well good for her, I dont really care and not the question. I came to the website looking for answers... I really dont know who any of you are... or what ...

Select Rows dependant on certain field data
How can I select certain rows in a spreadsheet based upon the text foun in one column? To explain: All rows have a column that contains tex "immediate" or "ongoing" or "closed". How can I select only the row that contain the word "immediate" in that column? When I say select a mean select as in ready to 'copy' or 'cut' tha data ready to be pasted elsewhere. I know what I mean :confused -- Madd ----------------------------------------------------------------------- Maddy's Profile: http://www.excelforum.com/member.php?action=getinf...

sort data without changing pattern
I have a worksheet where every other row has a 'pattern' in it to help make it easier to read across the spreadsheet. The problem I'm running into is as I add new rows at the bottom of the spreadsheet and then resort them into their correct place, the patterns go with the sort and now the patterns have to be redone again the get them back to every other row. Any suggestions? This will happen if your patterns are manually applied. It would be better to remove that manual shading and to apply background colours using conditional formatting, the condition being if th...

Customizing Contact Data
Is there a way to customize contact card/data to collect data from others that I want to use rather than the generic that Microsoft has used? -- Thank you very much. Tall Paul. "TallPaul" <TallPaul@discussions.microsoft.com> wrote in message news:1C1DDD4F-BDCB-42F8-8F99-A2D674482E8E@microsoft.com... > Is there a way to customize contact card/data to collect data from others > that I want to use rather than the generic that Microsoft has used? What type of information do you want to collect? -- Brian Tillman [MVP-Outlook] TallPaul wrote: > Is...

Updating large pivot source data
Hello, I'm experiencing the following problem with a very simple macro in Excel 2007 to update pivot table data source. That is, my data has more than 74000 rows and I'm trying to update the source data with the following code: Sub Macro1() Dim rng1 As Range Set rng1 = Sheets("Test_sheet").Range("A1:A74000") ActiveSheet.PivotTables("Pivot-taulukko1").ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng1, _ Version:=xlPivotTableVersion12) End Sub The problem is that the code ...

Obtain data from list
I want to get the data from the list on to a cell in worksheet. I have 2 sheets - On Sheet 1 - I have drop downs. Whatever is selected in the list - I want to show that value in a cell on Sheet 2. Any help is greatly appreciated. Thanks! Hi, I think if you just change the properties of your dropdown so that the linked cell refers to the cell on your other sheet, it should work fine. Right-click on the dropdown and goto properties...Linkedcell. jeff >-----Original Message----- >I want to get the data from the list on to a cell in >worksheet. > >I have 2 sheets -...

Data Validation Allow List
Is there a way to click on the resulting combo box and have ALL the list items show up instead of just what seems like the default of about 8--I can scroll, but would like to just point and choose? Also, is there a way to change the font of this combo box? The resulting look like they are about 6 or 8 points. Any help is appreciated. Is this the right forum doug? If you are using a combo box (from the control toolbox) as opposed to a validation list you can change visible rows but not if you use validation Debra has a solution here http://www.contextures.com/xlDataVal10.html -- R...

Size of sheet
I have created a small spreadsheet in Excel XP. I have hidden all the unwanted columns and rows and it prints ok. However, when trying to email, all the unused columns and rows are still present (though hidden) creating a very large file too large to send. How do I limit the size of the sheet? Unhide the columns/rows, select the first row (the whole row) that you want to hide, press ctrl + shift & down arrow, then do edit>delete, save the workbook. Repeat for the columns but press Ctrl + Shift & right arrow, finally hide the column/rows again -- Regards, Peo Sjoblom ...

Linking multiple sheets
I need to set up a spreadsheet that links to 12 others. On each of the others there may be 100 rows(or less) of information to bring in to the master. Is it possible to write a formula that enables the link to pull in only rows that contain information? I don't want blank rows between each of the 12 groups of information I'm sure an expert will chime in with something better, but I've done similar with lookup functions. don't do it direct or it will be slow. I let everything come over to one master workbook, then use a lookup page that consolidates the data. "...

Creat a time book
I'm building a semi automated time book in Access. what i want is to be able to give access a two week period prefferably by specifying the beginning and end dates and have access add an entry to a table i'm going to call the 'Time Book' for each person in a personnell table for each day. the best i have been able to come up with is to pack a Macro with 14 queries, each adds one more day to a specified starting point. one of the problems i'm running into is that some of the shifts run over night and Access doesn't calculate the shift end correctly. I wo...

Sorting Multiple Sheets
I have a workbook with multiple sheets. The main sheet has 2 columns, 1 for first name, 1 for last name. The remaining sheets all have formulas to pull the first and last names automatically from the 1st sheet. If a user sorts the names on the first sheet, they will sort on subsequent sheets, but the information that goes with those names on subsequent sheets will not be included in the sort, thereby misplacing information. Is there anyway to remedy this, short of sorting each sheet each time there is an addition to any one sheet? ...

Excel Graphing Line References off when chart is a sheet.
I have noticed that when any graph is created in EXCEL and you hover you mouse over the dataline you receive that corect response. If you convert the chart to a sheet, the hover of the data line is now not representative of the the y axis directly below it. The data being graphed is correct now the hover represents the "series" (x-Axis) correctly but does not represent the "Point" (y-axis) correctly at all. Tne Y-axis datapoint reference is wrong. Any help? ...

Category totals over time...
Good morning... Is there a way within Money 2007 to graph the income or expense in a given category over time? For example, I might want to graph the monthly sums of my Dining Out expense over the past few years. -Ed In microsoft.public.money, Ed Markovich wrote: > >Is there a way within Money 2007 to graph the income or expense in a >given category over time? > >For example, I might want to graph the monthly sums of my Dining Out >expense over the past few years. You could start with the Income and Spending Over Time report. Customize to select just the category you w...

Cannot edit customer data in additional tab at HQ
I have created global customer with addtional data in additional tab at shop then send to HQ. At HQ, Why cant I edit customer data in additional tab? Or even I create new global customer at HQ, I cannot add data in addtional tab too, it is gray out. Is there any setting that I need to do? Thank you for any help. -- Jeed I believe, just like when editing items in HQ, you have to be in Maintenance Mode. Ed -- RMS 2.0 / HQ 2.0 Server 2003 / XP "Jeed" wrote: > I have created global customer with addtional data in additional tab at shop > then send to HQ. At HQ, Why ca...

converting data
Have made an x-y scatter chart with temperature on the y axis and time on the x axis.The data was imported and the temperature is in centigrade.How can I convert the data into degrees fahrenheit? The temperature data is in column B and contained in over 400 cells The only way I know how to do this is manually converting each cell but this will take forever.Can anyone show me how to convert the entire column with a formula perhaps? -- sghioto ------------------------------------------------------------------------ sghioto's Profile: http://www.excelforum.com/member.php?action=getinfo&am...

Format Cells Date (or any change) not working on imported data
Hello, I've just spent ages researching this and not come up with what I need to be able to do. I have a worksheet for some simple data that has been imported, a date, text and number column (as they display graphically to the end user). All are a "general" format when using Format > Cells. The issue I have is that the date information is in an American date format and I would like to change them into a UK date format. Format > Cells and selecting any option (including custom and special) makes no changes to the imported data. I have seen the work arounds whereby you sp...

Pulling/Collating Data
Hi, I have a workbook with 31 sheets (diary) A9:F9 (and other rows) is the info I want with the employee's initials in G9 There are multiple entries for the employee so he may reappear in row 13 and 15 I want to pull all the lines that relate to that employee from the 31 sheets into a separate workbook Thanks What is the name of the "separate workbook"? And in what sheet of this "separate workbook" do you want this data placed? Do you want only that data that pertains to that one employee whose initials are in G9? G9 of what sheet? Otto &qu...

Display Missing Data Labels as N/A on a Chart
I am displaying values as the data labels in a chart but missing values are displayed as 0's. Is there a way to display them as N/A instead? ...