» Automatically Wrapping Text in a Cell

This is my entire formula for on cell, while presing F2 or moving on cell or selecting this cell entire worksheet fill with formula i.e. showing formula in formula bar,

1) can i hide this perticular formula in formula bar.




=IF(LEN(H189)=5,IF(ISNA(ABS(VLOOKUP(TEXT(H189,0),data!B$2:E$364,1,FALSE)))=TRUE,0,ABS(VLOOKUP(TEXT(H189,0),data!B$2:E$364,4,TRUE))),IF(LEN(H189)=11,(IF(ISNA(ABS(VLOOKUP(LEFT(TEXT(H189,0),5),data!B$2:E$364,1,FALSE)))=TRUE,0,ABS(VLOOKUP(LEFT(TEXT(H189,0),5),data!B$2:E$364,4,TRUE)))+IF(ISNA(ABS(VLOOKUP(RIGHT(TEXT(H189,0),5),data!B$2:E$364,1,FALSE)))=TRUE,0,ABS(VLOOKUP(RIGHT(TEXT(H189,0),5),data!B$2:E$364,4,TRUE)))),IF(LEN(H189)=17,(IF(ISNA(ABS(VLOOKUP(LEFT(H189,5),data!B$2:E$364,1,FALSE)))=TRUE,0,ABS(VLOOKUP(LEFT(H189,5),data!B$2:E$364,4,TRUE)))+IF(ISNA(ABS(VLOOKUP(MID(H189,7,5),data!B$2:E$364,1,FALSE)))=TRUE,0,ABS(VLOOKUP(MID(H189,7,5),data!B$2:E$364,4,TRUE)))+IF(ISNA(ABS(VLOOKUP(RIGHT(H189,5),data!B$2:E$364,1,FALSE)))=TRUE,0,ABS(VLOOKUP(RIGHT(H189,5),data!B$2:E$364,4,TRUE)))),1)))

how do you go about Canceling automatic text wrap?

Rob,

In the Format Menu, select Format cells (ctrl+1), select the Alignement tab, then uncheck the Wrap text box

Hi Divyesh,

This is probably not the answer you are looking for, but I have a personal rule that if my formulae get long enough to bother me in that way, I split it up across multiple cells in a separate calculation area.

Even if you understand it now, I guarantee that you will kick yourself if you have to re-write it in a years time!

Much better to calculate each part of the whole separately, label them neatly in a calculation zone, and keep each formula short in length. The person that inevitably follows you will have some hope of following it too, and won't rubbish your name / reputation quite as badly.

Hope that helps in some way,

Alan.

I have created a form with combo boxes, i.e. drop down boxes in two columns. In another column i want the text to wrap automatically so that as you type the cells get bigger - i have clicked on the wrap option in alignement but because of the drop down boxes it isn't working. Any ideas how i can solve this? Cheers, Chris.