software

I have a workbook where Sheet 1 Col A is a list of dates. Sheet 2 Col A
is a relatively short list of constantly changing names. I need to put
this list of names in a repeating fashion in Sheet 1 Col B. Ideally I
would like for Sheet 1 to regenerate Col B each time the roster list is
changed. Is there a way to do this? Thanks.What do you mean by quot;in a repeating fashionquot;? What you are asking for
involves VBA. Basically, Excel will recognize any change to the list of
names in Column A of sheet 2 and will execute whatever macro commands you
want when that happens. Post back with an explanation of quot;repeating
fashionquot;. HTH Otto
quot;smoorequot; gt; wrote in message oups.com...
gt;I have a workbook where Sheet 1 Col A is a list of dates. Sheet 2 Col A
gt; is a relatively short list of constantly changing names. I need to put
gt; this list of names in a repeating fashion in Sheet 1 Col B. Ideally I
gt; would like for Sheet 1 to regenerate Col B each time the roster list is
gt; changed. Is there a way to do this? Thanks.
gt;
Hi Otto,

Let me explain a little better. Let's say that their are 12 names on
the roster, subject to change at any date. I need that list posted to
worksheet 1 Col B as is. Then right below it the same list again then
right below that the same thing again and so on for as many dates as I
have in worksheet 1 Col A. Then I would like to be able to alter the
roster as needed and have col B change with it. Thank you for taking a
look at this.smoore
Here are a couple of macros to do what you want. These macros are
written with these conditions:
The first sheet is named quot;Firstquot;.
The name of the second sheet doesn't matter.
The roster is in the second sheet in Column A starting in A2.
The dates are in the first sheet in Column A starting in A2.
The macros will copy the roster and paste it into Column B of the first
sheet starting with B2.
The macros will cut off the entries in Column B of the first sheet at the
last date entry in Column A.

Note that the first macro below is a Worksheet_Change macro and must be
placed in the sheet module of the second sheet. To access that module,
right-click on the sheet tab of the second sheet, select View Code, and
paste the macro into that module. Click on the quot;Xquot; at the top right corner
of the module display to return to your spreadsheet.
The second macro below must be placed in a standard module.
In case you are unsure of where/how to place the macros, or word-wrap messes
up the code in this message, I am emailing you the small file I used for
this. I am sending this file to the email address that is attached to your
post. If this address is not valid, email me with a valid address and I'll
send you the file. My email address is . Remove the
quot;nopquot; from this address. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(quot;A:Aquot;)) Is Nothing _
And Target.Row gt; 1 Then _
Call UpdateData
End Sub

Sub UpdateData()
Dim Rng2ndNames As Range
Dim LastDate As Range
Set Rng2ndNames = Range(quot;A2quot;, _
Range(quot;Aquot; amp; Rows.Count).End(xlUp))
With Sheets(quot;Firstquot;)
If Not IsEmpty(.[B2].Value) Then .Range(quot;B2quot;, _
.Range(quot;Bquot; amp; Rows.Count).End(xlUp)).ClearContents
Do Until IsEmpty(.Range(quot;Bquot; amp;
Rows.Count).End(xlUp).Offset(, -1).Value)
Rng2ndNames.Copy .Range(quot;Bquot; amp; Rows.Count).End(xlUp).Offset(1)
Loop
Set LastDate = .Range(quot;Aquot; amp; Rows.Count).End(xlUp)
If Not IsEmpty(LastDate.Offset(1, 1).Value) Then _
.Range(LastDate.Offset(1, 1), _
.Range(quot;Bquot; amp; Rows.Count).End(xlUp)).ClearContents
End With
End Sub

quot;smoorequot; gt; wrote in message ups.com...
gt; Hi Otto,
gt;
gt; Let me explain a little better. Let's say that their are 12 names on
gt; the roster, subject to change at any date. I need that list posted to
gt; worksheet 1 Col B as is. Then right below it the same list again then
gt; right below that the same thing again and so on for as many dates as I
gt; have in worksheet 1 Col A. Then I would like to be able to alter the
gt; roster as needed and have col B change with it. Thank you for taking a
gt; look at this.
gt;
Otto,
Thanks for a perfect solution. Greatly appreciated.

ScottyThanks for the feedback. Otto
quot;smoorequot; gt; wrote in message ups.com...
gt; Otto,
gt; Thanks for a perfect solution. Greatly appreciated.
gt;
gt; Scotty
gt;