Results from a Concatenate function

Microsoft Excel 2013 32/64-bit - license...
December 14, 2017 at 08:43:52
Specs: Windows 8
I have the following function in a master sheet: =CONCATENATE('Big Spring'!AA3,DuBois!AA3,'Houston - FWP'!AA3,'Savage-Ames'!AA3,Sayre!AA3,'Trenton Pipe Yard'!AA3,'Trenton-EMI'!AA3,Williston!AA3)

The problems are:

1. there is an existing value in each of the cells. I only want the concatenated information if the cell has changed.

2. How do I format the information so that when it is returned each comment is on a separate line within the cell (or at least spaced out.

Can anyone help?


See More: Results from a Concatenate function

Report •

#1
December 14, 2017 at 09:32:00
1. there is an existing value in each of the cells. I only want the concatenated information if the cell has changed.

If you use a Worksheet_Change macro (in each sheet except for Master) you can write the formula into the Master sheet when a change is made to any sheet. Of course, once it's there, it's there, unless you take some other action (manual or macro) to erase it.

2. How do I format the information so that when it is returned each comment is on a separate line within the cell (or at least spaced out.

Format the cell for Wrap Text then use:

=CONCATENATE('Big Spring'!AA3,CHAR(10),DuBois!AA3,CHAR(10),'Houston - FWP'!AA3, etc.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
December 14, 2017 at 09:58:41
Thank you DerbyDad03 but you lost me at

If you use a Worksheet_Change macro (in each sheet except for Master) you can write the formula into the Master sheet when a change is made to any sheet. Of course, once it's there, it's there, unless you take some other action (manual or macro) to erase it.

I only need to record 3 columns in the master. AA, AB, AC. These cells have instructional text in them (as per my posts from yesterday). Once the user clicks in the cell and adds their comments, I want to take any comments and put it in a master sheet (which is the concatenate formula). I don't want the instructions to display only the comments entered by the user. Can anyone help write code for this?

I have searched for Worksheet_Change but am coming up empty handed. The VBA training I am reviewing is over my head. I am reading your article on Debugging VBA Code 101, A Tutorial to see if that will help at all.

Thanks
Danielle

message edited by Daniej


Report •

#3
December 14, 2017 at 17:53:07
OK, I'll explain it in some more detail, but still as a general concept. Until we know your exact process, it will be a difficult to provide specific code.

Make sure that you read this all the way through. I'm going to explain the general concept with a simple example and then point out a possible problem. You'll have to tell us if that "possible problem" will be an actual problem. If so, then we're going to have get deeper into this.

Let's say Master!A1 is the location where you want the Concatenate formula.
Let's say 'Big Spring'!AA3 and DuBois!AA3 contain instructions, not comments.

If I understand your requirements correctly, you want Master!A1 to be empty at this point. Once the user enters comments in 'Big Spring'!AA3 and DuBois!AA3 , you want those comments Concatenated in Master!A1.

In Big Spring you could use a Worksheet_Change to monitor AA3 and when it changes, you have the code write the Concatenate formula in Master!A1

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$AA$3" Then
  Sheets("Master").Range("$A$1").Formula = _
         "=CONCATENATE('Big Spring'!AA3,'DuBois'!AA3)"
 End If
End Sub

In a very general sense, this fulfills your requirement of not seeing the instructions, just the comments.

However, here's a possible problem: This solution requires that Big Spring!AA3 be changed in order for the formula to end up in Master!A1. If Big Spring!AA3 will always be changed then you are all set, except....

What if Big Spring is changed but Dubious isn't? What will happen is that you'll end up with the Comment from Big Spring and the instruction from Dubious. Not pretty.

If on the other hand, all AA3's will be changed from instructions to comments, then there will only be a mixture of comments and instructions in Master!A1 while the comments are being added. Once all the comments are in, Master!A1 will look just fine.

So you need to tell us if all of the AA3 cells in your formula will have comments entered or not. If not then we need to find a way to build a formula that ignores instructions and only concatenates cells with comments. Doable, but complicated.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
December 15, 2017 at 07:27:34
DerbyDad03... To answer your question no, not all AA3 cells will be changed on all sheets.

Each page is a different location and the 3 columns (AA, AB, AC), I am referring to, are columns where the end-user adds comments. So not all locations will have comments.

DerbyDad03 - any more help regarding this?

message edited by Daniej


Report •

#5
December 21, 2017 at 10:03:22
This problem has not been resolved.

Report •

Ask Question