LIST BOX IN EXCEL

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: LIST BOX IN EXCEL

Post by Rudi »

Hi Krishnaa

First, you must change the Pivot Table Layout to Tabular
1.jpg
Then you need to remove the sub totals. Right click on each sub total level and remove
2.jpg
Lastly, format with the first medium autoformat
3.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: LIST BOX IN EXCEL

Post by krishnaa_kumarr88 »

THANKS

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: LIST BOX IN EXCEL

Post by krishnaa_kumarr88 »

Hi,

Report this postReply with quoteRe: LIST BOX IN EXCEL
by HansV » 07 Nov 2014, 12:46

=COUNTIFS('TPM FORM'!A24:A8506,"AIRBUS",'TPM FORM'!B24:B8506,">="&TODAY()-WEEKDAY(TODAY(),3),'TPM FORM'!B24:B8506,"<"&TODAY()-WEEKDAY(TODAY(),3)+7)

I got this formula working.
But i would like generate graph for the every week data. That is i would like to store the above formulae data once in a week and generate graph weekly .
Does any one have any idea?
thanks

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LIST BOX IN EXCEL

Post by HansV »

Do you mean that if you have data for 10 years, you would like to generate 10*52 = 520 charts?
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: LIST BOX IN EXCEL

Post by krishnaa_kumarr88 »

nope. I would like to store the data on the same chart.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LIST BOX IN EXCEL

Post by HansV »

Could you provide a small example of what you want?
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: LIST BOX IN EXCEL

Post by krishnaa_kumarr88 »

I have
=COUNTIFS('TPM FORM'!A24:A8506,"AIRBUS",'TPM FORM'!B24:B8506,">="&TODAY()-WEEKDAY(TODAY(),3),'TPM FORM'!B24:B8506,"<"&TODAY()-WEEKDAY(TODAY(),3)+7) formula in one of the dashboard cell (A5) excel workbook.
The cell(A5) will refresh once in a week according to the formula.
There are different headings like "Airbus"(cell-A4), "Airbus2"(B4), etc . which again refresh once in a week.
That means i dont know what data i got in cell A5 last week as it refresh next week again to zero.
Say example on Sunday night if i get any data as 19 on cell A5 and next day that is monday, data will again goes to zero.
What i would like to do is creating a graph which monitor or store all the data. That is in the above example, i would like to store 19 (as value) in the graph/chart before it goes to zero again. simillary i would like to get data to the graph once in a week .

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LIST BOX IN EXCEL

Post by HansV »

Would you like one chart displaying the values for all weeks? If so, it would be best to use a pivot table and pivot chart based on the original data (rows 24:8506 on the TPM FORM sheet), with the dates grouped by days, in groups of 7.
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: LIST BOX IN EXCEL

Post by krishnaa_kumarr88 »

I would like one chart but different total number of values every week.
Say if Airbus load 20 rows for week 1, i should able to get 20 in chart. Next week if airbus load 18 rows for week 2, i should able to get 18 in chart for week 2. I just want to count the total number of rows for Airbus or different title every week and display the chart weekly. please let me know if u need more information.
thanks

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LIST BOX IN EXCEL

Post by HansV »

See my previous reply - create a pivot table that counts the number of rows for a specific title per week, and a chart based on that pivot table.
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: LIST BOX IN EXCEL

Post by krishnaa_kumarr88 »

Thanks

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: LIST BOX IN EXCEL

Post by krishnaa_kumarr88 »

Hi Rudi and Hans,

Hope you are good. I am just wondering is it possble to do the following process.

Please see the attachment. Open the attachment.

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

1. Click update (incase if u get pop up windows)
2. If it ask for password please click cancel
3. Please click continue
4. TPM form open up
5. Under Module: please select AIRBUS
6. Under Machine name please "SELECT ALL"
7. Under Result please select "OK"

Basically what i need is when you select "SELECT ALL" under Machine name i would like to update in TPM QRQC (2) Sheet and under "TOTAL NUMBER OF TPM INSPECTION DONE" coloumn and i would like to get the result at 14.

Thanks,
Kind regards,
Krishna

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LIST BOX IN EXCEL

Post by HansV »

Can you explain why the result should be 14?
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: LIST BOX IN EXCEL

Post by krishnaa_kumarr88 »

oops sorry.Actually it should be 17 . 17 indicate total number of machines from the MACHINE NAME for AIRBUS module. That is in the TPM FORM , Once u select Module as "AIRBUS". When u select the MACHINE NAME as "SELECT ALL", u can count the total number of machines under SELECT ALL. It will be 17.

Thanks

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LIST BOX IN EXCEL

Post by HansV »

There are only 12 rows with data in the TPM FORM sheet, so I don't understand where the number 17 comes from.
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: LIST BOX IN EXCEL

Post by krishnaa_kumarr88 »

sorry Hans. 17 comes from the userform. When u open the userform ,
1. Select the Module as "AIRBUS"
2. Under MACHINE NAME there are 17 Machines starting with CTX500B Turning Machine till Petroferm machine. So totally there r 17 machines. What i need is if we select "SELECT ALL" that means all 17 machines are selected. There i would like TPM QRQC(2) to get updated as 17 under "TOTAL NUMBER OF TPM INSPECTION DONE" in the TPM QRQC(2) sheet.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LIST BOX IN EXCEL

Post by HansV »

Add the following to Private Sub CommandButton1_Click():

Code: Select all

    Dim col As String
    If Me.Machine = "SELECT ALL" Then
        Select Case ComboBox1.Value
           Case "AIRBUS"
                col = "D"
            Case "GCU"
                col = "G"
            Case "FALCON"
                col = "I"
            Case "AC"
                col = "K"
            Case "DC"
                col = "M"
            Case "MRO"
                col = "O"
        End Select
        Worksheets("TPM QRQC (2)").Range("B6") = Application.CountA _
            (Worksheets("AddNew").Range(col & "8:" & col & "100"))
    End If
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: LIST BOX IN EXCEL

Post by krishnaa_kumarr88 »

Hi I am getting errorr in
If Me.Machine = "SELECT ALL" Then

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LIST BOX IN EXCEL

Post by HansV »

Where did you put the code? It works for me when I add it to CommandButton1_Click...
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: LIST BOX IN EXCEL

Post by krishnaa_kumarr88 »

Sorry its working now. I am wondering how to make separate cells for each results.
That is , i would like to Get airbus total number of machines in Worksheets("TPM QRQC (2)").Range("B6") = Application.CountA _
Falcon in Worksheets("TPM QRQC (2)").Range("B7") = Application.CountA _
GCU in Worksheets("TPM QRQC (2)").Range("B8") = Application.CountA _
and so on