Home |
Search |
Today's Posts |
#1
|
|||
|
|||
add cell across all worksheets
Hi! How do I add the sum of E21 across all but one worksheet? The
worksheet name has been changed to the first day of the week--053005, etc. E21 contains the hours worked in one week. I want to calculate totally yearly hours and then do some calculations off that number. Any help would be greatly appreciated. I'm very familiar with vba in Access, but I've done minimal in Excel. Thanks. Marcia |
#2
|
|||
|
|||
Marcia,
Move that sheet to the start and then use =SUM('first real sheet name:last sheet name'!E21) -- HTH RP (remove nothere from the email address if mailing direct) "Marcia" wrote in message ... Hi! How do I add the sum of E21 across all but one worksheet? The worksheet name has been changed to the first day of the week--053005, etc. E21 contains the hours worked in one week. I want to calculate totally yearly hours and then do some calculations off that number. Any help would be greatly appreciated. I'm very familiar with vba in Access, but I've done minimal in Excel. Thanks. Marcia |
#3
|
|||
|
|||
Thanks for the reply. here is what I have thus far. Reads the data, but...
because E21 is actually of the Time type, it reads a strange decimal number. How can I make it read as time? thanks. Marcia Private Sub CommandButton1_Click() Dim wsht As Worksheet Dim myshts As Range Dim allowshts As Sheets Dim mycel As Range Set allowshts = Worksheets Dim mytothours As Integer For Each wsht In allowshts Set myshts = wsht.Range("e21") For Each mycel In myshts If mycel.Value 0 Then mytothours = mytothours + mycel.Value End If Next mycel Next wsht End Sub "Bob Phillips" wrote in message ... Marcia, Move that sheet to the start and then use =SUM('first real sheet name:last sheet name'!E21) -- HTH RP (remove nothere from the email address if mailing direct) "Marcia" wrote in message ... Hi! How do I add the sum of E21 across all but one worksheet? The worksheet name has been changed to the first day of the week--053005, etc. E21 contains the hours worked in one week. I want to calculate totally yearly hours and then do some calculations off that number. Any help would be greatly appreciated. I'm very familiar with vba in Access, but I've done minimal in Excel. Thanks. Marcia |
#4
|
|||
|
|||
Time is just held as a fraction of a day, so if you want to see it as time,
format the result cell as [hh]:mm, or multiply by 24 to see as a decimal number. -- HTH Bob Phillips "Marcia" wrote in message ... Thanks for the reply. here is what I have thus far. Reads the data, but... because E21 is actually of the Time type, it reads a strange decimal number. How can I make it read as time? thanks. Marcia Private Sub CommandButton1_Click() Dim wsht As Worksheet Dim myshts As Range Dim allowshts As Sheets Dim mycel As Range Set allowshts = Worksheets Dim mytothours As Integer For Each wsht In allowshts Set myshts = wsht.Range("e21") For Each mycel In myshts If mycel.Value 0 Then mytothours = mytothours + mycel.Value End If Next mycel Next wsht End Sub "Bob Phillips" wrote in message ... Marcia, Move that sheet to the start and then use =SUM('first real sheet name:last sheet name'!E21) -- HTH RP (remove nothere from the email address if mailing direct) "Marcia" wrote in message ... Hi! How do I add the sum of E21 across all but one worksheet? The worksheet name has been changed to the first day of the week--053005, etc. E21 contains the hours worked in one week. I want to calculate totally yearly hours and then do some calculations off that number. Any help would be greatly appreciated. I'm very familiar with vba in Access, but I've done minimal in Excel. Thanks. Marcia |
#5
|
|||
|
|||
Thanks so much. That solved it.
Marcia "Bob Phillips" wrote in message ... Time is just held as a fraction of a day, so if you want to see it as time, format the result cell as [hh]:mm, or multiply by 24 to see as a decimal number. -- HTH Bob Phillips "Marcia" wrote in message ... Thanks for the reply. here is what I have thus far. Reads the data, but... because E21 is actually of the Time type, it reads a strange decimal number. How can I make it read as time? thanks. Marcia Private Sub CommandButton1_Click() Dim wsht As Worksheet Dim myshts As Range Dim allowshts As Sheets Dim mycel As Range Set allowshts = Worksheets Dim mytothours As Integer For Each wsht In allowshts Set myshts = wsht.Range("e21") For Each mycel In myshts If mycel.Value 0 Then mytothours = mytothours + mycel.Value End If Next mycel Next wsht End Sub "Bob Phillips" wrote in message ... Marcia, Move that sheet to the start and then use =SUM('first real sheet name:last sheet name'!E21) -- HTH RP (remove nothere from the email address if mailing direct) "Marcia" wrote in message ... Hi! How do I add the sum of E21 across all but one worksheet? The worksheet name has been changed to the first day of the week--053005, etc. E21 contains the hours worked in one week. I want to calculate totally yearly hours and then do some calculations off that number. Any help would be greatly appreciated. I'm very familiar with vba in Access, but I've done minimal in Excel. Thanks. Marcia |
#6
|
|||
|
|||
if you need to have the data copied to multiply sheets then you can use the
SUM function. eg =SUM('SheetName'!E21) But if all your going to do it calculations with it, you can referance it from the main sheet without coping it. "Marcia" wrote: Hi! How do I add the sum of E21 across all but one worksheet? The worksheet name has been changed to the first day of the week--053005, etc. E21 contains the hours worked in one week. I want to calculate totally yearly hours and then do some calculations off that number. Any help would be greatly appreciated. I'm very familiar with vba in Access, but I've done minimal in Excel. Thanks. Marcia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grand total same cell in all worksheets | Excel Worksheet Functions | |||
Cell addressing using the content of another cell. | Excel Worksheet Functions | |||
How can I do a lookup to a specific cell on multiple worksheets? | Excel Worksheet Functions | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |