MIS Help required - ( Calling all excel Ninjas and pro users )

[saumyamukherjeee]

Dear All,


Attached if the file showing pending consignments of a courier company .


https://docs.google.com/open?id=0BwzKuUvuZlkcMW1GZGtBbWR5Tlk


In different columns showing different details as below -


1.Consignment no - Consignment number of the sent packet


2.Originator name- the location from where consignment has been despatched


3.approver name- name of the officer of the concerned location from where consignment being sent.


4.Receiver Name - Receiver under whose attention consignment has been addressed


5.Despatched on - date of despatch frm originator location.


My job is to publish MIS of the pending receivers as on current date. Refer pivot table in the excel sheet which is currently i'm doing.


Important Notes -


1.As per process followed in this courier company internally, packet should be ideally get delivered to receiver latest by 7th day from the date of despatch.


2.Receiver name is limited to currently 5 users only, i.e anand, ardhendhu, mita, samrat and saumya. which on later date may increase maximum to 18 such diffrent names.


3.Consignment numbers are actually alphanumeric but to show the data in a better way I've removed all the additional alphabets ( this hardly matters since my job is to count the pending consignments not the name).


4.Suppose today is 25/10/2012, I'm least interested in publishing defaulting receivers less than 7days since TAT is only 7days and above to be considered as defaulting receiver.


5.Currently I'm publishing MIS on the basis of pivot attached showing pending dates in left pane and count of consignments vis a vis defaulting receivers name.


6.Since this is system generated report so format of the date is in the form of dd/mm/yyyy i.e 19th September 2012 being displayed as 19/09/2012. So practically i'm unable to format the date in my own ways and this looks a bit odd to managers.


My requirement is to obey above conditions and faults and display this MIS in a good way with proper format which is a bit worthy looking and eye pleasing.I don't want to use any macro in this excel but charts and conditional formatting and any other things will be okay except macro.


For any other details/information's/requirements please let me know.


Thanks...Saumya

 

[Luke M]

This may be a silly question, but what does MIS mean?

 

[saumyamukherjeee]

Luke, I practically don't think so, well let me go ahead and explain this-


A management information system (MIS) provides information that is needed to manage organizations efficiently and effectively, This is actually a type of report as per organizational requirement on prescribed criteria's.


Presentation criterias i've already set and defined. Let me know if anything else required ( info/details/ knowledge issues like MIS)


Thanks...Saumya

 

[Faseeh]

Hi Saumya,


You can readily make it attractive for readers by:


1. Select a diff. format of PT that you like.

2. You can always select and format date in whatever format you like but only few of your dates are true dates rest are texts so you need to adjust it.

3. Chart?? What do you want to show in chart?? That is the basic question.


Faseeh

 

[saumyamukherjeee]

Hi Faseeh,


Thanks a lot for raising queries , sill I'm not done with this w.r.t to below -


1.PT I'm currently using is the best possible way i found apart from trying others. Please let me know if you've any alternate option.


2.This file actually gets generated by a typical system and format of the date is in this way only so a bit confused. I tried applying formatting but it worked a little.


3.Chart has just came as an idea for me to show, there is no such mandatory requirement as such . Its just an idea I'm messed up with.


Thanks...Saumya