LIST BOX IN EXCEL
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
LIST BOX IN EXCEL
Hi ,
Thanks for looking at my query. I am having problem in creating listbox, please help me.
Please check the attachment. According to the attachment,
1. I would like to create a list box in sheet 2 that is named as TPM QRQC in my excel spreadsheet.
2. In my spreadsheet in sheet 2 (TPM QRQC), i have two column , one is for TOTAL NUMBER OF TPM INSPECTION and other for TOTAL NUMBER OF REQUIRES ATTENTION. I linked those two coloumns to sheet1 (TPM FORM) in such a way that if sheet1 turns red then it is counted as 1 in TOTAL NUMBER OF REQUIRES ATTENTION column in sheet 2.
3. What i need is , Depending upon how many TOTAL NUMBER OF REQUIRES ATTENTION (in sheet 2) which add on everyday basics,i would like to create a list box in sheet2 which shows information about the current RED row information in sheet 1 but more like summary way. In the list box it should show only Machine name, Result, Pirana open date which are red in sheet 1.
Please help
Thanks
Thanks for looking at my query. I am having problem in creating listbox, please help me.
Please check the attachment. According to the attachment,
1. I would like to create a list box in sheet 2 that is named as TPM QRQC in my excel spreadsheet.
2. In my spreadsheet in sheet 2 (TPM QRQC), i have two column , one is for TOTAL NUMBER OF TPM INSPECTION and other for TOTAL NUMBER OF REQUIRES ATTENTION. I linked those two coloumns to sheet1 (TPM FORM) in such a way that if sheet1 turns red then it is counted as 1 in TOTAL NUMBER OF REQUIRES ATTENTION column in sheet 2.
3. What i need is , Depending upon how many TOTAL NUMBER OF REQUIRES ATTENTION (in sheet 2) which add on everyday basics,i would like to create a list box in sheet2 which shows information about the current RED row information in sheet 1 but more like summary way. In the list box it should show only Machine name, Result, Pirana open date which are red in sheet 1.
Please help
Thanks
Last edited by krishnaa_kumarr88 on 07 Oct 2014, 12:32, edited 1 time in total.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: LIST BOX IN EXCEL
Hi,
I could suggest having a Pivot Table that simulates a list box.
It will be 100 times easier to manage and it is dynamic...it will update automatically based on the conditions in sheet 1.
See sample attached...
I could suggest having a Pivot Table that simulates a list box.
It will be 100 times easier to manage and it is dynamic...it will update automatically based on the conditions in sheet 1.
See sample attached...
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
thats brilliant. Thanks a lot. Its working great. Thanks :)
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
Thanks for your brilliant work. The problem in the above spreadsheet is ,only first 8 pivot table list is added automatically in sheet 2 based on the conditions in sheet 1. Anything added more than 8 or 9 in sheet 1 is not added automatically in sheet 2. Do you know the reason?
thanks
thanks
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
I see that you removed the attachment from your first post in this thread.
Please don't do that - it makes a thread hard to understand for others who read it.
Please don't do that - it makes a thread hard to understand for others who read it.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
Hi Rudi, I sorted out the issue. Its working brilliant now. Thanks for your help
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: LIST BOX IN EXCEL
I'm glad you came right. In the sample file I only set the Pivot data range to row 40 (if I remember correctly).
I assume you modified the range to increase it and include records that fell outside this data area.
I assume you modified the range to increase it and include records that fell outside this data area.
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.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
I would convert the data range to a table, and make the table the source range of the pivot table. That way, the source range will adjust itself automatically.
You need to stop the clock when you close the workbook. In the ThisWorkbook module:
(I'd remove the clock altogether - since the code to update it runs every second, editing the workbook is difficult)
You need to stop the clock when you close the workbook. In the ThisWorkbook module:
Code: Select all
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopClock
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
hI RUDI AND HANS.
Thanks a lot for your reply. Sorry to disturb you again. I got same problem again. I am really poor in pivot table :(.
1. Please see the attachment, I just added one more column called Module in A 23. Becaue of that i am not getting proper pivot table source range in sheet 2. I would like to set the pivot data range to row 600. Can you please help me in that?
2. I would like to get your suggestion in my following idea,
a) Click TPM FORM
b) Select Module to Airbus/GCU/Falcon/AC,etc any one
C) Load rest of the form information
My question is , is it possible to filter the information respected to MODULE Wise? That is , if i select AIRBUS module in the form, i should able to see only AIRBUS module information in the spreadsheet (Which has heading as MODULE in A23) . Simillarly , if i select GCU Module in the TPM FORM i should able to see only information related to GCU Module in the spreadsheet. So i would like to create a filter in the TPM form itself . Is it possible?
Thanks
Thanks a lot for your reply. Sorry to disturb you again. I got same problem again. I am really poor in pivot table :(.
1. Please see the attachment, I just added one more column called Module in A 23. Becaue of that i am not getting proper pivot table source range in sheet 2. I would like to set the pivot data range to row 600. Can you please help me in that?
2. I would like to get your suggestion in my following idea,
a) Click TPM FORM
b) Select Module to Airbus/GCU/Falcon/AC,etc any one
C) Load rest of the form information
My question is , is it possible to filter the information respected to MODULE Wise? That is , if i select AIRBUS module in the form, i should able to see only AIRBUS module information in the spreadsheet (Which has heading as MODULE in A23) . Simillarly , if i select GCU Module in the TPM FORM i should able to see only information related to GCU Module in the spreadsheet. So i would like to create a filter in the TPM form itself . Is it possible?
Thanks
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
Check the attachment here.
Thanks
hI RUDI AND HANS.
Thanks a lot for your reply. Sorry to disturb you again. I got same problem again. I am really poor in pivot table :(.
1. Please see the attachment, I just added one more column called Module in A 23. Becaue of that i am not getting proper pivot table source range in sheet 2. I would like to set the pivot data range to row 600. Can you please help me in that?
2. I would like to get your suggestion in my following idea,
a) Click TPM FORM
b) Select Module to Airbus/GCU/Falcon/AC,etc any one
C) Load rest of the form information
My question is , is it possible to filter the information respected to MODULE Wise? That is , if i select AIRBUS module in the form, i should able to see only AIRBUS module information in the spreadsheet (Which has heading as MODULE in A23) . Simillarly , if i select GCU Module in the TPM FORM i should able to see only information related to GCU Module in the spreadsheet. So i would like to create a filter in the TPM form itself . Is it possible?
ThanksPost=137272
krishnaa_kumarr88
NewLounger
Posts: 23
Joined: 30 Sep 2014, 15:18
Private messageE-mail krishnaa_kumarr88
Thanks
hI RUDI AND HANS.
Thanks a lot for your reply. Sorry to disturb you again. I got same problem again. I am really poor in pivot table :(.
1. Please see the attachment, I just added one more column called Module in A 23. Becaue of that i am not getting proper pivot table source range in sheet 2. I would like to set the pivot data range to row 600. Can you please help me in that?
2. I would like to get your suggestion in my following idea,
a) Click TPM FORM
b) Select Module to Airbus/GCU/Falcon/AC,etc any one
C) Load rest of the form information
My question is , is it possible to filter the information respected to MODULE Wise? That is , if i select AIRBUS module in the form, i should able to see only AIRBUS module information in the spreadsheet (Which has heading as MODULE in A23) . Simillarly , if i select GCU Module in the TPM FORM i should able to see only information related to GCU Module in the spreadsheet. So i would like to create a filter in the TPM form itself . Is it possible?
ThanksPost=137272
krishnaa_kumarr88
NewLounger
Posts: 23
Joined: 30 Sep 2014, 15:18
Private messageE-mail krishnaa_kumarr88
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: LIST BOX IN EXCEL
I added the pivot again.
Also modified the form to filter for the module.
I think the code still needs a bit of tweaking to work better, but it is bordering on the limit of my knowledge....
Also modified the form to filter for the module.
I think the code still needs a bit of tweaking to work better, but it is bordering on the limit of my knowledge....
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.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
About the pivot table: see the attached version. I incorporated the suggestion I posted in my previous reply.
I don't understand your question about filtering the TPM form, but I see that Rudi posted a suggestion about that.
I don't understand your question about filtering the TPM form, but I see that Rudi posted a suggestion about that.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
Hi Rudi and Hans
Wow. thats brilliant. thanks a lot for your help :)
Wow. thats brilliant. thanks a lot for your help :)
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
hI RUDI AND HANS
Regarding List box,
The above attachment is working fine , but i done some modification in my excel and after doing some modification my DATE coloumn in the pivot table is not working fine. Say example, if my spreadsheet show the date as 1/10/12 but in the pivot table under the same cell i am getting as some 1234.3344 . Please see the attachment. Thanks
Regarding List box,
The above attachment is working fine , but i done some modification in my excel and after doing some modification my DATE coloumn in the pivot table is not working fine. Say example, if my spreadsheet show the date as 1/10/12 but in the pivot table under the same cell i am getting as some 1234.3344 . Please see the attachment. Thanks
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: LIST BOX IN EXCEL
You can select the entire H column and format the column to a date format of your choice. The values you currently see in your pivot is the unformatted decimal values behind a typical date format.
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 rudi,
I am just wondering is it possible to create a chart based on the sheet1 value.
That is total number of rows vs rows in red for the above attachment.
thanks
I am just wondering is it possible to create a chart based on the sheet1 value.
That is total number of rows vs rows in red for the above attachment.
thanks
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: LIST BOX IN EXCEL
Hi,
See the attached with a simple chart example....
See the attached with a simple chart example....
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.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
Or a slightly different approach: calculate the number of non-red rows:
And create a stacked column chart (or stacked bar chart, or even a pie chart) based on the number of rows needing attention and the number of non-red rows:
And create a stacked column chart (or stacked bar chart, or even a pie chart) based on the number of rows needing attention and the number of non-red rows:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
THANKS