LIST BOX IN EXCEL
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: LIST BOX IN EXCEL
Hi Krishnaa
First, you must change the Pivot Table Layout to Tabular
Then you need to remove the sub totals. Right click on each sub total level and remove
Lastly, format with the first medium autoformat
First, you must change the Pivot Table Layout to Tabular
Then you need to remove the sub totals. Right click on each sub total level and remove
Lastly, format with the first medium autoformat
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
THANKS
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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
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
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
Do you mean that if you have data for 10 years, you would like to generate 10*52 = 520 charts?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
nope. I would like to store the data on the same chart.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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 .
=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 .
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
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
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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
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
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
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
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
Thanks
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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
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
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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
Thanks
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
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
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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.
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.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
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
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
Hi I am getting errorr in
If Me.Machine = "SELECT ALL" Then
If Me.Machine = "SELECT ALL" Then
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
Where did you put the code? It works for me when I add it to CommandButton1_Click...
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
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
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