Cannot autofit for mirrored cell

[Erniego]

Hi guys,
I've tried many ways to deal with this problem and tried several examples from various sources but nothing seems to work for me.
I'll describe what I'm trying to do. I've cut my sheet down to the barest minimum to explain the problem - this isn't my actual sheet!
I have a workbook that has two sheets. On one sheet I have a cell that is essentially a text box. On the second sheet I have another cell the same size that is there only to echo what is in the first sheet.
Let's say my text box in sheet1 is cell C3. On the second sheet, in one cell I have a formula that states "=Sheet1!C3" (without the quotes).
This means that every time I type a sentence in sheet1 in cell C3, the text is also echoed on sheet2 in the cell with the formula. It works fine - everytime. Obviously I hide the formula and make that cell as locked so that the user cannot input anything into it. It's a "display only" cell.
So, now I want to add Wordwrap to sheet1, cell C3, so that I can type text much larger than the width of the column. I add it and the text wraps (on sheet1) and everything works just fine (on that sheet).
However, the echoed text on sheet2 only gives me a single line - not wrapped -even if I set the cell to wrap text.
When I set the cell manually to "wrap text", it immediately works but if I save the sheet and then go back in, any text I add doesn't wrap on sheet2 again - even though the cell is set to wrap text. I guess what i need is for Excel to "remember" the Autofit Height function on that cell so that the next time it gets data that is too large for the column width it will automatically wrap the text - but it won't.
So how to I set this cell to always Autofit Height - or automatically wrap? Excel doesn't seem to "save" the Autofit function on the cell and setting the cell to "Word wrap" doesn't work either. It's odd that turning OFF Word wrap and then turning it on again does achieve the function - but only when you do it "live". In my "real" sheet I have loads of these cells performing a similar function as it creates a custom sheet based on the information inputted into the first one. I need this to work reliably.
I don't really want to go to VBA or Macros unless it's absolutely necessary (I'm not very adept at them) so is there a way to use the standard interface to do what I need? Essentially I want the cell to retain the Autofit Height" function permanently so that on exit and re-open the cell autofits the text coming in instead of having to manually do it every time. Of course, to manually do it, I also have to unlock the cell(s)! In my real sheet, this isn't going to work well.
I feel like I'm missing something fundamental here. Is this a bug? I'm using Excel 2019.
Anyone?

 

[SkipVought]

Hi and welcome to Tek-Tips!

Excel is a wonderful spreadsheet tool that has so many great capabilities. There are often several ways to achieve some desired solution.

Often people assume a particular method to accomplish a desired result because they don't realize that there might be a better method to achieve that result.

So rather than telling us that the method is not working please tell us WHAT result you need and WHY you're typing something in sheet1!C3 and you want to see the exact same thing in sheet2!C3, formatting and all.

Is some other user going to COPY the sheet2 result or just look at it?

Is sheet2 just a DISPLAY for all to see? The Camera feature might work. faq68-7518

BTW, this is referred to as "requirements gathering" by analysts. It is information that users give so that an application can be properly designed and implemented.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Cole Mendoza]

It seems like you're having trouble with the word wrap function in Excel, specifically with the echoed text in sheet2 not wrapping even though the cell is set to wrap text. You've tried manually setting the cell to wrap text, but it doesn't seem to save the autofit height function permanently.

One solution to this problem could be to use a VBA macro to automatically set the cell to wrap text and autofit the height every time new data is entered into sheet1, which will then be echoed in sheet2. While you mentioned that you're not very adept at VBA or macros, this might be the most reliable way to achieve the desired outcome.

However, if you would rather avoid using VBA or macros, you could try adjusting the row height manually in sheet2 so that it accommodates the wrapped text. To do this, select the row(s) containing the echoed text in sheet2 and manually adjust the row height to fit the text. This way, even if the cell doesn't automatically autofit the height, you'll still be able to see the entire wrapped text without having to manually adjust the row height every time.

I hope this helps! Let us know if you have any further questions or if there's anything else we can assist you with.