Here is what I am talking about. This superintendent will periodically request a data file and this is exactly how they will get it.
https://www.dropbox.com/s/r0dzbfkr05...eb2501c8cc.csv
Is there a way to automate it as it's put into the workbook with 5 char columns for those code columns?
An update. I injected all your code. When I select an ISD, District, and School Bldg (doesn't matter which combination), I get a run-time error 1004: ## is not an item of this field. The line below in red is always the culprit.
Public Function FilterPivot( _
ByVal PivotTable As String, _
ByVal ISDCode As String, _
ByVal DistrictCode As String, _
ByVal BuildingCode As String) As Boolean
Dim pvtItem As PivotItem
Application.ScreenUpdating = False
With Worksheets("Working Pivot Tables").PivotTables(PivotTable)
.PivotFields("ISDCode").EnableMultiplePageItems = False
.PivotFields("DistrictCode").EnableMultiplePageItems = False
.PivotFields("BuildingCode").EnableMultiplePageItems = False
With .PageRange
.Cells(1, 2).Value = ISDCode
.Cells(2, 2).Value = DistrictCode
.Cells(3, 2).Value = BuildingCode
I'm still not getting the problem. As I understand, Working Pivot tables is just a staging between the Combined worksheet and the Summary worksheet and charts. Your executive shouldn't care a monkey's what Combined and Working Pivot Tables look like (I would even hide them), only Summary. And on Summary, District and Building shows as 5 chars, ISD can be formatted same.Quote:
Originally Posted by dovermac
I was testing with Oakland Schools/Oakland Schools or Birmingham/any building or no building and it all worked fine. What values are you using, I will try those on mine see if it makes a difference. As a last resort, I can post my workbook somewhere.Quote:
Originally Posted by dovermac
I tried Oakland Schools, etc like you and got the same thing. I did mention to my project leader your recommendations and he replied with -
"We don't want to deslect the blank from the pivot tables because that is ensuring that we are working with the district summary record and not the building records which is what we want in this case.
On the EnrollmentByGrade pivot table the requirements are to show only the current school year in the graph so the school year is required."
Not sure if that matters or not. As for the working pivot thing, I don't know if the exec will use that or not, but for now, let's skip it. Did you catch my last post about somehow automating the input of CSV files into this workbook and converting those 3 columns to 5 characters? I can't have the superintendent manually formatting these columns every time. You know that won't fly ;-)
P.S. Almost forgot, is there a way to change the display name on the summary page to match what was chosen? I didn't code that, but was curious.
That is not what I meant. What I was saying is that the charts are showing blank entries, which have no values. They are irrelevant, create a datapoint on the series, which obscures a little. Best to just filter it in the pivots.Quote:
Originally Posted by dovermac
Okay, but how does the current school year get selected, it isn't on the selection form?Quote:
Originally Posted by dovermac
I did respond to that in a separate thread as well, but I think I am still missing it.Quote:
Originally Posted by dovermac
Which display name are you referring to?Quote:
Originally Posted by dovermac
I'll forward this to the PM.Quote:
Originally Posted by xld
I'll run this by my PM. Not my call.Quote:
Originally Posted by xld
This kind of lost me. Is it still confusing? I'm sure I'm probably explaining it poorly. Did you take a look at the source CSV file from the link I sent? My way of formatting the 3 columns and putting it into the workbook is inefficient I know.Quote:
Originally Posted by xld
The title at the top of the Summary tab in the black bar. Or does your workbook already accurately display the name of whatever you picked in the PS form like Oakland Schools, etc?[/QUOTE]Quote:
Originally Posted by xld
I did look, but it doesn't help me I feel. I understand how the data arrives, I understand how it gets changed when you post to Combined, but I don't understand where it is showing as a problem. As I say, Summary only has a short ISD number, and that is easily fixed.Quote:
Originally Posted by dovermacThis kind of lost me. Is it still confusing? I'm sure I'm probably explaining it poorly. Did you take a look at the source CSV file from the link I sent? My way of formatting the 3 columns and putting it into the workbook is inefficient I know.Quote:
Originally Posted by xld
Okay, I understand now. Replace the userform call with this codeQuote:
Originally Posted by dovermacThe title at the top of the Summary tab in the black bar. Or does your workbook already accurately display the name of whatever you picked in the PS form like Oakland Schools, etc?Quote:
Originally Posted by xld
Code:Public Sub CallUserForm()
Dim mpForm As ParameterSelection
Dim wsCombined As Worksheet
Dim mpTotalsRow As Long
Dim mpTotal As Double
Set wsCombined = Worksheets("Combined")
Set mpForm = New ParameterSelection
With mpForm
.Show
If Not .Cancel Then
Call FilterPivot("EnrollmentByGrade", .ISDCode, .DistrictCode, .BuildingCode)
Call FilterPivot("TotalEnrollmentTrend", .ISDCode, .DistrictCode, .BuildingCode)
Call FilterPivot("PivotTable1", .ISDCode, .DistrictCode, .BuildingCode) 'Race/Ethicity Makeup Trend
Call FilterPivot("PivotTable2", .ISDCode, .DistrictCode, .BuildingCode) 'Other Demographic Enrollment Trend
Worksheets("Summary").Range("B3").Value = .ISDCode
Worksheets("Summary").Range("B4").Value = .DistrictCode
Worksheets("Summary").Range("B5").Value = .BuildingCode
If .ISDCode = NoSelection Or .DistrictCode = NoSelection Or .BuildingCode = NoSelection Then
Worksheets("Summary").Range("B6:D8").ClearContents
ElseIf .BuildingCode <> NoSelection Then
mpTotalsRow = Worksheets("EEM").Columns(colEEM.BuildingCode).Find(.BuildingCode, After:=Worksheets("EEM").Cells(1, colEEM.BuildingCode)).Row
Worksheets("Summary").Range("B6").Value = Worksheets("EEM").Cells(mpTotalsRow, colEEM.Address)
Worksheets("Summary").Range("B7").Value = Worksheets("EEM").Cells(mpTotalsRow, colEEM.City) & " " & _
Worksheets("EEM").Cells(mpTotalsRow, colEEM.State) & " " & _
Worksheets("EEM").Cells(mpTotalsRow, colEEM.Zip)
Worksheets("Summary").Range("B8").Value = Worksheets("EEM").Cells(mpTotalsRow, colEEM.Phone)
End If
If .BuildingCode <> NoSelection Then
mpTotalsRow = wsCombined.Columns(colCombined.BuildingCode).Find(.BuildingCode, After:=wsCombined.Cells(1, colCombined.BuildingCode)).Row
ElseIf .DistrictCode <> NoSelection Then
mpTotalsRow = wsCombined.Columns(colCombined.DistrictCode).Find(.DistrictCode, After:=wsCombined.Cells(1, colCombined.DistrictCode)).Row
Else
mpTotalsRow = wsCombined.Columns(colCombined.ISDCode).Find(.ISDCode, After:=wsCombined.Cells(1, colCombined.ISDCode)).Row
End If
With wsCombined
mpTotal = .Cells(mpTotalsRow, colCombined.TotalEnrol).Value
Worksheets("Summary").Range("H3").Value = .Cells(mpTotalsRow, colCombined.American).Value
If mpTotal <> 0 Then
Worksheets("Summary").Range("I3").Value = .Cells(mpTotalsRow, colCombined.American).Value / mpTotal
Else
Worksheets("Summary").Range("I3").Value = 0
End If
Worksheets("Summary").Range("H4").Value = .Cells(mpTotalsRow, colCombined.Asian).Value
If mpTotal <> 0 Then
Worksheets("Summary").Range("I4").Value = .Cells(mpTotalsRow, colCombined.Asian).Value / mpTotal
Else
Worksheets("Summary").Range("I4").Value = 0
End If
Worksheets("Summary").Range("H5").Value = .Cells(mpTotalsRow, colCombined.African).Value
If mpTotal <> 0 Then
Worksheets("Summary").Range("I5").Value = .Cells(mpTotalsRow, colCombined.African).Value / mpTotal
Else
Worksheets("Summary").Range("I5").Value = 0
End If
Worksheets("Summary").Range("H6").Value = .Cells(mpTotalsRow, colCombined.Hispanic).Value
If mpTotal <> 0 Then
Worksheets("Summary").Range("I6").Value = .Cells(mpTotalsRow, colCombined.Hispanic).Value / mpTotal
Else
Worksheets("Summary").Range("I6").Value = 0
End If
Worksheets("Summary").Range("H7").Value = .Cells(mpTotalsRow, colCombined.Hawaiian).Value
If mpTotal <> 0 Then
Worksheets("Summary").Range("I7").Value = .Cells(mpTotalsRow, colCombined.Hawaiian).Value / mpTotal
Else
Worksheets("Summary").Range("I7").Value = 0
End If
Worksheets("Summary").Range("H8").Value = .Cells(mpTotalsRow, colCombined.White).Value
If mpTotal <> 0 Then
Worksheets("Summary").Range("I8").Value = .Cells(mpTotalsRow, colCombined.White).Value / mpTotal
Else
Worksheets("Summary").Range("I8").Value = 0
End If
Worksheets("Summary").Range("H9").Value = .Cells(mpTotalsRow, colCombined.TwoOrMore).Value
If mpTotal <> 0 Then
Worksheets("Summary").Range("I9").Value = .Cells(mpTotalsRow, colCombined.TwoOrMore).Value / mpTotal
Else
Worksheets("Summary").Range("I9").Value = 0
End If
End With
Worksheets("Summary").Range("A1").Value = wsCombined.Cells(mpTotalsRow, colCombined.ISDName).Value & _
IIf(.DistrictCode = NoSelection, "", ", " & wsCombined.Cells(mpTotalsRow, colCombined.DistrictName).Value) & _
IIf(.BuildingCode = NoSelection, "", ", " & wsCombined.Cells(mpTotalsRow, colCombined.BuildingName).Value)
End If
End With
End Sub
Ok, well, I still have the same error on line:
.Cells(1, 2).Value = ISDCode
So somewhere my code doesn't quite match up, perhaps on another tab? What was this code supposed to do exactly? I mean what adjustment was made?
Was this supposed to address the columns issue? If so, that would be so sweet.
I spoke to my PM and he said to just do whatever you recommend. If you can tell me how to tweak these pivot tables I will. Remember, I suck at these things. :-)
We need to figure out what I can do for you. I have to do something for all your help. By the way, my name is Jeff.
Hello Jeff, my name is Bob.
I have copied my file to DropBox, https://www.dropbox.com/s/pe70ja36dl...Dashboard.xlsm.
Download it, see if it solves your problems, and let me know what you think, whether anything is not what you need.
I'm at work Bob, and that's blocked, like most things here so as soon as I get home I'll try it out. Got any hobbies Bob? I'm personally into movies, music and games.
Hi Bob,
I just looked at your workbook. I'm impressed, looks great. We're just about there. Only one thing left. For the sake of this project's demo, we have to show importing 3 different files with the other user form (Data Acquisition), which is next to the PS form in the WB. That form is very basic. All you do is click Open, navigate to the file and then on top box, click the drop down arrow and pick a tab name. We are only going to be using Combined, StudentCount, and EEM. Now the source filename is something like a6578e05-0ec4-4b27-8323-c2836dacceda.csv. It then copies the contents to that tab, starting with the first blank line. My question is can you revise the existing code to somehow import these 3 files depending which one it is and convert the code columns to 5 characters? If you can get that working, that would be it! Then we need to figure out what I can do for you. :-) By the way, where are you located? I'm in Michigan as you probably guessed.
Jeff,
Are you saying that you just want to complete the Data Acquisition form take-on of data? open the file, clear out the old, insert the new? Or are you saying append to the data already there? Have you got a sample file that I can test with.
BTW, how are you proposing to fire these forms?
The form has been coded, but isn't working exactly right. The files will always append. That part is working. The "code" columns are not converting to 5 characters. The source filenames are gibberish so that doesn't help. Here are links to 3 sample files. Download them, don't rename them and experiment. Try the Data Acquisition Form. It will append just fine, it doesn't convert the 3 code columns to 5 chars.
EEM: https://downloads.mischooldata.org/GeneratedDataFile?filename=226ef227-e6ae-468e-8351-7a416b15ac36.csv
Student Count: https://downloads.mischooldata.org/GeneratedDataFile?filename=6cc33dc0-b63f-46c3-b595-83462cb9d33d.csv
https://downloads.mischooldata.org/GeneratedDataFile?filename=44577f82-18a0-4c3f-b0f0-2a48d5de0da7.csv
This is the Combined File.
I don't understand the "how am I to fire these forms" question. can you elaborate please?
Hey Bob,
Everything ok? Just curious. You need anything from me?
Jeff
Hi Bob,
I'm guessing that you're no longer interested in working on this? If that's the case, just let me know. No hard feelings. I really appreciate all your help, sincerely! :-)
Jeff