IF or should I use another function?

[dkmoody]

(Windows 7 ENT, Excel 2016)

I have data in a helper table at AB57:AE60. (4x4) for charting in a workbook.

The columns are:

"Which Data", 2015, 2016, 2017

The rows are:

Data 01, Data 02, Data 03


Referencing a drop-down list located at AB2, If AB58:AB60=AB2, express the appropriate row of information, with the year as a title with the data above the graphical representation.

When I use a single year and make the chart not selectable from the drop-down, I am successfully using:

="<data type=""> Totals: 2015 - "&$AC$58&" | 2016 - "&$AD$58&" | 2017 - "&$AE$58&""

I am hoping to find an easy way to get the following result:

<data type=""> Totals: 2015 - <###> | 2016 - <###> | 2017 - <###>

When I try to create a conditional so that there is a comparison between AB2 (selected by the drop-down) and the series AB58:AB60, I am running into the issue where Excel is asking if I'm not trying to type a formula.

I have used variations of these two:

=$AB$58:$AB$60,$AB$2,"&$AB$58:$AB$60&Totals:""&$AC57 -""&$AC$58:$AC60&""&$AD$57 -""&$AD$58:$AD$60&""&$AE$57 -""&$AE$58:$AE$60&"

and

=IF($AB$58:$AB$60,$AB$2,"&$AB$58:$AB$60&Totals:""&$AC57 -""&$AC$58:$AC60&""&$AD$57 -""&$AD$58:$AD$60&""&$AE$57 -""&$AE$58:$AE$60&")

But I do not look forward to creating titles for multiple charts in this workbook if nested IF or other verbose methods are called for.

The data is substantial but the data type and yearly totals are used as a title across the chart of numerous data points. I have to have titles and my manager would like the titles to be as flexible as the drop-down in AB2 for displaying the three difference sets of data.

Suggestions?</data></data>

 

[Perpa]

dkmoody,
Welcome to the Forum.
It appears you are trying to adjust the string in each location to reflect the chosen Data ROW in the AB2 dropdown. To do this you need to determine the cell Row and Column for each cell that has a string where the Data Set will be displayed.

Using the 'Worksheet_Change' code below finds all the cells in the worksheet (Sheet1) that have the partial string " Totals: *" and replaces the string using the 'Data01,Data02, or Data03' from the dropdown in cell AB2. You will need to adjust the range in the line of code ' Set searchRng = Range("A1:D" & lr)' to fit your all your data. The code assumes you are working with only one worksheet, 'Sheet1'. You'll need to change that to suit if you are using some other sheet.

To install the macro - right click the Sheet tab (bottom of worksheet) and select 'View Code' to open the VB Editor. Then in the window that opens, select 'Worksheet' from the down arrow on the line that says 'General'. Then paste the macro right over the 2 lines of default code that appear so that just the code below is in the window. Then close the VB Editor and save the workbook as macro enabled.
Try this on a copy of your workbook so you don't lose anything.
I hope this is helpful.
Perpa

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Long
Dim searchRng As Range
Dim ws As Worksheet
   If Target.Address = "$AB$2" Then
    
    'This sets the row Data Selection in cell AB2
       If Cells(2, "AB") = "Data01" Then rw = 58
       If Cells(2, "AB") = "Data02" Then rw = 59
       If Cells(2, "AB") = "Data03" Then rw = 60
    
       lr = Range("D" & Rows.Count).End(xlUp).Row   'Change to column with last used row
    
       Set searchRng = Range("A1:D" & lr)     'CHANGE TO FIT YOUR NEEDS
       Set ws = Worksheets("Sheet1")             'CHANGE WORKSHEET NAME IF REQUIRED
    
       searchRng.Replace What:=" Totals: *", Replacement:=" Totals: 2015 - " & Cells(rw, "AC") & " | 2016 - " & Cells(rw, "AD") & " | 2017 - " & Cells(rw, "AE"), _
    LookAt:=xlPart
    
       Range("A2").Activate
   End If
End Sub

 

[dkmoody]

Thank you. I will give this a try and report back. I appreciate the help!