how to stop an if then when true

[conwayhd]

I have two pages that I am referencing between. On the first page I have a list that constantly changes. I have one cell that will have a number between 1 and 140, say cell V9. And 18 other cells ,E28:e36, that will have varying numbers related to the first cell. On the second page I am trying to reference the first page to collect the entered numbers and make a list of the 1-140 variables. It has a row for each number 1-140 and the other 18 cells on the same row. I can use an If then, with a circular reference, that way when V9 changes on the first page it will not update the other 18 cells on the second page after that, but there has to be a better way. I know there is not an if then stop function, but is there a way to stop once the if then statement is true, and once it changes to keep the true number? Any help would be appreciated.

 

[bobsan42]

What is the meaning of constantly changing? How do you change the values - code or manually?
What is the condition to stop the cell from changing? Do you have a sample code in place?

Provide some more info.

If you change the values manually - a visual representation like conditional formatting should suffice.
if you change the values by code, there are several options:
1. Use Do while <condition> ........ Loop - loop while condition is TRUE
2. Use Do Until <condition> ........ Loop - Stop looping when condition becomes TRUE
3. For ....
If <condition> Then exit For - Exit "loop" If condition becomes TRUE

If the values change from external source which you cannot control - copy/paste the values somewhere when the condition becomes true.

 

[conwayhd]

This may help you understand better. On sheet 2 cell H18, I am using the following formula.

=IF('Sheet1'!$V$9= $D18,'Sheet'!$F$28,H18)

If cell D18 on sheet 2 equals cell v9 on sheet 1, then cell H18 on sheet 2 will equal cell F28 on sheet 1. After cell V9 on sheet 1 changes cell H18 on sheet 2 will reference itself. This is repeated many times in many other cells

 

[bobsan42]

Sorry but it doesn't help.
It only confused the issue further.
First, you didn't answer any of my questions above.
Second, You will only get an error if a cell references itself. You cannot stop a loop in a formula like this.
Normally, if you need to do iterations with a formula you will need to fill the formula down in a column for example. Keep repeating the calculations until you reach the value you need and use Conditional formatting to highlight it.

 

[conwayhd]

Bobsan42, sorry for confusing you even more, On the first page, I keep track of a runner, say runner "1". Under that is a column of 10 boxes, that I will put in different numbers for times. then when then the next runner goes, I would change it to runner 2, and change the column of numbers again.


On the second page I have chart that I want to copy the numbers over to automatically. I can do it with an IF statement, but when I change the runner from 1 to 2, the numbers in the table on tab 2 for runner 1 revert back to nothing, unless I use a circular reference. I was trying to not use a circular reference.

It is something like this on the first tab

Runner	1
time 1	15.25
time 2	25.50
time 3	45.00
Time 4	100.00

<tbody>
</tbody>

The second tab would automatically copy over

Runner	Time 1	Time 2	time 3	time 4
1	15.25	25.50	45.00	100.00
2
3
4

<tbody>
</tbody>

I think I could write a macro to do it, if it was just one or two cells, but there are 140 rows, with 18 columns. I am not even sure if what I am trying to do is possible.

Does that help you understand what I was trying to do?

 

[bobsan42]

I don't really see the point in the way you try to do it. The way you want it cannot be done with formulas on the second sheet - in this way you are trying to get different values from the same cell - CANNOT be done.
Circular reference is not a good solution IMHO. It will keep the last valid value in the cell, but this value is there only visually - it is NOT a real value and is not usable in any way. (try to sum two of these values to see what I am trying to explain).

One way to do it is write directly to the table on the second sheet (why bother with the first one?)
Second way (if you insist on two sheets) is to use a macro to copy the times from the first to the second sheet (but only the values - no formulas) before changing the player.
Another way is to use a UserForm to enter the times and the Form to write the times directly to the second sheet.

 

[conwayhd]

I don't really see the point in the way you try to do it. The way you want it cannot be done with formulas on the second sheet - in this way you are trying to get different values from the same cell - CANNOT be done.
Circular reference is not a good solution IMHO. It will keep the last valid value in the cell, but this value is there only visually - it is NOT a real value and is not usable in any way. (try to sum two of these values to see what I am trying to explain).

One way to do it is write directly to the table on the second sheet (why bother with the first one?)
Second way (if you insist on two sheets) is to use a macro to copy the times from the first to the second sheet (but only the values - no formulas) before changing the player.
Another way is to use a UserForm to enter the times and the Form to write the times directly to the second sheet.

I

I have been using a circular reference, but wanted to see about using macros, just because of the issues with the cells actually not containing anything as you mentioned above, I just could not figure out how to set up the macro to do it, if it was even possible. The reason for the two sheets is that the first sheet has additional data on it for the client, and the second is for our records. I removed the additional data from both pages since it was irrelevant.

 

[duggie33]

Maybe an Index and Match formula would do the trick…

=IFERROR(INDEX(Sheet1!$A$1:$N$11,MATCH(Sheet2!D$1,Sheet1!$A$1:$A$11,0),MATCH(Sheet2!$A5,Sheet1!$A$1:$N$1,0)),"")

Put the formula in all the cells you want to populate on sheet 2 from data on sheet 1. The ranges will probably need to be adjusted to match your layout. If you want to know more about Index and Match functions do some searching. There is a ton of information out there on how they work.

Hope that helps,

Doug

 

[duggie33]

I read through the whole thread again and concluded that the Index and Match may not work for exactly what you are looking to do. If you have a table of data on sheet 1 with multiple runners and multiple times with runners in columns and times in rows it would work.


After reading through again, it seems you need to have a macro that you would run after you have filled out the times for Runner 1, before entering data for Runner 2. The macro would copy the Runner 1 data, paste it (transposed) to next empty row on sheet 2, and clear the range on sheet 1 for the new data of Runner 2.The macro could be triggered by a button or keyboard shortcut.

Is that accurate?