C
cherman
I have data that looks like this:
WW_Yr OpenDays Sminor Smedium Scritical Sundefined
WW01 14 1 0 0 0
WW01 1 0 1 0 0
WW02 15 1 0 0 0
WW02 129 0 0 1 0
WW03 17 0 0 0 0
WW04 13 1 0 0 0
etc.
This data starts in cell A1 and goes across to column M and the number of
rows can be different.
So in the 1st column I can have 1 or more records with the same week #
indicator. The other fields are numbers. The last 4 columns are basically
true/false. There will only be a "1" in one of the fields.
The 1st thing I want to do is to create a dynamic table based on this data,
with the data starting in cell O3, 4 columns wide and 26 rows.
The 1st column will have the distinct WW_Yr values from my source data above
dropped in from a routine.
The 2nd column needs to be an average of OpenDays for each of the 26 WWW_Yr
value where Sminor = 1 or a 0 if there are none. The other 3 columns will
work the same for Smedium, Scritical & Sundefined.
Here is a sample of what it might look like:
WW_Yr MinAvg MedAvg CriAvg UndAvg
WW01 23.567 0 0 0
WW02 0 18.88 0 0
WW03 0 0 0 7.127
WW04 42.5 0 0 0
WW05 0 0 11.11 0
WW06 0 31 0 0
etc.
The next thing I want to do is build in filtering options.
There's 7 more fields after Sundefined above and I want these to be filters.
I can use the autofilter if I have to, but I would like a way to give my
users the option to only filter on these fields from a distinct list of
values found in my source data. One of the fields is called Platform.
When a user chooses a value in a filter then I want the source data above to
filter to show only those rows and I need the dynamic table to adjust to only
calculate off of the rows showing.
Eventually, I will build a chart off of the dynamic table, but I just can't
get all the pieces to come together.
Any help will be greatly appreciated.
Thanks,
Clint
WW_Yr OpenDays Sminor Smedium Scritical Sundefined
WW01 14 1 0 0 0
WW01 1 0 1 0 0
WW02 15 1 0 0 0
WW02 129 0 0 1 0
WW03 17 0 0 0 0
WW04 13 1 0 0 0
etc.
This data starts in cell A1 and goes across to column M and the number of
rows can be different.
So in the 1st column I can have 1 or more records with the same week #
indicator. The other fields are numbers. The last 4 columns are basically
true/false. There will only be a "1" in one of the fields.
The 1st thing I want to do is to create a dynamic table based on this data,
with the data starting in cell O3, 4 columns wide and 26 rows.
The 1st column will have the distinct WW_Yr values from my source data above
dropped in from a routine.
The 2nd column needs to be an average of OpenDays for each of the 26 WWW_Yr
value where Sminor = 1 or a 0 if there are none. The other 3 columns will
work the same for Smedium, Scritical & Sundefined.
Here is a sample of what it might look like:
WW_Yr MinAvg MedAvg CriAvg UndAvg
WW01 23.567 0 0 0
WW02 0 18.88 0 0
WW03 0 0 0 7.127
WW04 42.5 0 0 0
WW05 0 0 11.11 0
WW06 0 31 0 0
etc.
The next thing I want to do is build in filtering options.
There's 7 more fields after Sundefined above and I want these to be filters.
I can use the autofilter if I have to, but I would like a way to give my
users the option to only filter on these fields from a distinct list of
values found in my source data. One of the fields is called Platform.
When a user chooses a value in a filter then I want the source data above to
filter to show only those rows and I need the dynamic table to adjust to only
calculate off of the rows showing.
Eventually, I will build a chart off of the dynamic table, but I just can't
get all the pieces to come together.
Any help will be greatly appreciated.
Thanks,
Clint