Loop to calculate value until next cell with value


I'm not even sure if this is possible.  You are brilliant if you can do it!

I have a spreadsheet that contains test cases.  Column A has an ID for every separate test case.  If column A has an ID (other cells will be blank) then look at all values for that test case and input the status.

It will look like this:
Column A has the value of 1 on row 15 and the value of 2 on row 20.  
Column F has the text Pass, Fail, Skipped or Blocked or blank in the rows 16-19.  These values are also stored as 1 if true in columns G - K ; meaning column G will have a 1 if the step passed, H will have a 1 if it is failed, J will have a 1 if blocked, and K will have a 1 if skipped.  

I need to look at the values and if any step fails, place a 1 in Column F of the same row that contains the ID - row 15.  If all steps pass, then a 1 will be placed in column H of row 15.  

I've attached a spreadsheet to help explain.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
1)  Why do you want to replace the values in column F with the ID number if there is a failure?
2)  Why do you want to replace the formula(s) in column H with the ID number is there is no failure?
 Is it possible you want this result in another column?

Unrelated to your question, but you have a circular reference error on the "Metrics" sheet.

KathtgAuthor Commented:
Hi Glenn

Sorry, I was so confused in trying to work on this all day that I may have missed the verbage.   Every test case has a row that will count as a header.  The header will contain the ID column and a column to represent each status - Pass, Failed, Blocked and Skipped.  This will give the overall status of the test case and I will use this value on the metrics sheet.  

There will be an indeterminate amount of rows under the header - any number of rows that house the test case steps.  Each step will receive a status - either Pass, Fail, Blocked, Skipped.  If there is a 1 in any column under Failed, I want a 1 in the header column for Fail and same with the others.  

Does this  make more sense?

ps.  I haven't seen the metrics error but will look, thank you.  I think the Metrics sheet is completely blown up right now.  I corrupted it in trying to work on this - everything in Excel even started writing backwards at one point.  (that kind of day)
KathtgAuthor Commented:
I've requested that this question be deleted for the following reason:

It seems that I didn't portray the question well.  I need help with a loop.  The only help that I received was to question my reason for wanting to do it and that something else is wrong in my spreadsheet.  I don't think I explained this well and will search the internet for a solution.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Glenn RayExcel VBA DeveloperCommented:
It's only been one day since the initial post.  I think I can resolve the issue, but misunderstood the questioner's request.
Glenn RayExcel VBA DeveloperCommented:
Restating the request to make sure I understand what you'd like to see:

For each row containing an ID number in column A, check all step results in columns H:K.  If there is a "1" result for any step within a specific column, return "1" as a result on the same row as the ID.

This is doable, and might be possible without any VBA using dynamic ranges.  

Question:  What do you want to see if there is no true result (i.e., if not "1", then display what instead)?  

Just an early recommendation:  Change the FALSE results to zero (0) so that some arithmatic can be used.

KathtgAuthor Commented:
Thanks Glenn.  I was just trying to restructure my wording.  yes, that is exactly what I need.  I actually did change the False results to 0.  I attached the file again (still haven't looked at metrics sheet, I needed a break from it last night).  The sheet is 'James'.
Glenn RayExcel VBA DeveloperCommented:
I can't see your new attachment, but that's okay; I've begun editing a copy of your original.

I'm working on resolving the dynamic range aspect.  I'm also adding some data validation to your cells in column F ("Pass/Fail") to standardize/limit entry to your four valid values.

KathtgAuthor Commented:
I don't know you, but you are truly wonderful.  Thanks and sorry for the sheet.  I can attach it again but it seems you're good.  Let me know if you decide otherwise.
Glenn RayExcel VBA DeveloperCommented:
Still working on this...may have to use VBA to get the totals to populate properly, however I do have a couple of questions.

If the "total row" for each ID returns a 1 or 0 only, the totals in the section above (D3:D7) will not be correct, since they will include the total row amounts.

Also, shouldn't each step have a TFS # in column B?  Data here is used for the "Count" column (G).

KathtgAuthor Commented:
Thanks Glenn.  

If the "total row" for each ID returns a 1 or 0 only, the totals in the section above (D3:D7) will not be correct, since they will include the total row amounts.  - I think you mean that if the ID in column A is 1 or 0, that it will not work.  Is this correct?  I can have them start at a different number.  Would this be the easiest solution?

I was planning to change the count column to go along with the ID in column A but yes, it can also be on the TFS # column.  Either works for my needs.
Glenn RayExcel VBA DeveloperCommented:
I think the total amounts on top can be changed to SUMIF statements that will limit the sum to just Total Rows (that is, only sum up rows where there is an ID in column A).

It's killing me that I can't find a dynamic range solution; I think it's possible and am close.  Trying to determine exactly how many rows there are for a given ID is difficult when not all values in column A contain the ID. Otherwise, it would be very easy.  But I'm still up for the challenge.

KathtgAuthor Commented:
thank you so much Glenn.  I am sorry that it is not an easy one!  Is there a way that I can give you extra points - I'll look
Glenn RayExcel VBA DeveloperCommented:

I had to do some "smoke and mirrors" to make this work, but I think I have the start of a solution for you.  The summary totals on top still need to be revised; it depends on how each test case subtotal is shown.

1) I changed the data for the ID column to contain the ID for each row of the test case.  There is a conditional format rule applied to column A that hides the repetitive ID values.    I made this change in order to be able to detect the number of steps for each task, regardless of the number.

2) I changed the formatting for the cells in H11:K11 so that the entire words "Pass", "Fail", "Blocked", "Skip" are actually contained in the cells, but only the first letter appears (as originally).  This allow a standardized formula to be used throughout those columns, based on this formula in H15:
=IF($A15<>$A14,SUM(OFFSET(H15,1,0):OFFSET(H15,COUNTIF($A15:$A50,$A15)-1,0)),IF($F16=H$11, 1,0))

3) There is a standard formula for column G, but it's slightly different because the criteria for displaying a Count value is different.  This is the formula in G15, copied down:

I've attached an example file but only edited the "James" sheet.  

Let me know if this will work and what changes need to be made to make it work for you.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KathtgAuthor Commented:
oh Glenn, this will actually work perfect!!!  You definitely met this challenge.  Is there a way that I can aware MORE points to you?  Serious question, I've only found the ability to give an A
Glenn RayExcel VBA DeveloperCommented:
Well, the totals on top are only correct in displaying the number of "steps" that meet each criteria and the "Total Test Cases" value is totally wrong.  It can be fixed by changing the formula for "Count" (col G) to just display 1 or 0 if there are any steps (rather than summing the number of steps).

I appreciate the offer for extra points, but so long as you're satisfied with this solution, there's no need.

KathtgAuthor Commented:
I didn't look at the top but I can do those.  With what you have given me, I am confident that it will meet my needs and I can finish tweaking it.  Thanks so much again.  I hope you have a great one, you made mine!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.