Solved

# Loop to calculate value until next cell with value

Posted on 2014-11-04
167 Views
Hello,

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.
Testing-metrics.xlsm
0
Question by:Kathtg
• 8
• 8

LVL 27

Expert Comment

ID: 40422670
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.

-Glenn
0

Author Comment

ID: 40422784
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)
0

Author Comment

ID: 40424387
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.
0

LVL 27

Expert Comment

ID: 40424388
It's only been one day since the initial post.  I think I can resolve the issue, but misunderstood the questioner's request.
0

LVL 27

Expert Comment

ID: 40424429
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.

-Glenn
0

Author Comment

ID: 40424437
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'.
0

LVL 27

Expert Comment

ID: 40424448
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.

-Glenn
0

Author Comment

ID: 40424481
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.
0

LVL 27

Expert Comment

ID: 40426667
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).

-Glenn
0

Author Comment

ID: 40427179
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.
0

LVL 27

Expert Comment

ID: 40427311
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.

-Glenn
0

Author Comment

ID: 40427403
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
0

LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 40429524
Hey,

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:
=IF(\$A15<>\$A14,SUM(OFFSET(G15,1,0):OFFSET(G15,COUNTIF(\$A15:\$A50,\$A15)-1,0)),IF(B15<>"",1,0))

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.

Regards,
-Glenn
EE-Testing-metrics.xlsm
0

Author Comment

ID: 40429535
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
0

LVL 27

Expert Comment

ID: 40429566
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.

Regards,
-Glenn
0

Author Comment

ID: 40429621
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!
0

## Featured Post

### Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦