IF or should I use another function?

dkmoody

New Member
Joined
Jan 10, 2018
Messages
2
(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>
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top