select values from other sheet and paste into bunch of texts in between

Brass Contributor

Hello All,

 

I am new space and looking to automate one task where I need to replace values in between texts which is coming from different sheet. I managed to update values between sheets using "vlookup" function , but here , my requirement to pick values among texts and replace from different sheet.

In given below text , I want to select values from other sheet and paste into the sheet which is in between "double quotes

 

Spoiler
Hi L3 Support,
"We have received confirmation from zylo team to move to ZDS environment. This requirement is for "Test ISA permanent test environment" after migration. DDBA to update for Table compression details.

Regards

 

5 Replies

Hi @AlokBeheria,

 

if I understand you right, you want to mix static text with dynamic text. If that's the case, you have to enter the static text starting with = sign and double quotes. And then you can concatenate it with any type of formula, cell reference etc. by using the ampersand sign &

 

Here is one example that just pulls another text from cell A1 in Sheet2:

 

="Hi L3 Support, we have received confirmation… This requirement is for "&Sheet2!A1&" after migration. DDB to update…."

Martin_Weiss_0-1666706996593.png

 

Martin_Weiss_1-1666707016678.png

 

@Martin_Weiss Thanks for your help here , but it seems , I am still not out of the woods and in order to make your suggestion , I have to put "= + entire text in one line" , otherwise , it's not working for me. 

I need the text to be aligned in multiple lines.  Also, if I change the values from source text, it's not reflecting straightaway as it reflects in case of "vlookup" and I also want if someone change this text , it should immediately reflect on reference sheet.  Since , I am not allowed to paste pictures, I am not able to explain it effectively. Hope , you able to make out from my question. 



Regards












Any inputs from your end mate. I would appreciate , your help here.

Regards

Hi @AlokBeheria 

 

I do not understand why changes are not reflected immediately on your side. As it is just a cell reference to another sheet, any changes there should be visible right away in the formula cell.

 

Concerning text alignment and maintaining the static texts, I see your point. To a certain extent, you can deal with it by entering all static texts in separate cells. And then concatenate them in one formula cell.

 

=A1&CHAR(10)&A2&CHAR(10)&A3&" "&Sheet2!A1&" "&A4&CHAR(10)&A5

Martin_Weiss_0-1666792144072.png

The CHAR(10) inserts a line break. Please note that in order to get these line breaks working, you need to adjust the column with, so it's wide enough. And you have to activate the "Wrap text" functionality:

Martin_Weiss_1-1666792306362.png

 

I admit this is far from perfect, but it's the best solution I can offer.

 

Thanks a ton .. You really rocked !! . I was able to create multi line feed with your help . Also, would like to tell you that the problem for "change effect immediately" is perished by it's own.

Once again thanks a millions.

Regards