CODEWITHSUNDEEP
excelexcel-formula
Jeffrey Clark
Jeffrey Clark

Reputation: 13

In Excel, issue with Concatenating formula in Chart title with a string

I am having an issue naming my excel chart title using a combination of a cell reference and text. Whether I try the CONCATENATE function or just the ampersand operator, excel doesn't like it and I cannot figure out why.

Using ='Input Data'!$B$3:$F$3 & " DollarvsDepth", I receive the error "There's an error in the formula you entered"

Using =CONCATENATE('Input Data'!$B$3:$F$3, "DollarvsDepth") I get "That function isn't valid"

I would like to be able to name the chart from the text from a merged cell B3 through F3 on the 'Input Data' sheet, as well as some static text. Is this possible?

enter image description here

Upvotes: 0

Views: 2208

Answers (3)

Error 1004
Error 1004

Reputation: 8220

Try:

=CONCATENATE('Input Data'!B3, " ", 'Input Data'!C3," ",'Input Data'!D3," ",'Input Data'!E3," ",'Input Data'!F3," ","DollarvsDepth")

Input Sheet:

enter image description here

Output Sheet:

enter image description here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

I am posting this to follow up on the comment I left above. The issue here seems to be that Excel does not like having a chart title referencing a cell which contains a formula. So, one workaround might be to create a new cell which just references your current cell with a formula. This may not be the most ideal solution, but it is one to consider.

Upvotes: 2

user10825204
user10825204

Reputation:

When dealing with a merged cell, only the top-left cell reference is valid.

='Input Data'!$B$3 & " DollarvsDepth"

Upvotes: 1

Related Questions