LIST BOX IN EXCEL
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
My code does not work for pivot table close. You have any idea?
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
In Module 1:
In ThisWorkbook:
Code: Select all
Public dtmTime As Date
Sub refresh_pivot_tables()
Dim pt As PivotTable
With ThisWorkbook.Worksheets("TPM QRQC")
For Each pt In .PivotTables
pt.RefreshTable
.Range("L1").Value = pt.RefreshDate 'Last refresh time
.Range("M1").Value = pt.Name 'last refreshed table name
Next pt
End With
dtmTime = Now + TimeValue("00:00:05")
Application.OnTime dtmTime, "refresh_pivot_tables"
End Sub
Code: Select all
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dtmTime, "refresh_pivot_tables", , False
End Sub
Private Sub Workbook_Open()
dtmTime = Now + TimeValue("00:00:05")
Application.OnTime dtmTime, "refresh_pivot_tables"
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
thanks hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
Hi ,
Thanks for your help. I am almost stuck with the following problem. Hope you can give me suggestion please.
Please see the attachment.
This is not the latest attachment. I dont have any problem in updating pivot chart from one excel to another( Note: the attachment is not latest ).
The problem in sheet 2 in row B5 and C5. What i need is, i have total number of machines in Airbus as 17 (that is the list from TPM form). I would like the operators to update all the machines every day. Row C5 in sheet 2 gives information about TOTAL NUMBER OF TPM INSPECTION CARRIED OUT FOR THIS WEEK.
What i need is i would like to update sheet 2 C6 every week starting from zero.
That is from monday till sunday it need to give information for that week. from next monday the cell c6 should go automatically to zero for that week.
Any idea?
thanks
Thanks for your help. I am almost stuck with the following problem. Hope you can give me suggestion please.
Please see the attachment.
This is not the latest attachment. I dont have any problem in updating pivot chart from one excel to another( Note: the attachment is not latest ).
The problem in sheet 2 in row B5 and C5. What i need is, i have total number of machines in Airbus as 17 (that is the list from TPM form). I would like the operators to update all the machines every day. Row C5 in sheet 2 gives information about TOTAL NUMBER OF TPM INSPECTION CARRIED OUT FOR THIS WEEK.
What i need is i would like to update sheet 2 C6 every week starting from zero.
That is from monday till sunday it need to give information for that week. from next monday the cell c6 should go automatically to zero for that week.
Any idea?
thanks
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
I assume that you meant C6 instead of C5.
Change the formula in C6 to
=COUNTIFS('TPM FORM'!A24:A8506,"AIRBUS",'TPM FORM'!B24:B8506,">="&TODAY()-WEEKDAY(TODAY(),3),'TPM FORM'!B24:B8506,"<"&TODAY()-WEEKDAY(TODAY(),3)+7)
Change the formula in C6 to
=COUNTIFS('TPM FORM'!A24:A8506,"AIRBUS",'TPM FORM'!B24:B8506,">="&TODAY()-WEEKDAY(TODAY(),3),'TPM FORM'!B24:B8506,"<"&TODAY()-WEEKDAY(TODAY(),3)+7)
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
THANKS A LOT
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
tHANKS A LOT.
i am wondering is there is any way to automatically generate the same formula all over the coloumn.
Say column C i should able to generate the same formula. I dont want to copy and drag it as it slow down my excel.
Do you have any idea?
thanks
i am wondering is there is any way to automatically generate the same formula all over the coloumn.
Say column C i should able to generate the same formula. I dont want to copy and drag it as it slow down my excel.
Do you have any idea?
thanks
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
Whether you fill the formula down manually or using code, the result will be the same.
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 hans,
What does this formula do?
=COUNTIFS('TPM FORM'!A24:A8506,"AIRBUS",'TPM FORM'!B24:B8506,">="&TODAY()-WEEKDAY(TODAY(),3),'TPM FORM'!B24:B8506,"<"&TODAY()-WEEKDAY(TODAY(),3)+7)
Does this formula will turn the value to zero during weekendS?
What does this formula do?
=COUNTIFS('TPM FORM'!A24:A8506,"AIRBUS",'TPM FORM'!B24:B8506,">="&TODAY()-WEEKDAY(TODAY(),3),'TPM FORM'!B24:B8506,"<"&TODAY()-WEEKDAY(TODAY(),3)+7)
Does this formula will turn the value to zero during weekendS?
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
No, it simply counts rows that have AIRBUS in column A, and a date from Monday to Sunday (inclusive) in the current week.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
I am trying to use the below formula
=COUNTIFS('[TPM,MAINTENANCE INPUT FORM.xlsm]TPM FORM'!A24:A5555,"AIRBUS",'[TPM,MAINTENANCE INPUT FORM.xlsm]TPM FORM'!B24:B5555,">="&TODAY()-WEEKDAY(TODAY(),3),'[TPM,MAINTENANCE INPUT FORM.xlsm]TPM FORM'!B24:B5555,"<"&TODAY()-WEEKDAY(TODAY(),3)+7)
but its not working right. Anyidea?
=COUNTIFS('[TPM,MAINTENANCE INPUT FORM.xlsm]TPM FORM'!A24:A5555,"AIRBUS",'[TPM,MAINTENANCE INPUT FORM.xlsm]TPM FORM'!B24:B5555,">="&TODAY()-WEEKDAY(TODAY(),3),'[TPM,MAINTENANCE INPUT FORM.xlsm]TPM FORM'!B24:B5555,"<"&TODAY()-WEEKDAY(TODAY(),3)+7)
but its not working right. Anyidea?
-
- Administrator
- Posts: 78545
- 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
Sorry hans,
Its working now.
My mistake. Sorry abt that
Its working now.
My mistake. Sorry abt that
-
- Administrator
- Posts: 78545
- 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
Another problem now hans,
I have two spreadsheet. One is for dashboard workbook which will be open all the time in one big screen and another is the input workbook(shared workbook).
If i use the above mentioned formula in the dashboard workbook, i can see the values only if both the spreadsheet are opened in the same computer. If i close the input workbook(shared workbook) i am not getting any data in the dashboard workbook.
I have two spreadsheet. One is for dashboard workbook which will be open all the time in one big screen and another is the input workbook(shared workbook).
If i use the above mentioned formula in the dashboard workbook, i can see the values only if both the spreadsheet are opened in the same computer. If i close the input workbook(shared workbook) i am not getting any data in the dashboard workbook.
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: LIST BOX IN EXCEL
i am getting error as #value!
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LIST BOX IN EXCEL
COUNTIFS only works with references to another workbook if that workbook is open in the same instance of Excel.
You could place the COUNTIFS formulas in the source workbook, and use a formula in the target workbook that simply refers to the cell with the COUNTIFS formula in the source workbook.
You could place the COUNTIFS formulas in the source workbook, and use a formula in the target workbook that simply refers to the cell with the COUNTIFS formula in the source workbook.
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,
Please see the attachment. In the attachment Please see the sheet TPM QRQC and sheet 1.
TPM QRQC-done by Rudi
Sheet 1- done by myself.
I created pivot table in the same way of how Rudi done. But the format is totally different in Pivot table. I would like to have the same format as u seen in TPM QRQC. What do i need to change in sheet 1 to get the TPM QRQC format pivot table?
thanks
Please see the attachment. In the attachment Please see the sheet TPM QRQC and sheet 1.
TPM QRQC-done by Rudi
Sheet 1- done by myself.
I created pivot table in the same way of how Rudi done. But the format is totally different in Pivot table. I would like to have the same format as u seen in TPM QRQC. What do i need to change in sheet 1 to get the TPM QRQC format pivot table?
thanks
You do not have the required permissions to view the files attached to this post.