 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2007Looping 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 If the value in Z1 is found in A1, what are "the 2 cells prior to the found cell from each other"? > 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.  SignatureJohn Smith > If the value in Z1 is found in A1, what are "the 2 cells prior to the > found cell from each other"? [quoted text clipped - 5 lines] > > 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? > 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 [quoted text clipped - 11 lines] > > > 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. SignatureHTH JonR > 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 [quoted text clipped - 13 lines] > > > 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!! SignatureJohn Smith > 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. [quoted text clipped - 30 lines] > > > > THANK YOU!! > > > > -John Smith jonathan.roberts@jpmchase.com 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. SignatureHTH JonR > 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 [quoted text clipped - 37 lines] > > > > > 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 > 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!!! SignatureJohn Smith > COBOL Dinosaur, > [quoted text clipped - 15 lines] > > THANK YOU!! > > -John Smith COBOL, Wouldn't that be something like? PERFORM 3 TIMES DISPLAY " THANK YOU!!! " END-PERFORM ;-) Bernie > THANK YOU SIR!!!!!!!!!!!!! > [quoted text clipped - 23 lines] >> > THANK YOU!! >> > -John Smith |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.