PDA

View Full Version : Solved: Combobox - Dynamic Named Range



f2e4
07-21-2008, 04:34 AM
Hi guys,

I currently have a combobox with a ListFillRange = staff_list

This range is from another sheet using the offset function to make it dynamic:

=OFFSET('Staff List'!$C$4,0,0,COUNTA('Staff List'!$C:$C),2)

When a value in the combobox is selected, a filter in a pivottable is changed.

My issue is the following:

Using the above dynamic range, I always get 1-2 blank rows at the bottom of the listbox

and for some totally unexplainable reason, this totally messes with my pivot filter.

E.G. I select staffmember 4

The filter in the pivot table changes to staffmember 4

However, the datafields display data from either staffmember 2 or 3.

No idea why this is happening!!!

Has anyone ever come across this or have some suggestions?

Thanks

F

mikerickson
07-21-2008, 05:32 AM
You might try defining the range
=OFFSET( 'Staff List'!$C$4,0,0,COUNTA('Staff List'!$C:$C)-COUNTA('Staff List"!$C$1:$C$3),2)
to account for the entries in C1:C3

f2e4
07-21-2008, 07:53 AM
Thanks mike,

That cleared up the blank rows at the end of the combobox.

However, i'm still having an issue with the pivot table filter.

Below is an outtake of my code showing how I am changing the PT filter:


temp = Sheets("Reports").ComboBox1.ListIndex

With Sheets("Reports").ComboBox1
StaffMember = .List(temp, 0) & "," & " " & .List(temp, 1)
End With

Sheets("Drop Downs").PivotTables("PivotTable1").PivotFields("Staff Member").CurrentPage = StaffMember


I think i may have figured out why the filter is going wrong...

When i look at the list of names in the filter list, all the names are there and for some reason there is an additional one - ","

I'm guessing now that because there is an additional name in the list, the row references are all pushed up one.

For the life of me, i just can't figure out why that "," is there seeing as there are no blanks anymore....

mikerickson
07-21-2008, 10:25 AM
I'm not familiar with Pivot Tables, but as a pure guess, the .List of a ComboBox is 0 based, and if pivot tables require a 1-based arrays (the IF reflects my ignorance) that might cause a problem.

Hopefully, someone with more knowledge of pivot tables will be able to help.

f2e4
07-22-2008, 02:38 AM
thanks for help anyway mike

hopefully someone will be able to figure this one out

f2e4
08-01-2008, 05:20 AM
anyone else got any ideas - tried so many different ways to get round this but at a dead end now

Bob Phillips
08-01-2008, 05:26 AM
Can you post a workbook example so as to save us re-inventing your wheel?

f2e4
08-01-2008, 05:51 AM
Can you post a workbook example so as to save us re-inventing your wheel?

There are so many bits all linked together, it would take me ages to start trying to extract the relevant sections

I don't want to attach the full version so can i email it to look over - plus you have helped me put most it together

Bob Phillips
08-01-2008, 06:21 AM
Yeah sure, you have my email now.

f2e4
08-13-2008, 07:10 AM
Ok so I finally worked this one out.

Everytime the worksheet opened, I was deleting the pivot fields and then adding them again.

I thought this deleted all information, however, it seems that the field I was filtering on, was actually storing all old information (including items I had deleted).

This then threw the index out by 1-2 items so that when you selected someone to view their info, you were actually looking at the info of 2 people up.

The only way round this was to delete the entire pivot table and create a new one from scratch.

Bob Phillips
08-13-2008, 07:52 AM
Thanks for the update. I was following the wrong path altogether, I was thinking it was cascading events causing the problem.