LIST BOX IN EXCEL

[krishnaa_kumarr88]

HI Rudi,

I am just wondering is it possible to do the following things,
1. In Sheet 2 , in the PIVOT TABLE. Is it possible to include one more coloumn next to pivot table showing how long the particular red row is activated for. Each row should indicate the how long the red row is activated for .Example: 2 days 3hrs 30 sec, etc.

Is it possible?

[Rudi]

Hi,

See if you can work with this....

TPM filter with Chart_Update.xlsm

[krishnaa_kumarr88]

Wow. Brilliant rudi..

[krishnaa_kumarr88]

Hi Rudi,

I tried so many different possible things , but i cant sort the following issue. Do you have any idea of how to do?
thanks,

My issue is as follows,
I would like to modify the pivot table in sheet 2 as follows,

1. Click the input form in sheet 1
2. Go to Priority section in input form (dropdown list). You will find 5 different options in priority section.
3. Depending upon the selection of priority in the input form, i should get different conditional formatting in pivot table in sheet 2 ( no change in sheet 1)
4. If i select any one among first two options in the priority , pivot table for the corresponding row in sheet 2 should turn red
5. If i select any one among next two options in the priority, pivot table for the corresponding row in sheet 2 should turn amber
6. If i select last option in the priority, pivot table for the corresponding in sheet 2 should turn green.

Most importantly, in the pivot table in sheet 2 i would like to arrange the row automatically based upon the color. That is all red colors as top priority row, amber next to red priority and green last.

Please check the attachment
Please help me,
Thanks

[Rudi]

There is no attachment...

[krishnaa_kumarr88]

hi,
i am struggling to do the attachment. The file is too big :(

[HansV]

Have you tried zipping the workbook?

If that doesn't help, you can upload the workbook to a site such as OneDrive (https://onedrive.live.com" onclick="window.open(this.href);return false;), DropBox (https://www.dropbox.com" onclick="window.open(this.href);return false;) or FileDropper (http://www.filedropper.com" onclick="window.open(this.href);return false;). Make sure that you share the uploaded file, then get a link to it and post the link here.

[krishnaa_kumarr88]

Try this
http://www.filedropper.com/finalzip" onclick="window.open(this.href);return false;

thanks

[HansV]

1) Select the pivot table on the TPM QRQC sheet, and set the fill color to (None).
2) The conditional formatting manager window should look like this:

S28.png

The formulas are, from top to bottom:

=$K6=ADDNEW!$D$170
=OR($K6=ADDNEW!$D$166,$K6=ADDNEW!$D$167)
=OR($K6=ADDNEW!$D$168,$K6=ADDNEW!$D$169)

[krishnaa_kumarr88]

i tried the same way which u described above before, but there is some problem with my conditional formatting , its not highlighting the right row.

[krishnaa_kumarr88]

http://www.filedropper.com/showdownload.php/finaltpm_1" onclick="window.open(this.href);return false;

Its not highlighting the correct row and its not arranging automatically depending upon the color of the row.

Example: Alll the red color in the pivot table should go top, amber color should come bottom to the red color and green should come bottom to amber color. (automatic arrangement in the pivot table)

Thanks

http://www.filedropper.com/showdownload.php/finaltpm_1" onclick="window.open(this.href);return false;

[HansV]

The Conditional Formatting Rules Manager looks different in your workbook: the formulas refer to the wrong row and the rules apply to the wrong range:

S0133.png

Please remove the rules, then select the body of the pivot table (G6:K11) and create the rules as described in my previous reply.

[krishnaa_kumarr88]

THANKS A LOT

[krishnaa_kumarr88]

Everything working fine , except the following issue
There r 3 colors in pivot table,
Red,
Amber and
Green.

I want red to sort on the top, next amber and finally green automatically each time when the pivot table is updated.

I tried by using Data->Sort option but its showing error as

"you cannot move a part of Pivot table.........."

Thanks

[Rudi]

Hi Krishnaa,

I got sidetracked with work and had to turn my attention to a project I need to help with.
I see Hans has graciously helped you further. I will look at your Pivot question in more detail later if no help is offered further, but without viewing your file I can just comment that pivots are very automated and it might be difficult (if not impossible) to get it to sort the way you need it based on colours. Pivots only work with values (and limited within the primary group). Of course this advice is given blindly as I do not know the setup of your file yet, but don't hold your breath for a solution for this question based on standard pivot limitations.

[krishnaa_kumarr88]

Hi Rudi,

Thanks a lot for your kind help

[HansV]

I can't get the pivot table to sort on priority, sorry.

[krishnaa_kumarr88]

Thanks anyway Hansv

[Rudi]

Krishnaa,

Hans is more of an expert than I. If he has confirmed that it cannot be done, then there is no way I will be able to assist either. Either you will need to dispose of the colour idea of determine a different approach.

Hans, TX for your input.

[krishnaa_kumarr88]

Thanks a lot rudi and hans