Forum Discussion

Brass Contributor

Oct 25, 2022

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

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 "v...

excel

Martin_Weiss

Bronze Contributor

Oct 25, 2022

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…."

AlokBeheria

Brass Contributor

Oct 25, 2022

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

AlokBeheria
Brass Contributor
Oct 26, 2022
Any inputs from your end mate. I would appreciate , your help here.

Regards
- Martin_Weiss
  Bronze Contributor
  Oct 26, 2022
  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
  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:
  
  I admit this is far from perfect, but it's the best solution I can offer.
  - AlokBeheria
    Brass Contributor
    Oct 27, 2022
    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