Transparent Cell Borders For Formula Cells?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Naji - 18 Jul 2007 22:04 GMT

Hey guys, I've searched high and low for a solution to this, and as a
last resort, I've come to the forums for some help.

For range C1:ZZ3 , I have the following formulas,

C1 : =IF(DAY(C$3)=1,CHOOSE(MONTH(C
$3),"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"),"")

C2: =CHOOSE(WEEKDAY(C$3),"S","M","T","W","T","F","S")

C3: = DATE + 1 (Incrementing the initial date by 1 as you progress
through the columns)

Now, please bare in mind I have simplified the issue to help me
explain this, but here it is:

I am actually only talking about the first row here,

If I paste the formula all the way across, most cells will be "" blank
cells because the month only shows up at the first of every month. So
lets say it's the first of the month and cell C1 would be "JULY", only
the "J" is visible because the formula in C2 cuts it off. How can I
make it so that blank formula results will have transparent cell
formats so that the entire text of JULY is visible without it being
cut off by the following cell? How can I correct this problem? If
anyone can help with this, I will be very grateful! Thank you!

-Naji

Reply to this Message

Bob I - 18 Jul 2007 22:20 GMT

I must be missing something here, How can "C2" cut off the something in
"C1"? C2 is below C1. What you put in D1 would cut off C1.

> Hey guys, I've searched high and low for a solution to this, and as a
> last resort, I've come to the forums for some help.
[quoted text clipped - 24 lines]
>
> -Naji

Reply to this Message

Sandy Mann - 18 Jul 2007 22:33 GMT

>I must be missing something here, How can "C2" cut off the something in
>"C1"? C2 is below C1. What you put in D1 would cut off C1.

If C3=1 then the formula in C1 will return "July" (say) However, D1 & D3
have the same formulas dragged acoss, D3 cannot be 1 because C3 was,
therefore the formaula will return "" whcih will cut off the "July" if the
column is only wide enough the show the "J"

As far a I know there is not way around this - unless you know better.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>I must be missing something here, How can "C2" cut off the something in
>"C1"? C2 is below C1. What you put in D1 would cut off C1.
[quoted text clipped - 27 lines]
>>
>> -Naji

Reply to this Message

Rick Rothstein (MVP - VB) - 18 Jul 2007 22:42 GMT

I realize you asked a different question, but I thought you might be
interested in these shorter formulas....

> C1 : =IF(DAY(C$3)=1,CHOOSE(MONTH(C
> $3),"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"),"")

The above formula can be replaced with this...

C1: =UPPER(TEXT(C$3,"mmm"))

> C2: =CHOOSE(WEEKDAY(C$3),"S","M","T","W","T","F","S")

The above formula can be replaced with this...

C2: =LEFT(TEXT(C$3,"ddd"),1)

Rick

Reply to this Message

Sandy Mann - 18 Jul 2007 23:14 GMT

UsingRicks formula the you can spell out the Month name one letter at a time
by using:

=IF(DAY(C3)<10,MID(UPPER(TEXT(C3,"mmmm")),DAY(C3),1),"")

in C1 and dragging across

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>I realize you asked a different question, but I thought you might be
>interested in these shorter formulas....
[quoted text clipped - 13 lines]
>
> Rick

Reply to this Message

Naji - 19 Jul 2007 15:59 GMT

Oh I am sorry, I meant C1 is cut off by D1's "" return.

> UsingRicks formula the you can spell out the Month name one letter at a time
> by using:
[quoted text clipped - 30 lines]
> >
> > Rick

Reply to this Message

Sandy Mann - 19 Jul 2007 16:25 GMT

Yes I surmised that. The formula I posted will enter J In C1, U in D1, L in
E1, Y in F1 and "" in every other cell until it encounters another 1 in row
3

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Oh I am sorry, I meant C1 is cut off by D1's "" return.
>
[quoted text clipped - 33 lines]
>> >
>> > Rick

Reply to this Message