Question about dates.

[superbeast326]

I have two columns, A and B. Both have 50 rows. Column A is filled entirely with "No". What I want is that the moment I switch a row in Column A to "Yes". I want the row in Column B to store the date that the change was made. Is this possible?

 

[superbeast326]

Actually, I want to populate Column C, 48 rows down, with the formula

=8-INT((ROW()-1)/6)

I wanted to fill column A with the other half of the number pair. So I used the formula

=IF(ISEVEN(C1),C1-1,C1+1)

However for column B, I want to keep repeating the pattern,
8
7
6
5
4
3
2
1
, while skipping the numbers that are located in both Column A and C. Do you have any idea how I can achieve this?

 

[Peter_SSs]

What would the results be in column B for rows 1-13?

 

[superbeast326]

The result will be like this:
Column A Column B Column C
7 6 8
7 5 8
7 4 8
7 3 8
7 2 8
7 1 8
8 6 7
8 5 7
8 4 7
8 3 7
8 2 7
8 1 7
5 8 6
5 7 6
5 4 6
5 3 6
5 2 6
5 1 6
6 8 5
6 7 5
6 4 5
6 3 5
6 2 5
6 1 5
3 8 4
3 7 4
3 6 4
3 5 4
3 2 4
3 1 4
4 8 3
4 7 3
4 6 3
4 5 3
4 2 3
4 1 3
1 8 2
1 7 2
1 6 2
1 5 2
1 4 2
1 3 2
2 8 1
2 7 1
2 6 1
2 5 1
2 4 1
2 3 1

 

[Peter_SSs]

Firstly, I would recommend a change to your existing C1 formula
=8-INT((ROW()-1)/6)
as it isn't very robust. If you subsequently decide to add a new row at the top - say for a heading - your formula will produce different results. I would use the one shown below my screen shot instead.

A suggestion for column B is also shown below.

Excel Workbook
	A	B	C
1	7	6	8
2	7	5	8
3	7	4	8
4	7	3	8
5	7	2	8
6	7	1	8
7	8	6	7
8	8	5	7
9	8	4	7
10	8	3	7
11	8	2	7
12	8	1	7
13	5	8	6
14	5	7	6
15	5	4	6
16	5	3	6
17	5	2	6
18	5	1	6
19	6	8	5
Formula

 

[superbeast326]

Is there a VBA alternative for column B?

Also, could I please have an explanation for the formula in Column B.

 

[superbeast326]

One more question:

If I were to use For Each c for a two-dimensional range. In what order does the For Loop go through the range?

 

[Peter_SSs]

Is there a VBA alternative for column B?

Yep, here's one

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> FillB()<br>    Range("B1:B48").Formula = "=MID(SUBSTITUTE(SUBSTITUTE(""87654321"",A1,""""),C1,""""),MOD(ROWS(B$1:B1)-1,6)+1,1)+0"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Also, could I please have an explanation for the formula in Column B.

Take the string 87654321, remove the two digits on each side of the formula cell, then pick the digit out of what is left that correspodes to the row number (in groups of 6)

One more question:

If I were to use For Each c for a two-dimensional range. In what order does the For Loop go through the range?

Do a test and see. For example, set up such a loop. All that needs to be inside the loop is to select c, and step through the code with F8.

 

[superbeast326]

I did a test for the for loop question. Apparently, it goes through the range row-by-row. I actually figured out how the formula in Column B works. By the way, if I have a list of names and the names are numbered in the column to its left and if I were to define the formula in Column B with a name, I can use that name in a VLOOKUP right?

 

[superbeast326]

By the way, thank you.

 

[Peter_SSs]

I did a test for the for loop question. Apparently, it goes through the range row-by-row.

Correct.

I actually figured out how the formula in Column B works.

Great.

By the way, if I have a list of names and the names are numbered in the column to its left and if I were to define the formula in Column B with a name, I can use that name in a VLOOKUP right?

I didn't really understand that fully, but in any case if you continue to ask "Will this work" my answer will continue to be "Give it a try and see"


Edit: BTW, you can edit your post for 10 minutes to add extra things. No real need for a separate post

By the way, thank you.

No problem.