Looks like I can't set all the drop downs to " --Select--" as it now looks at this as an entry and it adds these in on the counts.

Uggggggg.

Back to the drawing board on this one.

Regarding the data validation and the drop owns, the way I had it set up is it refers to a range specified in typical Excel RC format e.g. A2:A20. A more robust way is to refer to a named range. I have done that on the file attached (see a few posts down) and also included a list for Investigators. I will attach the file after I respond to everything here.

To add new list items, there are a few ways to do it:
* See that little doohickey on the bottom right of each list table? Grab it and drag it down.
* Select a cell or a group of cells, right click, and select Insert: Table Rows above. The number of rows inserted will be the number of cells selected.
* Once you have added your new items, sort the table.
* Don't leave blanks or they will appear in the list.

This is addressed above. If you go to Data: Data Validation for each and look at the specified ranges, you will see why.

Up on your ribbon to the right, you will see Pivot Table Options with tabs for Options and Design there. Options will let you modify the data displayed. Design allows you to format how they are displayed. Use the canned formats under Design.

As I've use the Format as Table functionality here, there are lots of things you can do. Sorting or even filtering right on the data table. For example, click the filter doohickey for any one of the columns with dates. You can select specific months or years, or even filter by Last Quarter and Year to Date.

I added this too to the file to be attached soon. I added some dummy dispositions with a few blanks in the middle. Look at the drop downs on the Log and you will see the blanks. This is why you should not have blanks in the list.

OK...not sure what that mans, but great! Here's my updated file.

Attached Files

Working Sample.xlsx (85.3 KB, 7 views)

I will take a look at this soon.......... thanks again.

I see how the drop downs are working and how to adjust the rows on the Data page to include or exclude what is in the drop down. Very nice, thanks.

I'm trying to adjust the Pivot Table now as they are not updating when I refresh. It looks like some old references are in the Pivot Table. I'm seeing if I can figure out how to change this. It's showing the investigator initials but I replaced all of them with their real names now. The Log sheet drop downs updates but not the Pivot.

Let me clarify, it looks like Pivot2 is not updating but Pivot 1 is. So, there are no totals at all on Pivot2.

It shows a (blank) entry on both Pivot1 and Pivot2 which I do not remember seeing before.

I removed the Blank, that was just a sorting thing.

Pivot2 is just a copy of Pivot1. All I did was move Investigator from the Row Labels in the pivot options on Pivot1 to the Report Filter. Other than that, they are identical. Go ahead and delete Pivot2. I just added it as an illustration of the flexibility of pivot tables. No need t muddy the waters further with it. There's probably something about Investigator being in the filter that is throwing a wrench in things.

Truthfully, your priority should be to get the Data and Log tabs to your suiting first. Then I suggest deleting both pivots and starting fresh.

I think the only thing I need to do is some minor formatting. I have updated the drop down lists with what I need.

I was hoping to use the Pivot Tables you made....... I guess now I'll have to figure out how to make them myself! Yikes. I'll give it a shot.

I'm hoping to have this done by tomorrow because my counterpart is still using the old spreadsheet and I need to get him off of that ASAP because the 2016 info will need to be added again into this spreadsheet.

I'm going to tackle a pivot table right now. I'm going to blow away Pivot 2 and re-create it so that it totals all the incidents.

So far Pivot 1 is working so I will leave that for now.

Ok, I'm sure your getting bored with me by now.

I'm trying to make a new pivot table and it keeps telling me I can't because the range is not valid. I am trying to select the entire set of fields on the Log tab.....although I know that I only need a few of the columns. I'm not sure how to select just specific columns or how to setup a Pivot outside of the wizard.

I'm looking through Excel "How To" videos now but so far nothing that goes over my particular issue.

Haha...don't worry. They are easy. Just follow how I have it set up. To start...

* Go to Insert: Pivot Table
* For Table/Range, type in "CaseLog" - no quotes.
* Place it a new sheet.
* Click OK.

Now you have to set it up. To replicate Pivot1:
* On the right, click and drag both the Investigator and Incident fields to Row Labels. It is important that Investigator is on top. To see why, drag Incident above it.
* Click and drag Jurisdiction to Column Labels.
* Click and drag Incident again but this time to Values.
* Pivot2 is the same except Investigator is in report Filter rather than Row Labels.

That's all. Easy peasy!

I'm also curious as to how you have the Investigator drop down selection on Pivot 2 as it seems to be a different setup than Pivot 1.

If you can push me in the right direction that would be great, I'm not expecting you to keep doing all the work.

Damn...... That was easy! Why doesn't the Wizard tell me I can do this????

I'm a Pivot Master. HA HA HA