Concatenate issue

[Daniej]

I really need help. I need to put together a spreadsheet for my boss. I do not know VBA (at all - someone helped with the macro below)

I have several issues.

1. Using the formula below in the Master sheet, I need to bold the name of the sites (Big Spring, Dubois, etc) when it returns a result. How can I do this?


=CONCATENATE("Big Spring: ", 'Big Spring'!AA3, "; ", "DuBois: ", DuBois!AA3, "; ", "Houston - FWP: ", 'Houston - FWP'!AA3, "; ", "Savage-Ames: ", 'Savage-Ames'!AA3, "; ", "Sayre: ", Sayre!AA3, "; ","Trenton Pipe Yard: ", 'Trenton Pipe Yard'!AA3, "; ", "Trenton-EMI: ", 'Trenton-EMI'!AA3, "; ", "Williston: ", Williston!AA3)


2. In each sheet from the above example (in columns AA, AB, AC) there is existing instructional text (that disappears and changes color when new text has been entered (Done in VBA - see below). I want the above formula to extract when new text has been entered. (I only want the concatenated results to return information if someone has changed the information in cells AA, AB, AC.). How do I do this?

VBA code
Private Sub Worksheet_Change(ByVal Target As Range)
'
If Target.Column = 27 Then
Target.Font.Color = Automatic
End If
'
If Target.Column = 28 Then
Target.Font.Color = Automatic
End If
'
If Target.Column = 29 Then
Target.Font.Color = Automatic
End If
'
Target.Interior.Color = xlNone
End Sub




3. How do I format the information so that when it is returned each comment is on a separate line within the cell.
I am at my wits end trying to figure this out... Can anyone help?

Danielle

 

[Eric W]

Re: I have a concatenate issue. Can anyone help?

Welcome to the board!

First, you can't bold part of a cell using formulas, you'll need to build the CONCATENATEd value with VBA. Next, is that formula a single formula, or is it dragged down/across, so that there are actually several cells that need bolding? I don't understand what you mean when you say

(I only want the concatenated results to return information if someone has changed the information in cells AA, AB, AC.)

Excel formulas automatically change when the referenced cells are changed.

Your third point should be easy enough - if it's done in VBA, then when the text is built, we can add a line feed after each item, and you format the cell with Word Wrap.

So based on how I understand this, I'd suggest adding an event handler to your Master sheet. Whenever it is selected, it fires, looks up the data from the other sheets, formats it, and puts it in the cell(s) you want. Let me know if this sounds feasible.

 

[Daniej]

Re: I have a concatenate issue. Can anyone help?

Welcome to the board!

First, you can't bold part of a cell using formulas, you'll need to build the CONCATENATEd value with VBA. Next, is that formula a single formula, or is it dragged down/across, so that there are actually several cells that need bolding? I don't understand what you mean when you say Excel formulas automatically change when the referenced cells are changed.

Your third point should be easy enough - if it's done in VBA, then when the text is built, we can add a line feed after each item, and you format the cell with Word Wrap.

So based on how I understand this, I'd suggest adding an event handler to your Master sheet. Whenever it is selected, it fires, looks up the data from the other sheets, formats it, and puts it in the cell(s) you want. Let me know if this sounds feasible.

Eric,
Thank you so much for getting back to me. To explain a little... I have a spreadsheet of different locations. In the spreadsheet I have 7 sheets: the master and 6 locations. I've set the spreadsheet up so that all cells (except the last 3 columns) have a drop down list. The only item on the list is yes. If an end user selects yes than it is registered (and counted) in the master.

The last 3 columns are for comments. I have a VBA trigger set up so that the cell contains red instructional text with a grey background. Once an end-user clicks in the cell the instructions disappear and the text is black with a white background. What I am trying to do is concatenate the cells in the master so that when text is entered in one of the last 3 columns (AA, AB, AC) it is displayed on the master. The problem is I don't want to see the instructions... all I want are comments if they have been entered in one of the sheets. MY WISH - would be that each locations entry would be on a separate line (within the same cell), the location name be bold and no instructional text is displayed in the master.

Now I know absolutely nothing about VBA. The formula I have was given to me by someone else. I am sure that there can be more than one instruction in the VBA window (for a sheet) but that is my knowledge. I am however a decent Excel user (more advanced than most).

There you go Eric... can you help?

 

[Eric W]

Re: I have a concatenate issue. Can anyone help?

I'm still a little hazy on the concept, but here's a first draft:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str1 As String, sl(100, 2) As Long, ctr As Long, ws As Worksheet

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Row < 4 Or Target.Row > 10 Then Exit Sub
    If Target.Column < 27 Or Target.Column > 29 Then Exit Sub
    
    Target.Font.Color = xlAutomatic
    Target.Interior.Color = xlNone
    
    ctr = 0
    For Each ws In Worksheets
        If ws.Name <> "Master" Then
            ctr = ctr + 1
            sl(ctr, 1) = Len(str1) + 1
            sl(ctr, 2) = Len(ws.Name) + 1
            str1 = str1 & ws.Name & ": "
            
            If ws.Range(Target.Address).Font.Color <> xlAutomatic Then str1 = str1 & ws.Range(Target.Address).Value
            str1 = str1 & vbCrLf
        End If
    Next ws
    
    Sheets("Master").Range(Target.Address) = str1
    For i = 1 To ctr
        Sheets("Master").Range(Target.Address).Characters(Start:=sl(i, 1), Length:=sl(i, 2)).Font.Bold = True
    Next i
    
End Sub

Replace your current VBA code on each of your worksheets with this. It will change the font color as before, but when that's done, it will collect the data from all the worksheets and create the concatenated string on your master sheet.

Some thoughts: the 4 and 10 in the code refer to the lower and upper rows. Anything outside that range won't do anything. Change as needed. The 27 and 29 in the next line refer to columns (AA:AC). The name of the master sheet I used "Master" in red. Change as needed. Also, every sheet except Master will be included in the string. If you have other sheets, we can exclude them too. There is also a limit of 100 region sheets, also easily changed.

Finally, I didn't know where you wanted to put the string. So I just put the results from the AA5 cell in AA5 on the Master sheet (and so on). Also easily changed if you tell me where you want it.

Let me know what you think!

 

[Daniej]

Re: I have a concatenate issue. Can anyone help?

Eric I am not sure if this will work (but I may be wrong, so I want to clarify):

Users will click on their location (worksheet) and select "Yes" on the pull down menu (Data Validation list) in cells D3:Z46. The yes will signify that the job (second row) has to perform specific duties (2 first columns). I have used the following formula in the master in the corresponding cell (to match the sheet cells) =COUNTA('Big Spring:Williston'!A3). Since the sheets are set up to only select a yes or leave it alone then this formula does the job (I know I could have used a count with an if statemet)

In cells AA3:AC46 I want the end user to enter information regarding their Yes selections in that row.

Each location sheet is set up so that cells AA3:AC46 display instructions on what I want in the cell. These instructions are in red with a grey background. When a user types in one of those cells the instructional text color is cleared to Automatic and the background is cleared to Automatic.

The VBA code i used is:

Private Sub Worksheet_Change(ByVal Target As Range) '
If Target.Column = 27 Then
Target.Font.Color = Automatic
End If
'
If Target.Column = 28 Then
Target.Font.Color = Automatic
End If
'
If Target.Column = 29 Then
Target.Font.Color = Automatic
End If
'
Target.Interior.Color = xlNone
End Sub

I want all the user entered text to go into a cell on the master sheet (this should list all information (from AA3 on all sheets into the master sheet). I have the following concatenated formula (so it would gather information from sheet 2, sheet 3, sheet 4 cell AA3 into the Master sheet cell AA3 one comment after the other) with the location name listed first.

=CONCATENATE("Big Spring: ", 'Big Spring'!AA3, "; ", "DuBois: ", DuBois!AA3, "; ", "Houston - FWP: ", 'Houston - FWP'!AA3, "; ", "Savage-Ames: ", 'Savage-Ames'!AA3, "; ", "Sayre: ", Sayre!AA3, "; ","Trenton Pipe Yard: ", 'Trenton Pipe Yard'!AA3, "; ", "Trenton-EMI: ", 'Trenton-EMI'!AA3, "; ", "Williston: ", Williston!AA3)

The problem is it pulls in the instructions I put in the cell (which means I have some user text and some instructional text)... I don't want the instructional text just information the end user wrote. Which means that I want only the information that has changed in those columns (AA3:AC46) entered in their respective columns in the Master. Please remember that end users will select Yes on each worksheet that is also gathered in their respective cells (the counta formula)

Given this information MY WISH - would be that each locations entry would be on a separate line (within the same cell (in the example MaterSheet:AA3), the location name be bold and no instructional text is displayed in the master.

Do you still think the above will work? Do you have enough information/clarification?

Thank you for all your help!

 

[Eric W]

Re: I have a concatenate issue. Can anyone help?

It should. Open a copy of your workbook and try it. It does nothing with the D3:Z46 cells, it just looks at the AA:AC columns. As I said in the previous post, you'll need to change the row numbers from 4 and 10 to 3 and 46. What it does is look at the formatting of the cells to decide if it is your instruction, or entered text.

 

[Daniej]

Re: I have a concatenate issue. Can anyone help?

Eric we are close with some of it... unfortunately the master cell still displays the instructional text from the cells in the location sheets along with the information I typed in. That said the locations do bold!!!!!

When I type in a sheet in AA3:AC46 the text is no longer visible - i think it goes white.

Danielle

 

[Eric W]

Re: I have a concatenate issue. Can anyone help?

One thing I noticed in your original code:

[COLOR=#333333][FONT=arial]Target.Font.Color = [/FONT][/COLOR][COLOR=#ff0000][FONT=arial]Automatic[/FONT][/COLOR]

I really expected that to be xlAutomatic, but didn't think too much of it at the time. Since Automatic is not defined anywhere, it will default to 0, which renders my check for the formatting incorrect. Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str1 As String, sl(100, 2) As Long, ctr As Long, ws As Worksheet, i as Long

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Row < 3 Or Target.Row > 46 Then Exit Sub
    If Target.Column < 27 Or Target.Column > 29 Then Exit Sub
    
    Target.Font.Color = xlAutomatic
    Target.Interior.Color = xlNone
    
    ctr = 0
    For Each ws In Worksheets
        If ws.Name <> "Master" Then
            ctr = ctr + 1
            sl(ctr, 1) = Len(str1) + 1
            sl(ctr, 2) = Len(ws.Name) + 1
            str1 = str1 & ws.Name & ": "
            
            If ws.Range(Target.Address).Interior.Color = xlNone Then str1 = str1 & ws.Range(Target.Address).Value
            str1 = str1 & vbCrLf
        End If
    Next ws
    
    With Sheets("Master").Range(Target.Address)
        .Value = str1
        .Font.Bold = False
        For i = 1 To ctr
            .Characters(Start:=sl(i, 1), Length:=sl(i, 2)).Font.Bold = True
        Next i
    End With
    
End Sub

I made one other tweak to handle the bolding better.

 

[Daniej]

Re: I have a concatenate issue. Can anyone help?

Hi Eric

I got a run error when I tried to enter text in a Location cell AA4.

One thing I noticed in your original code:

[COLOR=#333333][FONT=arial]Target.Font.Color = [/FONT][/COLOR][COLOR=#ff0000][FONT=arial]Automatic[/FONT][/COLOR]

I really expected that to be xlAutomatic, but didn't think too much of it at the time. Since Automatic is not defined anywhere, it will default to 0, which renders my check for the formatting incorrect. Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str1 As String, sl(100, 2) As Long, ctr As Long, ws As Worksheet, i as Long

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Row < 3 Or Target.Row > 46 Then Exit Sub
    If Target.Column < 27 Or Target.Column > 29 Then Exit Sub
    
    Target.Font.Color = xlAutomatic
    Target.Interior.Color = xlNone
    
    ctr = 0
    For Each ws In Worksheets
        If ws.Name <> "Master" Then
            ctr = ctr + 1
            sl(ctr, 1) = Len(str1) + 1
            sl(ctr, 2) = Len(ws.Name) + 1
            str1 = str1 & ws.Name & ": "
            
            If ws.Range(Target.Address).Interior.Color = xlNone Then str1 = str1 & ws.Range(Target.Address).Value
            str1 = str1 & vbCrLf
        End If
    Next ws
    
    With Sheets("Master").Range(Target.Address)
        .Value = str1
        .Font.Bold = False
        For i = 1 To ctr
            .Characters(Start:=sl(i, 1), Length:=sl(i, 2)).Font.Bold = True
        Next i
    End With
    
End Sub

I made one other tweak to handle the bolding better.

 

[Eric W]

Re: I have a concatenate issue. Can anyone help?

I found one problem with my code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str1 As String, sl(100, 2) As Long, ctr As Long, ws As Worksheet, i As Long

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Row < 3 Or Target.Row > 46 Then Exit Sub
    If Target.Column < 27 Or Target.Column > 29 Then Exit Sub
    
    Target.Font.Color = xlAutomatic
    Target.Interior.Color = xlNone
    
    ctr = 0
    For Each ws In Worksheets
        If ws.Name <> "Master" Then
            ctr = ctr + 1
            sl(ctr, 1) = Len(str1) + 1
            sl(ctr, 2) = Len(ws.Name) + 1
            str1 = str1 & ws.Name & ": "
            
            If ws.Range(Target.Address).Interior.Color = RGB(255, 255, 255) Then str1 = str1 & ws.Range(Target.Address).Value
            str1 = str1 & vbCrLf
        End If
    Next ws
    
    With Sheets("Master").Range(Target.Address)
        .Value = str1
        .Font.Bold = False
        For i = 1 To ctr
            .Characters(Start:=sl(i, 1), Length:=sl(i, 2)).Font.Bold = True
        Next i
    End With
    
End Sub

Change the bolded line to fix it. But this would have just resulted in incorrect results, not a run error. I'm not sure what could cause a run error, there's very little in the code that can cause a problem. What version of Excel are you using? You are still just using the same sheet names, right? When you get the error, what line is highlighted in the code?