Unique city is nothing more than I am looking at the "Jurisdiction" column and I'm creating a column with just unique entries. I did this so that I can then add just items for those unique cities a little easier. It is my way of breaking the problem up into multiple steps.

The reason there are multiple tabs is because each tab represents a different investigator. The person using this spreadsheet goes to each investigator and tracks which cases they are assigned...... which incident types and cities, etc.



I personally think a small Access DB would be better for doing what he is doing but this is what I have to work with at the moment.

Ah ok.

What do you think about combining them and adding a field (i.e. a column) to track investigator for each record?

No doubt. Unfortunately, few people use Access anymore and I think Microsoft is letting that tomato wither on the vine to die.

Well, take a look at this. In this example, I have combined the data into a single sheet called Log. The data table itself is called CaseLog (I hate spaces). I added a field for Investigator and another for Review date.

Pivot1 shows the count for each Investigator for each Incident type by Jurisdiction. Any particular investigator's records can be collapsed, but the totals remain the same. Alternatively (see Pivot2), we could add an investigator filter and the totals would update for the filter applied.

I also added drop down data validations for Incident and Investigator. I noticed a few entries for jurisdiction that had extraneous spaces or multiple spellings and that will screw up the pivot results. Data validation will prevent that.

Anyhoo...that's my first cut at it. If it looks promising, we can forge ahead. If not, back to the drawing board. This is just for illustrative purposes only. You can play around with the pivots and get a feel for how they work. I resisted for a long time but use them a lot now. They are easy as all get out.

Attached Files

Working Sample.xlsx (84.0 KB, 8 views)

Wow, that is some amazing work. It also makes sense to me. I like that the user can add items to the drop down lists by managing this on the Data tab. I have done similar things in other spreadsheets but didn't even think about it here for some reason.... probably because I was focused on changing the initial spreadsheet instead of just putting a new one together, although, the drop down fields are not updating when I add items to the list.

So, now I need to tackle trying to add in new investigators to the Pivot Tables.

Other than that, I think this is looking pretty awesome.

Your help has been invaluable.

What is the easiest way to modify these pivot tables? I assume it must be easy to add new investigators or have it automatically find them.

I definitely need to study up on Pivot Tables as they appear to be a very powerful tool.

New investigators will be added as you enter records in the log for them. If you want to use the data validation drop downs, you first have to update the investigator's list on the Data tab which is easy enough. Then add records to the log. Lastly, you need to refresh the pivot after any new data is added. Right click anywhere in the pivot and select Refresh.

Don't be intimidated. They are really easy. Just play around with them and you'll get the hang of it.

Look over what I posted and let's talk about next steps.

Ahhhh, the good ole' right-click.

That worked well.

I'm trying to figure out why the drop-downs are not refreshing now. I have added an incident type and a jurisdiction but on the Log tab they are not yet showing up.

Figured it out....... I need to expand the range the drop down is looking at.

Is there a quick way for me to change the validation range for the entire column?

And will this carry over for each additional row? I'm assuming so......

Figured that out too......... ugggg. It's early. Sorry.

I just had to highlight the entire column it appears and then make the validation change.

Maybe my last question..........

I have extended the data validation for 1000 rows for both jurisdiction and incident and I selected a range on the Data sheet that is much larger (more rows) so that new items can be entered.

I don't see blanks in the drop down for incident but I do for jurisdiction. Under the validation setting I have "Ignore Blank" checked.

Any clue why I'm seeing blanks in the drop down?

Ok, let me sum up the only two questions I think I need at this point.

You let me know if you think I'm missing anything.

1) Why am I getting blanks in the Jurisdiction drop-down but not the Incident drop down?

2) Are there any specific issues I need to worry about when formatting the Pivot Table? i.e. Changing the fonts, colors, etc. Will they carry over as the Pivot Table changes? I will obviously do some testing but thought I would ask as well.

I really can't think of anything else at this point.

I can add investigators and know how to update the Pivot Table to reflect them.

The Pivot Tables do the calculations.

The Log tab can be sorted by any column.

The Data tab can be used to update the drop down lists.

I think you did a great job for me and now I will try to enhance this cosmetically and start adding in the investigators, wiping out the old data, etc.

By the way, I made Disposition a drop-down and added it to the Data tab....... I'm getting blanks in this drop-down as well. I must be missing something simple.

I noticed that if I add something the pivot table will calculate it but if I delete something the pivot table doesn't remove it.

Is this how Pivot Tables typically work?

I think I have everything figured out...... or at least I have stop gap measures put in place.

I made a " --Select--" option for all the dropdowns and set it as the default so the blank lines don't show.

I tried to do a dynamic list but it wasn't working well so I will continue to look at that but at least I have a decent fix in place that will work.

The formatting seems to be fine as I changed a few things and everything looks okay.

I think I'm good to go!!!!!!!!!!!


Your help is very much appreciated. I can't thank you enough.