Adding Cell Value to another Sheet if...

[Guest]

On Sheet1 I have a column of account names and a column of dates of last vist
ie:

abc company 6/1/2007
xwz corp 4/15/2007
exl inc. 2/12/2007
aaa corp 6/5/2007
bbb inc. 6/11/2007

What I want to do is if the date of last visit is equal to month(today())
then I want it to list all of those accounts seperated by a comma on sheet2.
ie:

abc company, aaa corp, bbb inc.

Is that possible, if so how?

 

[Guest]

As an alternative, perhaps one formulas play
which outputs the required list into a col in Sheet2?

Source data assumed in Sheet1 cols A and B, from row1 down,
with col A = companies, col B = dates (real dates assumed)

In Sheet2,

Put in A1:
=IF(Sheet1!B1="","",IF(MONTH(Sheet1!B1)=MONTH(TODAY()),ROW(),""))

Put in B1:
=IF(ROW()>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW())))
Select A1:B1, copy down to cover the max expected extent of data in Sheet1,
say down to B100. Hide away col A. Col B will return the required list of
companies, all neatly bunched at the top.

 

[Don Guillett]

This should do it
Sub getmonthlydata()
For Each c In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
If Month(c) = Month(Date) Then
'MsgBox c.Address
ms = ms & ", " & c.Offset(, -1)
End If
Next c
'MsgBox Right(ms, Len(ms) - 1)
Sheets("sheet2").Range("d7") = Right(ms, Len(ms) - 1)
End Sub

 

[Guest]

This looks like a great way to do it, I am just a little new at writing
macros. Would you mind please being a little more descriptive so that I can
figure out how to put this thing in. I really appreciate it.

 

[Don Guillett]

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thank you

http://www.mvps.org/dmcritchie/excel/getstarted.htm