How do I loop through a range of cells (A1:Y40) looking for a match to
another cell (Z1). There will be multiple matches.
When a match is found, I want to subtract the 2 cells prior to the found
cell from each other and accumulate all of the results.
THANK YOU!!
-John Smith

If the value in Z1 is found in A1, what are "the 2 cells prior to the
found cell from each other"?


In article ,
COBOL Dinosaur wrote:

How do I loop through a range of cells (A1:Y40) looking for a match to
another cell (Z1). There will be multiple matches.
When a match is found, I want to subtract the 2 cells prior to the found
cell from each other and accumulate all of the results.
THANK YOU!!
-John Smith

JE: If the value in Z1 is found in, say, A6, then I want to subtract A4 from
A5. I then want to continue looking for more matches to Z1. The goal is to
arrive at a total for all of the subtraction calculations where there is a
match to Z1.
Z1 is someone's name. A4 is the time of day (HH:MM) they started work. A5
is the time they stopped working. I want to arrive at the total time they
worked on multiple days.
--
John Smith


"JE McGimpsey" wrote:

If the value in Z1 is found in A1, what are "the 2 cells prior to the
found cell from each other"?


In article ,
COBOL Dinosaur wrote:

How do I loop through a range of cells (A1:Y40) looking for a match to
another cell (Z1). There will be multiple matches.
When a match is found, I want to subtract the 2 cells prior to the found
cell from each other and accumulate all of the results.
THANK YOU!!
-John Smith

I still don't understand - you said you wanted to loop through A1:Y40.
Are you saying that Z1 will never be found in Rows 1 or 2?


In article ,
COBOL Dinosaur wrote:

JE: If the value in Z1 is found in, say, A6, then I want to subtract A4 from
A5. I then want to continue looking for more matches to Z1. The goal is to
arrive at a total for all of the subtraction calculations where there is a
match to Z1.
Z1 is someone's name. A4 is the time of day (HH:MM) they started work. A5
is the time they stopped working. I want to arrive at the total time they
worked on multiple days.
--
John Smith


"JE McGimpsey" wrote:

If the value in Z1 is found in A1, what are "the 2 cells prior to the
found cell from each other"?


In article ,
COBOL Dinosaur wrote:

How do I loop through a range of cells (A1:Y40) looking for a match to
another cell (Z1). There will be multiple matches.
When a match is found, I want to subtract the 2 cells prior to the found
cell from each other and accumulate all of the results.

I would set up three named ranges for name, time in, and time out, then set
up your spreadsheet with a conditional sum to consolidate the aggregate time.
Time calculations can be tricky, and you'll have to watch your formatting.

Chip Pearson has a great site with a lot of examples of time calculation. He
also describes the process for using dynamic named ranges.
http://www.cpearson.com/excel/topic.htm

You can also find (I think) a little more clear explaination of dynamic
ranges and how to use them on Jon Peltier's site
http://peltiertech.com/Excel/Charts/ChartIndex.html

If you still can't figure it out, send me an e-mail and I'll help you set up
your sheet.


--
HTH

JonR


"COBOL Dinosaur" wrote:

JE: If the value in Z1 is found in, say, A6, then I want to subtract A4 from
A5. I then want to continue looking for more matches to Z1. The goal is to
arrive at a total for all of the subtraction calculations where there is a
match to Z1.
Z1 is someone's name. A4 is the time of day (HH:MM) they started work. A5
is the time they stopped working. I want to arrive at the total time they
worked on multiple days.
--
John Smith


"JE McGimpsey" wrote:

If the value in Z1 is found in A1, what are "the 2 cells prior to the
found cell from each other"?


In article ,
COBOL Dinosaur wrote:

How do I loop through a range of cells (A1:Y40) looking for a match to
another cell (Z1). There will be multiple matches.
When a match is found, I want to subtract the 2 cells prior to the found
cell from each other and accumulate all of the results.
THANK YOU!!
-John Smith

I've got a handle on the time calculations but my spreadsheet is setup like a
calendar so I have multiple "hits" on my condition checking on each row
making monthly totaling impossible so far.
I can't find your email address or I would send you a copy of the
spreadsheet I'm working on - if you wouldn't mind taking a look at it.
THANK YOU!!
--
John Smith


"JonR" wrote:

I would set up three named ranges for name, time in, and time out, then set
up your spreadsheet with a conditional sum to consolidate the aggregate time.
Time calculations can be tricky, and you'll have to watch your formatting.

Chip Pearson has a great site with a lot of examples of time calculation. He
also describes the process for using dynamic named ranges.
http://www.cpearson.com/excel/topic.htm

You can also find (I think) a little more clear explaination of dynamic
ranges and how to use them on Jon Peltier's site
http://peltiertech.com/Excel/Charts/ChartIndex.html

If you still can't figure it out, send me an e-mail and I'll help you set up
your sheet.


--
HTH

JonR


"COBOL Dinosaur" wrote:

JE: If the value in Z1 is found in, say, A6, then I want to subtract A4 from
A5. I then want to continue looking for more matches to Z1. The goal is to
arrive at a total for all of the subtraction calculations where there is a
match to Z1.
Z1 is someone's name. A4 is the time of day (HH:MM) they started work. A5
is the time they stopped working. I want to arrive at the total time they
worked on multiple days.
--
John Smith


"JE McGimpsey" wrote:

If the value in Z1 is found in A1, what are "the 2 cells prior to the
found cell from each other"?


In article ,
COBOL Dinosaur wrote:

How do I loop through a range of cells (A1:Y40) looking for a match to
another cell (Z1). There will be multiple matches.
When a match is found, I want to subtract the 2 cells prior to the found
cell from each other and accumulate all of the results.
THANK YOU!!
-John Smith

The calendar format is probably going to give you grief. Let me take a look
and see what we can do to preserve your format and improve your function.
--
HTH

JonR


"COBOL Dinosaur" wrote:

I've got a handle on the time calculations but my spreadsheet is setup like a
calendar so I have multiple "hits" on my condition checking on each row
making monthly totaling impossible so far.
I can't find your email address or I would send you a copy of the
spreadsheet I'm working on - if you wouldn't mind taking a look at it.
THANK YOU!!
--
John Smith


"JonR" wrote:

I would set up three named ranges for name, time in, and time out, then set
up your spreadsheet with a conditional sum to consolidate the aggregate time.
Time calculations can be tricky, and you'll have to watch your formatting.

Chip Pearson has a great site with a lot of examples of time calculation. He
also describes the process for using dynamic named ranges.
http://www.cpearson.com/excel/topic.htm

You can also find (I think) a little more clear explaination of dynamic
ranges and how to use them on Jon Peltier's site
http://peltiertech.com/Excel/Charts/ChartIndex.html

If you still can't figure it out, send me an e-mail and I'll help you set up
your sheet.


--
HTH

JonR


"COBOL Dinosaur" wrote:

JE: If the value in Z1 is found in, say, A6, then I want to subtract A4 from
A5. I then want to continue looking for more matches to Z1. The goal is to
arrive at a total for all of the subtraction calculations where there is a
match to Z1.
Z1 is someone's name. A4 is the time of day (HH:MM) they started work. A5
is the time they stopped working. I want to arrive at the total time they
worked on multiple days.
--
John Smith


"JE McGimpsey" wrote:

If the value in Z1 is found in A1, what are "the 2 cells prior to the
found cell from each other"?


In article ,
COBOL Dinosaur wrote:

How do I loop through a range of cells (A1:Y40) looking for a match to
another cell (Z1). There will be multiple matches.
When a match is found, I want to subtract the 2 cells prior to the found
cell from each other and accumulate all of the results.
THANK YOU!!
-John Smith

COBOL Dinosaur,

Use the array formula (enter using Ctrl-Shift-Enter)

=SUM(IF(A3:Y40=Z1,A2:Y39-A1:Y38,0))

Note that the three ranges are offset by complete rows, but have the same number of rows and
columns....

HTH,
Bernie
MS Excel MVP


"COBOL Dinosaur" wrote in message
...
How do I loop through a range of cells (A1:Y40) looking for a match to
another cell (Z1). There will be multiple matches.
When a match is found, I want to subtract the 2 cells prior to the found
cell from each other and accumulate all of the results.
THANK YOU!!
-John Smith

THANK YOU SIR!!!!!!!!!!!!!

YOU ARE ONE SMART DUDE!!!!!!!

THANK YOU!!! THANK YOU!!! THANK YOU!!!
--
John Smith


"Bernie Deitrick" wrote:

COBOL Dinosaur,

Use the array formula (enter using Ctrl-Shift-Enter)

=SUM(IF(A3:Y40=Z1,A2:Y39-A1:Y38,0))

Note that the three ranges are offset by complete rows, but have the same number of rows and
columns....

HTH,
Bernie
MS Excel MVP


"COBOL Dinosaur" wrote in message
...
How do I loop through a range of cells (A1:Y40) looking for a match to
another cell (Z1). There will be multiple matches.
When a match is found, I want to subtract the 2 cells prior to the found
cell from each other and accumulate all of the results.
THANK YOU!!
-John Smith

COBOL,

Wouldn't that be something like?

PERFORM 3 TIMES
DISPLAY " THANK YOU!!! "
END-PERFORM

;-)

Bernie

"COBOL Dinosaur" wrote in message
...
THANK YOU SIR!!!!!!!!!!!!!

YOU ARE ONE SMART DUDE!!!!!!!

THANK YOU!!! THANK YOU!!! THANK YOU!!!
--
John Smith


"Bernie Deitrick" wrote:

COBOL Dinosaur,

Use the array formula (enter using Ctrl-Shift-Enter)

=SUM(IF(A3:Y40=Z1,A2:Y39-A1:Y38,0))

Note that the three ranges are offset by complete rows, but have the same
number of rows and
columns....

HTH,
Bernie
MS Excel MVP


"COBOL Dinosaur" wrote in
message
...
How do I loop through a range of cells (A1:Y40) looking for a match to
another cell (Z1). There will be multiple matches.
When a match is found, I want to subtract the 2 cells prior to the
found
cell from each other and accumulate all of the results.
THANK YOU!!
-John Smith