Automatic Sort

buntykins · Apr 25th, 2002, 05:41 AM

I have a sheet in a workbook that is full of entries by employees about information they have retrieved from customers. In column J is the date of the call. Is there any way I can have the other sheets automatically pick out calls done on a certain day. So for instance sheet 2 could be called the 26/4/02 and it would display all calls taken on the 26/4/02, sheet 3 would show all calls taken on the 27th and so on...

Be grateful for some help

Janie

xx

buntykins · Apr 25th, 2002, 07:27 AM

Hiya guys,

Has anyone got ideas about this? All I need to know is the VB to display the entire row from a different sheet if the date meets a certain criteria. There will be multiple rows that will need to be displayed.

Thanks

Janie

gbarclay2001 · Apr 25th, 2002, 07:40 AM

It'd be easy to do if you link it to Access. Ever used Access before? What will you do with the data after it has been split up?

buntykins · Apr 25th, 2002, 07:48 AM

It's just going to be archived. I need to do it without Access if possible

Janie

Chris Davison · Apr 25th, 2002, 01:35 PM

could pivot tables help out ?

set up a pivot table on each sheet, with the "page" field set as the date

so first sheet could be June 18th data, second sheet could be June 19th data, third sheet could be June 29th data etc etc

_________________
Hope this helps,
Chris

[ This Message was edited by: Chris Davison on 2002-04-25 12:36 ]

Mark W. · Apr 25th, 2002, 01:39 PM

Create a macro that uses Advanced AutoFilters to extract data to each of your worksheets.

Mark W. · Apr 25th, 2002, 04:38 PM

I've figured out yet another approach using the Data | Get External Data | New Database Query... menu command.

With a range on Sheet1 containing...

{"Customer","Call_Date"
;"A","Jan 25, 2002"
;"B","Jan 25, 2002"
;"C","Jan 23, 2002"
;"D","Jan 25, 2002"}

The query...

SELECT Table1.Customer
FROM Book2.Table1 Table1
WHERE (Table1.Call_Date=?)

...returns {"Customer";"A";"B";"D"} to Sheet2!A1:A4 when the workbook is opened and/or the query parameter (stored in Sheet2!D1) is changed to "Jan 25, 2002".

Sheet2 could be copied and the query parameter changed to create different subsets of Table1.

[ This Message was edited by: Mark W. on 2002-04-25 15:45 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 05:41 AM	#1
buntykins Board Regular Join Date: Apr 2002 Posts: 76	I have a sheet in a workbook that is full of entries by employees about information they have retrieved from customers. In column J is the date of the call. Is there any way I can have the other sheets automatically pick out calls done on a certain day. So for instance sheet 2 could be called the 26/4/02 and it would display all calls taken on the 26/4/02, sheet 3 would show all calls taken on the 27th and so on... Be grateful for some help Janie xx

Apr 25th, 2002, 07:27 AM	#2
buntykins Board Regular Join Date: Apr 2002 Posts: 76	Hiya guys, Has anyone got ideas about this? All I need to know is the VB to display the entire row from a different sheet if the date meets a certain criteria. There will be multiple rows that will need to be displayed. Thanks Janie

Apr 25th, 2002, 07:40 AM	#3
gbarclay2001 New Member Join Date: Mar 2002 Location: London Posts: 46	It'd be easy to do if you link it to Access. Ever used Access before? What will you do with the data after it has been split up?

Apr 25th, 2002, 07:48 AM	#4
buntykins Board Regular Join Date: Apr 2002 Posts: 76	It's just going to be archived. I need to do it without Access if possible Janie

Apr 25th, 2002, 01:35 PM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	could pivot tables help out ? set up a pivot table on each sheet, with the "page" field set as the date so first sheet could be June 18th data, second sheet could be June 19th data, third sheet could be June 29th data etc etc _________________ Hope this helps, Chris [ This Message was edited by: Chris Davison on 2002-04-25 12:36 ]

Apr 25th, 2002, 01:39 PM	#6
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Create a macro that uses Advanced AutoFilters to extract data to each of your worksheets.

Apr 25th, 2002, 04:38 PM	#7
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	I've figured out yet another approach using the Data \| Get External Data \| New Database Query... menu command. With a range on Sheet1 containing... {"Customer","Call_Date" ;"A","Jan 25, 2002" ;"B","Jan 25, 2002" ;"C","Jan 23, 2002" ;"D","Jan 25, 2002"} The query... SELECT Table1.Customer FROM Book2.Table1 Table1 WHERE (Table1.Call_Date=?) ...returns {"Customer";"A";"B";"D"} to Sheet2!A1:A4 when the workbook is opened and/or the query parameter (stored in Sheet2!D1) is changed to "Jan 25, 2002". Sheet2 could be copied and the query parameter changed to create different subsets of Table1. [ This Message was edited by: Mark W. on 2002-04-25 15:45 ]