probably easier for me to code it for you. what i did was write code to create a
formula. there's a shorter way, but creating the formula in the cell will show
you how the result was accomplished.
i use sheet1 and sheet2 in my code and you would need to change the names if
yours are named differently.
not sure what you know about vba, but open the
vb editor and under project
explorer you should see all of the sheets listed, double click on sheet2 and
paste the following code.
then go to sheet2 and change the values in A1: c1 and see if you get the correct
result.
i also pasted the code with line numbers in case any of it wrapped incorrectly
so you can try to place the line breaks..
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lastrow As Long
Dim rngfound As Range
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
If Not Intersect(Target, ws2.Range("C1")) Is Nothing Then
With ws.Range("A1:A" & lastrow)
Set rngfound = .Find(ws2.Range("C1"), lookat:=xlWhole)
If Not rngfound Is Nothing Then
ws2.Range("D1").Formula = "=SUMIF(Sheet1!B1:Z1," & """=""" & _
"&A1" & ",Sheet1!B" & rngfound.Row & ":Z" & rngfound.Row & _
")-SUMIF(Sheet1!B1:Z1," & """""" & "&B1" & ",Sheet1!B" & _
rngfound.Row & ":Z" & rngfound.Row & ")"
End If
End With
End If
End Sub
================================================== ===========
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lastrow As Long
Dim rngfound As Range
10 Set ws = Worksheets("Sheet1")
20 Set ws2 = Worksheets("Sheet2")
30 lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
40 If Not Intersect(Target, ws2.Range("C1")) Is Nothing Then
50 With ws.Range("A1:A" & lastrow)
60 Set rngfound = .Find(ws2.Range("C1"), lookat:=xlWhole)
70 If Not rngfound Is Nothing Then
80 ws2.Range("D1").Formula = "=SUMIF(Sheet1!B1:Z1," & """=""" &
_
"&A1" & ",Sheet1!B" & rngfound.Row & ":Z" & rngfound.Row
& _
")-SUMIF(Sheet1!B1:Z1," & """""" & "&B1" & ",Sheet1!B" &
_
rngfound.Row & ":Z" & rngfound.Row & ")"
90 End If
100 End With
110 End If
End Sub
--
Gary
"nicolairob" wrote in message
...
Gary,
That works great as long as I do not sort rows on "Sheet 1." If I insert
rows, the name range will move accordingly, but if I sort names in alpha
order for example, it remains constant regardles of whose name is in column
A. I need it to give me "Toms" hours even if I sort rows.
Thank you so much for helping me with this!
Rob
"Gary Keramidas" wrote:
ok, on sheet1, name the range B3:Z3 Tom, and do the same for every other
name
then try this formula in column d on sheet2
=SUMIF(Sheet1!B1:Z1,"="&A1&"",INDIRECT(C1))-SUMIF(Sheet1!B1:Z1,""&B1&"",INDIRECT(C1))
--
Gary
"nicolairob" wrote in message
...
Hi Gary,
Yes, the dates were in B1:Z1. However, on a separate worksheet "Sheet 2" I
will have the date 11/2/07 in (A1) and 11/4/07 in (B1). Employee names in
(C1) In (D1), I want the calculation from "Sheet 1" as presented below:
I want to know how many hrs Tom(A3) worked from 11/2/07 - 11/4/07 (C3:E3)
Now, I have to link to the name "Tom" and not the cell, as new entries on
that sheet will shift his name accordingly. Also, when I change the dates
in
"Sheet 2 (A1) and (B1), it will need to provide that time range summation
in
(D1).
Thanks,
Rob
"Gary Keramidas" wrote:
may be a simpler way, but would something like this work for you?
=SUMIF(B1:Z1,"="&B1&"",B3:Z4)-SUMIF(B1:Z1,""&E1&"",B3:Z4)
i assumed dates were in B1:Z1 and you wanted the totals from B1 to E1
--
Gary
"nicolairob" wrote in message
...
Hi,
I am having problems with summing some data with multiple criteria. It
appears that SUMIF will not work due to all ranges required to be the
same
size and shape.
Example:
"HOURS WORKED"
(A) (B) (C)
(D)
(E)
1. 11/1/07 11/2/07 11/3/07
11/4/07
2. Mary 8.0 4.5 6.0
3.5
3. Tom 7.0 2.5
3.0
6.0
4. Cindy 3.5 8.0
9.0
7.0
5. Rob 4.0 5.5
6.5
8.0
Calculation: I need to know the total hours Tom worked from 11/2/07 -
11/4/07.
Please Help and thanks so much,
Rob