Concatenate fails when cell reference is changed

[DaveRook]

Hi
My excel sheet has a table and underneath, I concatenate a 'string' to summary the table. When I replace the value of a cell by dragging in a new cell, it errors.

EG, cell A1 has the value of 5 and B1 has the value of 10.

cell A3 has the string:
=CONCATENATE("Total is ", IF($A$1 + $B$1 > 10, "higher", "lower"), " than 11")

This works as it should do. Brilliant.

In cell C1, I have the value 4. If I drag C1 into (and replace) the contents of B1, my string errors:
=CONCATENATE("Total is ", IF($A$1 +#REF! > 10, "higher", "lower"), " than 11")

I have changed this to use absolute cells ($) and not, and even from different worksheets. I'm not sure why the cell reference has to change in the string.

Any ideas?

Dave

 

[anandvarma]

try this

Excel Workbook
	A	B	C
1	5	10	4
2
3	Total is higher than 11
Sheet6

 

[Tom Urtis]

="Total is "&IF(A1+INDIRECT("B1")>10, "higher", "lower")&" than 11"

 

[DaveRook]

Thank you both

This is the answer and works perfectly

Dave

 

[Peter_SSs]

Incidentally, you could also use the OFFSET function to do this job.

="Total is "&IF(A1+OFFSET(A1,0,1)>10, "higher", "lower")&" than 11"