Macro that calculate each sheet and paste answer in main sheet row

FaizanRoshan

Board Regular
Joined
Jun 11, 2015
Messages
54
Hi Everyone. i'm facing some trouble how to manage this because i'm new in macro. here is details what i want to do. is very know about this please help me .

1) Use “Column I” in “adt4-16 - 4-22” tab to calculate the total number of minutes per data item.

1a) You can convert the text format of ‘1h30m’ to ‘90’ by using this equation (update cell name):
=IF( ISERROR( LEFT(I6,FIND("h",I6)-1)),0, LEFT(I6,FIND("h",I6)-1))*60 + IF(ISERROR(RIGHT(LEFT(I6,FIND("m",I6)-1),2)),0,RIGHT(LEFT(I6,FIND("m",I6)-1),2))

2) Calculate data and fill in “Discharge Time Dashboard” tab cells as labeled.
2a) “<8HR Count of Data Items” is only the count of all data items that are less than 8 hours or 480 minutes
2b) “Average of <8HRs (mins)” is the average number of minutes all data items that is less than 8 hours or 480 minutes

2c) “<8HRs Savings” is calculated by: Baseline cell in $C$3 minus “Average of <8HRs (mins).” The result of this, multiplied by Cost/Hour in $D$1, multiplied by “<8HR Count of Data Items.”

2d) “<5HR Count of Data Items” is only the count of all data items that are less than 5 hours or 300 minutes

2e) “Average of <5HRs (mins)” is the average number of minutes for all data items that is less than 5 hours or 300 minutes

2f) “<5HRs Savings“ is exactly the same at 2c, “<8HRs Savings,” except it is for the <5hr data

2g) “Avg Discharge (mins)” is the average number of minutes for all data items

2h) “Count of All Data Items” is a count of all data items

2i) “All Data Savings” is exactly the same at 2c, “<8HRs Savings,” except it is for All data

3) A new tab of data will be added at each time period, whatever that is, day/week/month/year. A user will creates new tab and inserts all data for that time period (format exactly like existing) into that tab.
4) “Discharge Time Dashboard” needs to populate the dashboard with appropriate calculated data for each time period one after another. May need to insert a button to execute calculation, but open to creative ideas. No need to remove old data-tabs. 3 weeks of data (tabs) have been populated for example. In a regular time period, these data tabs would have been inserted weekly and a new line for the dashboard on “Discharge Time Dashboard” tab would populate.

If you experience please help me to solve this As much as you can.

Advance thanks for you help
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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
Back
Top