I have a typical Histogram showing the number of incidents that occured
by type.
I am trying to create an automatic action for the user that re-sorts
the data in the chart and reflects in the chart in a decending method.
I can do this using a pivot table with no problem but the pivot table
requires the user to perform a refresh....I do not want the to have to
perform any operations to update the chart if possible.
I tried some VBA code to fresh the Pivot table based on the internal
clock but this ended up causing some other problems.
I am open for any suggestions and can email original data if for your
review.
Thanks and HAPPY Holidays


--
Robert_L.
------------------------------------------------------------------------
Robert_L.'s Profile: http://www.excelforum.com/member.php...o&userid=21791
View this thread: http://www.excelforum.com/showthread...hreadid=495941

No need for VBA code. Suppose your incident types are in column A
starting with A1.

Then, in some column, say C, starting with C1 enter the names of the
incident types.
Then, in D1 enter =COUNTIF(A:A,C1). Copy D1 down col. D as far as you
have data in C.
In E1 enter =RANK(D1,D:D)+COUNTIF($D$1:D1,D1)-1. Copy E1 as far down E
as you have data in D.
In G1 enter =INDEX(C:C,MATCH(ROW(),E:E,0)). Copy G1 as far down G as
you have data in E.
In H1 enter =VLOOKUP(G1,C:D,2,FALSE). Copy H1 as far down H as you have
data in G.

Now, as you enter more data in A, the sorted order in G:H will adjust
itself. And, yes, it adjusts itself for ties.

If you start monitoring a new type of incident, you will have to add its
name at the bottom of col. C and extend all the formulas in D:H down one
row.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article ,
says...

I have a typical Histogram showing the number of incidents that occured
by type.
I am trying to create an automatic action for the user that re-sorts
the data in the chart and reflects in the chart in a decending method.
I can do this using a pivot table with no problem but the pivot table
requires the user to perform a refresh....I do not want the to have to
perform any operations to update the chart if possible.
I tried some VBA code to fresh the Pivot table based on the internal
clock but this ended up causing some other problems.
I am open for any suggestions and can email original data if for your
review.
Thanks and HAPPY Holidays


--
Robert_L.
------------------------------------------------------------------------
Robert_L.'s Profile: http://www.excelforum.com/member.php...o&userid=21791
View this thread: http://www.excelforum.com/showthread...hreadid=495941

Thank you for the information.
This did not exactly fit my situation.
I have enclosed a word document showing how I have the form laid out
currently from A:G. Column H is a copy and paste of column A. Columns
I:K are with the formulas suggested below except for the 1st countif
statement.
I have the following in the columns:

Column I:=IF(F40=0,NA(),RANK(F40,F:F)+COUNTIF($F$40:F40,F 40)-1)
added the If statement for I do not want to chart 0 values

Column J: =INDEX(H40:H52,MATCH(ROW(),I40:I52,0))
Have an error for I am receiving the dreaded N/A in the cell

Column K: =VLOOKUP(J40,H40:H72,2,FALSE)
Receiving an error on this based on column "J".

Can you assist again in finding my error?

Thank you


--
Robert_L.
------------------------------------------------------------------------
Robert_L.'s Profile: http://www.excelforum.com/member.php...o&userid=21791
View this thread: http://www.excelforum.com/showthread...hreadid=495941

I tweaked the formula a little bit and got it to work....
thank you for the help


--
Robert_L.
------------------------------------------------------------------------
Robert_L.'s Profile: http://www.excelforum.com/member.php...o&userid=21791
View this thread: http://www.excelforum.com/showthread...hreadid=495941