Daily Sum to Weekly Sum

Dave G

New Member
Joined
Sep 10, 2002
Messages
3
Hello,

To explain my situation, I work for an automotive supplier and deal directly with the production machinery in our facility. This machinery is fully automated. Every 8 hours the HMI (Human Machine Interface)generates a log file of alarms and events that occur during the machines operation. This log file then gets pasted into Excel. The log contains text commentsmessages in column A. I have created a Excel Workbook that counts the number of instances the message occurs and displays them in a 'daily worksheet', {=COUNTIF('Shift 1 - 22 to 6'!A:A,A2)} comparing it to a master message list containing all of the alarm/event messages in that can occur (2250 total). The log is always in a different order in its column than the master message list. At the end of a 24 hr period the totals are summed sorted and filtered in the 'daily worksheet' and a daily report is generated as to how many times a particular alarm/event occurred and which shifts they occurred on. Then the data is cleared and the next day begins. My problem is I want to have a button with a assigned macro for each day of the week to match the text in the 'weekly total' master list, then copy the 5th cell to the right into a 'weekly total' worksheet in the same workbook, and be able to retain that data in the weekly report. I've tried some index/match functions but cannot get them to work. Any advice on how to do this is greatly appreciated.

Windows NT4.0 SP6
MSExcel 97
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
Hi,

This should not be too much trouble, but we need more details for a good solution.

How is your data set up? Can you use Colo's utility to show a sample of your output and where you would like to summarize the weekly totals?
 

Dave G

New Member
Joined
Sep 10, 2002
Messages
3
Hi Jay,

Thanks for the reply. I tried the HTML Maker but I get an error message "Could not load object because it is not availble on this machine"

Can I send you the file?
 

Dave G

New Member
Joined
Sep 10, 2002
Messages
3
The solution to this problem was the VLOOKUP function.
=VLOOKUP($A2,'Shift Report'!$A:$E,5,FALSE)
Then copy and paste the result into the day of the week.
 

Forum statistics

Threads
1,144,220
Messages
5,723,085
Members
422,477
Latest member
pete101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top