Search code examples
excelconcatenationcellupdatesformulas

Row in formula change based on value in another cell

I'm trying to create a paragraph in excel. I have values listed in another sheet that I want to pull from.

Here is the code I'm using:

=CONCATENATE("Dear ",'Linkage Log'!I6,", "Blah Blah Blah")

I want the column I to change the same, but I'm trying to have it so that the 6 changes depending on a value I type in another cell (e.g. E8). So if I type 7 in the cell, the formula will change to:

=CONCATENATE("Dear ",'Linkage Log'!I7,", "Blah Blah Blah")

Is this possible?

I'm not trying to generate mail, so a mail merge isn't really helpful to me.

Thanks for your help!

Solution

  • You should be able to use the INDIRECT worksheet function to achieve that. So e.g. if cell A1 contains the value 7, your formula would be

    =CONCATENATE("Dear ",INDIRECT("'Linkage Log'!I"&A1), "Blah Blah Blah")