Disappearing Text...

[Snowmonster]

Hello all...

I have a problem again...
I have a cell with a formula in it, that puts data in it from another worksheet, which is a date, I.E. 1st, 2nd, etc... It is formatted as custom dd.

What I need to do is for the proceeding cells to be blank if they are are a higher number than the next one I.E. (27th 28th 29th 30th 1st 2nd 3rd) should read (blank blank blank blank 1st 2nd 3rd)...

An additional problem therefore lies at the other end, as the end week will show (27th 28th 29th 30th 1st 2nd 3rd) but need to show (27th 28th 29th 30th blank blank blank)...

Any help would be appreciated...

Snowmonster

 

[jasonb75]

what formula are you using?

it would be helpful if you tell us how / why those specific dates are extracted.

 

[Snowmonster]

Hi Jason

It's just a simple get formula, '=IF(Rota!$F$2="Year2011",Calendar!Q3,IF(Rota!$F$2="Year2012",Calendar!Q4,etc to 2050...'

Cheers

Snowmonster

 

[jasonb75]

Even simpler would be

=INDEX(Calendar!Q3:Q42,RIGHT(Rota!$F$2,2)-10)

I assume that the range Calendar!Q3:.. is literaly just a calendar of dates, what range do you have the formula entered into?

I'm trying to get a picture of what your output sheet looks like.

 

[Snowmonster]

Hi Jason...

It is in range Q5:OD5...

Thanks...

 

[Snowmonster]

The calendar is in range B3:NP42, and is called Calendar...

Cheers

Snowmonster

 

[jasonb75]

So you want a whole years worth of dates visible?

With 2012 in Rota!F2, which cells in Q5:OD5 should have dates visible, and what should be in the first and last of those cells?

 

[jasonb75]

A shot in the dark based on information so far, with a few assumptions to fill the blanks.

=IF(YEAR(INDEX(Calendar!Q3:Q42,RIGHT(Rota!$F$2,2)-10))-RIGHT(Rota!$F$2,4),"",INDEX(Calendar!Q3:Q42,RIGHT(Rota!$F$2,2)-10))