Question about dates.

superbeast326

Board Regular
Joined
Nov 12, 2011
Messages
132
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?
 
Actually, I want to populate Column C, 48 rows down, with the formula
Code:
=8-INT((ROW()-1)/6)

I wanted to fill column A with the other half of the number pair. So I used the formula
Code:
=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?
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
What would the results be in column B for rows 1-13?
 
Upvote 0
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
 
Upvote 0
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
ABC
1768
2758
3748
4738
5728
6718
7867
8857
9847
10837
11827
12817
13586
14576
15546
16536
17526
18516
19685
Formula
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top