In a workbook, how do I not have initials split and wrapped?

[Guest]

I have a vacation schedule where the first tab pulls people's initials from
other tabs. It splits & wraps their initials. Changing the width of the
column is not the solution since there can be 15 sets of initals in any given
cell.
Any suggestions?

 

[Pete_UK]

If you want to stop the cell from wrapping, click on Format | Cells |
Alignment tab, and un-check the Wrap Text option. Is this what you
mean? I'm a bit confused ...

Pete

 

[Guest]

Sorry, guess I wasn't very clear. I need the cell to wrap since there can be
up to 15 people's initials in any cell. My problem is that on the tally it
breaks up the people's initials.
ie:
MSN,CMA,RIM,SP
G, DAK
For instance, on MSN's page cells now read =IF(M36>0,"MSN","") in order to
be pulled to the tally page. Is there some command to keep this from
happening?

Thanks
Leeney

 

[Pete_UK]

I see now.

You obviously have some formula to concatenate the initials with
commas. You should arrange for this formula to add a comma and a
<space> after each set of initials, and then the wrapping will take
place at the <space> without splitting the initials.

Hope this helps.

Pete

 

[Guest]

Hi Pete,
That is definately a good idea - do you mean
=IF(M36>0,"MSN ,","") or =IF(M36>0,"MSN, ","")
I already tried that. It still will seperate anything between the quote
marks. Might there be a command (like a $ in a formula will keep the number
or letter from incrementing) to keep whatever is between the quotes together?
Leeney

 

[Pete_UK]

No, I mean on your tally page what is the formula that produces:

MSN,CMA,RIM,SP
G, DAK

Is it something like:

=Sheet1!N36&","&Sheet2!N36&","& ...etc?

If so, then you should change the "," to ", ", i.e. put a space after
each comma within the quotes. Then you should get something like:

MSN, CMA, RIM,
SPG, DAK

because of the spaces.

Hope this helps.

Pete

 

[Guest]

Hi Pete -

The tally page reads: (shortened)
=CONCATENATE(CEB!AB13,GEC!AB13,AED!AB13,KVG!AB13)

I tried =CONCATENATE(CEB!AB13&", ",GEC!AB13&", ",AED!AB13&", ",KVG!13&", ",)
That didn't work.

What does the '&' do?

Leeney

 

[Peo Sjoblom]

The whole point of using the ampersand is that you don't need the
CONCATENATE function

=CEB!AB13&", "&GEC!AB13 and so on


--


Regards,


Peo Sjoblom

 

[Guest]

Thank you! I'll try that. Do you happen to know how to fix the other problem?

Leeney

 

[Pete_UK]

Leeney,

I'm a bit confused - where does the comma come from in the composite
cell on your tally sheet that produces:

MSN,CMA,RIM,SP
G, DAK

because your CONCATENATE function is not adding it between initials.

Is the contents of cell AB13 on the CEB sheet "MSN,"? (i.e. with a
comma already at the end?) If so, how does it get there? Is there a
formula in that cell (or some other cell) which joins the comma to the
initials? If so, then make it a <comma><space> which is joined on.

We'll get there eventually ...

Pete

 

[Guest]

Hi Pete -

Yes, I'm sure we'll get there . . . eventually. ha

The comma after the initials is on the individuals sheet -
=IF(K20>0,"BPR,","")
I did try putting a space before the comma and also tried putting one after
the comma but before the quotes - it still splits things where ever it wants.

Thnx for all your help
Leeney

 

[Pete_UK]

Are you sure you put the space in the correct place? It should be:

=IF(K20>0,"BPR, ","")

You will also need to make this change in every worksheet that has
this kind of formula.

That should cure the splitting of initials.

Hope this helps (finally).

Pete