Solved

Loop to calculate value until next cell with value

Posted on 2014-11-04
16
193 Views
Last Modified: 2014-11-07
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
Comment
Question by:Kathtg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
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

by:Kathtg
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

by:Kathtg
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
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

by:Glenn Ray
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

by:Kathtg
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

by:Glenn Ray
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

by:Kathtg
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

by:Glenn Ray
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

by:Kathtg
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

by:Glenn Ray
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

by:Kathtg
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

by:
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

by:Kathtg
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

by:Glenn Ray
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

by:Kathtg
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question