Append text to indirect reference

[gwhitney]

I have done a bit of searching but am having a hard time finding a way to accomplish my objective. I was hoping someone here can help. I have the following query that works perfectly:

=IF(INDIRECT(CONCATENATE("'Combined Pipeline View'!C",ROUNDDOWN(ROW()/2+1,0)))="","",INDIRECT(CONCATENATE("'Combined Pipeline View'!C",ROUNDDOWN(ROW()/2+1,0))))

It returns the results of a cell in a workbook called Combined Pipeline View. This formula will create two rows in the destination sheet for every one row in the source sheet so long as the source is not empty. What I want to be able to do is append some text to the source value for one of the destination rows.

Here is an example which I know is not correct but for illustrative purposes:

=IF(INDIRECT(CONCATENATE("'Combined Pipeline View'!C",ROUNDDOWN(ROW()/2+1,0)))="","",INDIRECT(CONCATENATE("TEST ","'Combined Pipeline View'!C",ROUNDDOWN(ROW()/2+1,0))))

If the source cell has a value of "ABCD" and the IF condition is appropriate then the new cell would have "TEST ABCD". I've tried various combinations of re-writing this function but can only get back a REF# or a TRUE/FALSE result of the IF statement due to the use of the INDIRECT statement. Thanks in advance for any time you might spend thinking about this.

 

[Marcelo Branco]

Welcome to Mr Excel

Try

=IF(INDIRECT(CONCATENATE("'Combined Pipeline View'!C",ROUNDDOWN(ROW()/2+1,0)))="","","TEST "&INDIRECT(CONCATENATE("'Combined Pipeline View'!C",ROUNDDOWN(ROW()/2+1,0))))

Hope this helps

M.

 

[gwhitney]

Yes, perfect. Simple and effective. I tried the ampersand but I think I was using it inside the INDIRECT statement. I got lost in my function. Thanks for your help with this.

 

[Marcelo Branco]

Yes, perfect. Simple and effective. I tried the ampersand but I think I was using it inside the INDIRECT statement. I got lost in my function. Thanks for your help with this.

You are welcome. Thanks for the feedback.

M.