Hello all,
This is a twofold Question/Post so please specify which one you are answering or querying about.
I have a spreadsheet recording details of incidents that occur in my area with individual worksheets for every month.
These incidents are broken down with the following columns;
As you can see, I'm not really in a position to post the whole spreadsheet as it contains personal information.
The last worksheet I have a formatted report to print off giving summaries of various fields for the year, broken down by the month.
Q1:
For each month I have the most common "offence or MH condition". This field HAS to be a free text field, so I cannot create a standard list for drop downs.
At the moment I am using the following array formula;
{=INDEX('Jan 14'!D:d,MATCH(MAX(COUNTIF('Jan 14'!D:d,'Jan 14'!D:d)),COUNTIF('Jan 14'!D:d,'Jan 14'!D:d),0))}
- it is D: D but it converted this to hence the lower case d - and yes I know it doesn't matter with case lol -
My question is, whether or not I can do this without using an array formula, as it slows down the data entry for the fields it refers to.
I am not against using a macro, BUT, I am not familiar or versed in the process.
Q2:
I'd like to collate the "top 20" patients and how many times they have been involved for that year.
The issue I am having is referencing multiple worksheets to collate this information as they are text fields, AND have to remain as such.
I currently have been playing with a pivot table set up, but am unsure how to reference multiple worksheets and limiting to the 'top 20'.
I think I could possibly create lists (200 rows to ensure total capture) of this column for each month and use those lists in a pivot table or macro, but doing so is beyond my knowledge.
What would be the best way to approach this?
NB: Because this spreadsheet is stored on a shared network drive I want to limit the total size of the file and calculations it has to do to ensure the speedy opening/saving and data entry.
It will be used by people who are not familiar with Excel let alone could be called computer literate. So I am trying to make most things autopopulate/autocalculate.
Any help you can offer would be highly appreciated, but if it is in macro form please dumb it down as I am not versed in it, as stated above.
Regards,
Levi
This is a twofold Question/Post so please specify which one you are answering or querying about.
I have a spreadsheet recording details of incidents that occur in my area with individual worksheets for every month.
These incidents are broken down with the following columns;
Date | Event No | Patient Name | Offence or MH condition | Drug Related? | Alcohol Related? | Suburb | Time at Scene (Record Minutes) | Travel Time | Hospital Waiting Time | Return travel time | Total Time | Officers Involved | Sec 22 Police vehicle Escort | Sec 22 Ambulance with police Escort | Sec 20 Ambuance with police Escort | Section 20 Ambulance Escort | Other |
As you can see, I'm not really in a position to post the whole spreadsheet as it contains personal information.
The last worksheet I have a formatted report to print off giving summaries of various fields for the year, broken down by the month.
Q1:
For each month I have the most common "offence or MH condition". This field HAS to be a free text field, so I cannot create a standard list for drop downs.
At the moment I am using the following array formula;
{=INDEX('Jan 14'!D:d,MATCH(MAX(COUNTIF('Jan 14'!D:d,'Jan 14'!D:d)),COUNTIF('Jan 14'!D:d,'Jan 14'!D:d),0))}
- it is D: D but it converted this to hence the lower case d - and yes I know it doesn't matter with case lol -
My question is, whether or not I can do this without using an array formula, as it slows down the data entry for the fields it refers to.
I am not against using a macro, BUT, I am not familiar or versed in the process.
Q2:
I'd like to collate the "top 20" patients and how many times they have been involved for that year.
The issue I am having is referencing multiple worksheets to collate this information as they are text fields, AND have to remain as such.
I currently have been playing with a pivot table set up, but am unsure how to reference multiple worksheets and limiting to the 'top 20'.
I think I could possibly create lists (200 rows to ensure total capture) of this column for each month and use those lists in a pivot table or macro, but doing so is beyond my knowledge.
What would be the best way to approach this?
NB: Because this spreadsheet is stored on a shared network drive I want to limit the total size of the file and calculations it has to do to ensure the speedy opening/saving and data entry.
It will be used by people who are not familiar with Excel let alone could be called computer literate. So I am trying to make most things autopopulate/autocalculate.
Any help you can offer would be highly appreciated, but if it is in macro form please dumb it down as I am not versed in it, as stated above.
Regards,
Levi