Returning values basic question (probably)

[Heartlight]

Hi there,

I am trying to create a formula that scans a column and, if there is any data in that column, list the data on another sheet.

For instance, one sheet is called data and the data sheet has the column 'comments'.
The Comments column has some rows that are blank and some that have text:
Comments
1 Great job!
2 Boring
3
4
5 What an ultra maroon.

I have another sheet (called comments) that I want to compile just the comments:
Comments
1 Great job!
2 Boring
3 What an ultra maroon.
4
5

If you're interested in the reason for this, I'm tabulating a survey and most of it is statistical data; however, there is a comments section I would like to list, along with my calculations of the statistical data. But I want the program to do the heavy lifting and extract all the comments and place them in a nice, neat and orderly row progression.

Any suggestions are appreciated, thank you!

 

[My Aswer Is This]

I could write you a vba script so when a comment is entered into the comment column
It would be immediately entered into another sheet.

But please do not say Comment column please tell me column A or Column G
And into what column on the sheet named "Comment" do you want these comments entered. Again say column B or F do not say column Comment.

This would be done with Vba

 

[Heartlight]

That's awesome, thanks!

The sheet where the data is coming from is called "Data" and the column is B.
The sheet where the data is going is called "Understanding" and the column is A.

Thanks again!

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab Named Data
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 5/10/18 5:00 PM EDT
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Understanding").Cells(Rows.Count, "A").End(xlUp).Row + 1
Target.Copy Sheets("Understanding").Cells(Lastrow, 1)
End If
End Sub

Any time you enter a value in column B this data will be copied to sheet named Understanding​

 

[Heartlight]

Hi and thanks!

I did as you instructed. It worked for data that hadn't been entered yet - but since I'm just setting up this sheet, that's just fine.

Thank you!

 

[My Aswer Is This]

Hi and thanks!

I did as you instructed. It worked for data that hadn't been entered yet - but since I'm just setting up this sheet, that's just fine.

Thank you!

Yes this script works when you enter data. It does not go and copy all values already entered.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.