EXCEL- A sorted & filtered sheet combining data from 10 sheets 1

[johnturgoose]

Hi,

As usual my knowledge limit has been reached.

I have posted a similar question in the past but it was poorly explained and hence a number of the answers didn't really fit the requirements.

1 have 10ish sheets representing different centres. All similar layout. Each with many columns of patient data. However the columns of interest are patientid number, patientinitials, date of randomisation.

What is required is a sheet that incorporates that data from each sheet in one long list. The data will need to be sorted by date of randomisation. I then need to apply a formula to the new list that looks at the date of randomisation and if more than a year has elapsed it answers with some sort of 'true' remark allowing me to select it in a mailmerge to send out chasing letters.

Each of the 'feeder' sheets is a list which is constantly growing. I have specified dynamic range names for each sheet but its the combining where I struggle.

--------------

so to summarise

10 ish feeder sheets

eg. Hull, Leeds, Scunthorpe
each with patient records. Much data but important stuff is

eg
Scunthorpe
ID INITIALS DATE OF RANDOMISATION
00012 JB 25/12/2002
00063 JT 01/07/2003

Hull

ID INITIALS DATE OF RANDOMISATION
00003 BJ 25/07/2002
00100 CF 31/08/2003

COMBINING TO MAKE

ID INITIALS DATE OF RANDOMISATION
00003 BJ 25/07/2002
00012 JB 25/12/2002
ETC....


Any suggestions would be gratefully recieved. What would be really impressive would be if the records in the combined list showed which sheet they came from. Perhaps with a column showing sheet of origin. Not essential though so added as an after thought.

Thanks so much,

John

 

[SkipVought]

John,

My STRONG recommendation is that you take the time to redesign you system to have all your similar data in a SINGLE table. You will need to ADD a column for Centre.

Once you have done that, the reporting that you need to do is much simpler.

Segmenting data, like you have done, is a mistake that many users make -- and it is a BIG MISTAKE to generate and maintain non-normailzed data.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[johnturgoose]

I have taken your advice skip and eventually managed to get all the data into one table. Interestingly doing the exercise I noticed a number of duplications/errors etc (perhaps reinforcing your point regarding keeping all data together).

The problem i seem to face now is with the advanced filter function.

I basically want to filter the data by a function. I want to select all rows where the randomisation was over a year ago.

Date of Randomnisation =Column D.
There are 3 Header rows 4,5,6

My criteria =(TODAY()-365)>7

So i put criteria in D2 using the 3 criteria rows as descibed in the help function. When i apply it, all records are filtered out.

Any ideas, I bet my car i'm doing something stupidly simple.

Thanks guys,

JOhn

 

[SkipVought]

John,

=(TODAY()-365)>7 will ALWAYS be TRUE???

Today() is 38036.

What are you trying to use as a criteria VALUE for what criteria HEADING?

Also, you should have ONE (1) heading row -- nothing the row ABOVE the heading row -- nothing in any column directly to the left or right of the table.

HINT: There is a Current Region toolbar button in the Edit menu (right click the toolbar, select Customize in the popup -- select the Commands tab -- select the Edit menu -- scroll to bottom and drag Select Current Region button to your toolbar) that is really handy to make sure that your table is ISOLATED from other data or to select the Current Region. If you select any cell in your table and hit the Current Region button, it should select ALL the data and the headings in your table and NOTHING ELSE.


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[johnturgoose]

Sorry Skip the criteria I entered was.

=(TODAY()-365)>D7

Where D7 is the first row of data in the column Date of Randomnisation. Therefore when D7 = 11/12/2001 the answer should be true and the row included. However a recent randomnisation 10/2/2004 should return false and be filtered out.

Thanks

John

 

[SkipVought]

"criteria in D2 using the 3 criteria rows "

You mention ONE criteria and also 3 criteria rows????

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[johnturgoose]

I only have 1 criteria Skip. To select only rows where the date in column D is over a year ago.

I got the 3 criteria rows from the help files where they suggest you have 3 rows above the data into which to enter criteria.

Thanks

John

 

[SkipVought]

D7 has a heading.

Lets say that the Heading is DATE

Here's your CRITERIA
[tt]
FIRST ROW - DATE
NEXT ROW - =">"&TEXT(AA1,"mm/dd/yyyy&quot

AA1 - =TODAY()-365
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[johnturgoose]

Thanks Skip,

The solution works great. My only problem is I need to provide a way to filter down these results by centre. It won't let me have an autofilter on top of an advanced filter so I guess I would need another criteria entered. I don't want to have to make the users switch advanced filter on and off and change criteria. I would prefer to hide these rows. Is there a way of providing them with a list based on column C. The listbox of centre names which they can select from.

Column C heading is Centre

so could I have the heading,

C1 - Centre
C2 - =AA2
AA2 - this is populated from some sort of combobox from row c? perhaps a dynamic range name so as more centres are joining they are included?

Am I better to try and do this through forms?

Why are things never easy.

Thanks

John

 

[SkipVought]

John

You can specify multiple criteria in adjacent cells
[tt]
DATE Centre
=">"&TEXT(AA1,"mm/dd/yyyy&quot =AA2
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884