Looping thru a range of cells

[COBOL Dinosaur]

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 McGimpsey]

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

 

[COBOL Dinosaur]

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.

 

[JE McGimpsey]

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?

 

[JonR]

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.

 

[COBOL Dinosaur]

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!!

 

[JonR]

(e-mail address removed)

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.

 

[Bernie Deitrick]

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]

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

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

THANK YOU!!! THANK YOU!!! THANK YOU!!!

 

[Bernie Deitrick]

COBOL,

Wouldn't that be something like?

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

;-)

Bernie