One interp and play ..
(Link to a sample file provided below)
Assume source data is in Sheet1, cols A to E, from row1 down
In Sheet2
----
Cell A1 is reserved for input of the code, e.g.: 33T, 32M, 22N, 11H, etc
Input a code in A1: 33T
Put in B2:
=IF(A1="","",IF(LEFT(A1,1)+0=3,INDEX(Sheet1!B:B,MA TCH(A1,Sheet1!$E:$E,0)),IF
(LEFT(A1,1)+0=2,INDEX(Sheet1!B:B,MATCH(A1,Sheet1!$ D:$D,0)),IF(LEFT(A1,1)+0=1
,INDEX(Sheet1!B:B,MATCH(A1,Sheet1!$C:$C,0)),""))))
Format B2 as date
With B2 selected, click inside the namebox* and type: StartDate
*box with the droparrow just to the left of the equal sign / formula bar
This names cell B2 as StartDate, which will be used in the CF formula we're
setting up in Sheet1
Put in B3: =IF(B2="","",B2+1)
Copy B3 down as far as required, say to B368 ?
Put in A2: =TEXT(B2,"dddd")
Copy A2 down to A368
Sheet2 will return the desired results in A2:B368
depending on the code input in A1
In Sheet1
----
To set up the conditional formatting (CF)
Select cols A to E
Click Format Conditional Formatting
Under Condition 1:
Formula is: =AND($B1=StartDate,$B1<"")
Click Format button Patterns tab Light green? OK
Click OK at the main dialog
This will colour all the rows in Sheet1 with dates in col B = StartDate
Here'a link to a sample file with the implementation:
http://www.savefile.com/files/7021116
File: Calendar_Dates_Help_Nat_misc.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Nat" wrote in message
...
Hi All,
I have an excel spreadsheet that lists every date in the year, with a
particular code in the next cell.
IE:
Monday 3/01/2005 11M 22M 32M
Tuesday 4/01/2005 11T 22T 32T
Wednes 5/01/2005 11W 22W 32W
Thursday 6/01/2005 11H 22H 32H
Friday 7/01/2005 11F 22F 32F
Saturday 8/01/2005 11S 22S 32S
Sunday 9/01/2005 11N 22N 32N
Monday 10/01/2005 11M 21M 33M
Tuesday 11/01/2005 11T 21T 33T
Wednes 12/01/2005 11W 21W 33W
Thursday 13/01/2005 11H 21H 33H
Friday 14/01/2005 11F 21F 33F
What I need is to be able to search by the code eg "33T" and have all the
dates listed for the rest of the year in a seperate sheet.
Also, (now I know I'm asking too much :0) but would it be possible to have
a
pre-designed calendar set up, and the dates get automatically
highlighted/shaded with the dates listed from the search???
THANKS!!!!!!!!!!!!!!!!!
Nat