|
Looping thru a range of cells
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 |
Looping thru a range of cells
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 |
Looping thru a range of cells
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 |
Looping thru a range of cells
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. |
Looping thru a range of cells
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 |
Looping thru a range of cells
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 |
Looping thru a range of cells
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 |
Looping thru a range of cells
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 |
Looping thru a range of cells
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 |
| All times are GMT +1. The time now is 01:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com