Looping thru a range of cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"?
 
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.
 
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?
 
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.
 
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!!
 
(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.
 
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
 
THANK YOU SIR!!!!!!!!!!!!!

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

THANK YOU!!! THANK YOU!!! THANK YOU!!!
 
COBOL,

Wouldn't that be something like?

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

;-)

Bernie
 
Back
Top