Concatenate & Indirect

[malcmail]

I have a custom function called DC which requires a rate and year passed to it. So the text =DC("PAT",2017) entered into a cell gives the answer 100000.

I now have to extend this so that in certain situations PAT will become SPAT - and the easiest way to do it is to set a flag in a cell to S and then concatenate it. So I've used the following formula:

=+CONCATENATE("=dc(" & """" & J54 & "PAT" & """" & ",2017)")

and in text terms that gives me exactly the same result as if I had typed in the line as in the first line of this post. The problem is that when I use =INDIRECT(cell containing formula above) it spits out a #REF! error. What have I missed here - the text I typed and the text that is concatenated looks exactly the same so why does it not resolve properly?

TIA

[dflak]

I don't think you have to invoke INDIRECT here. INDIRECT will take whatever is between the parenthesis and interpret it as a range.

I think you simply need =DC(J54 & "PAT",2017).

[Pete_UK]

I think it would be better to do it this way in the cell containing the original formula:

=DC(IF(condition,"S","") & "PAT",2017)

where you will have to specify what condition is.

Hope this helps.

Pete

[etaf]

is DC a user defined function ?

whats in J54 ?

you want to setup
=DC("PAT",2017)
as an indirect
What part is in a CELL
not sure what
=+CONCATENATE("=dc(" & """" & J54 & "PAT" & """" & ",2017)")

=DC(" whatever is in J54 plus PAT" ,2017)

[malcmail]

Thanks for the help there guys - dflak was the first I saw and it worked a charm. Now to tackle my next problem no doubt!