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

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

Wow, Max this is so great and I see you've put so much effort into this,
thank you so much!! I just have 2 little questions :0)

1) My source data in Sheet 1 is Column A-L, how do I amend the formula to
include these extra colums.

2) When I input the code ie. 33T it shows the first date that has that
code, but then it lists every single date after that, rather than only the
dates that show the code 33T?

Thank you so much!!

Nat

"Max" wrote:

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

Thanks for the clarifications! Seems my interp on your orig. post was quite
way-off <g. Discard the previous attempt, let's try it all over again.

The revised construct below should do it now
(Link to revised sample file below)

In Sheet1
----
Source data is in cols A to L, with the codes in cols C to L

Using an empty col to the right, col M

Put in M1:
=IF(ISNUMBER(MATCH(Sheet2!$A$1,C1:L1,0)),ROW(),"")

Copy M1 down to say M50,
to cover the max expected source data range

To set up the conditional formatting (CF):
----------------
Select cols A to L

Click Format Conditional Formatting

Under Condition 1:
Formula is: =$M1<""
Click Format button Patterns tab Light green? OK
Click OK at the main dialog

This will colour all the rows in Sheet1 which have been "auto-filtered" into
Sheet2

In Sheet2
---------
Cell A1 is reserved for input of the code, e.g.: 33T, 32M, 22N, 11H, etc.
Input a code in A1, say: 33T

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$M:$M,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$M:$M,ROWS($A$1:A1)),Sheet1!$M:$M,0)))

Copy A2 across to L2, fill down to L51
(cover the same range size as done in col M in Sheet1)

Format col B as dates

Sheet2 will auto-return the desired results from Sheet1
depending on the code input in A1

For a cleaner look in Sheet2,
suppress the display of extraneous zeros via clicking:
Tools Options View tab Uncheck Zero values OK

Link to revised sample file for the above construct:
http://www.savefile.com/files/6175722
File: Calendar_Dates_Help_Nat2_misc.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Nat" wrote in message
...
Wow, Max this is so great and I see you've put so much effort into this,
thank you so much!! I just have 2 little questions :0)

1) My source data in Sheet 1 is Column A-L, how do I amend the formula to

include these extra colums.

2) When I input the code ie. 33T it shows the first date that has that
code, but then it lists every single date after that, rather than only the
dates that show the code 33T?

Thank you so much!!

Nat

YOU'RE A LEGEND!!! THANK YOU SO MUCH!!!! PERFECT!!!

Nat

"Max" wrote:

Thanks for the clarifications! Seems my interp on your orig. post was quite
way-off <g. Discard the previous attempt, let's try it all over again.

The revised construct below should do it now
(Link to revised sample file below)

In Sheet1
----
Source data is in cols A to L, with the codes in cols C to L

Using an empty col to the right, col M

Put in M1:
=IF(ISNUMBER(MATCH(Sheet2!$A$1,C1:L1,0)),ROW(),"")

Copy M1 down to say M50,
to cover the max expected source data range

To set up the conditional formatting (CF):
----------------
Select cols A to L

Click Format Conditional Formatting

Under Condition 1:
Formula is: =$M1<""
Click Format button Patterns tab Light green? OK
Click OK at the main dialog

This will colour all the rows in Sheet1 which have been "auto-filtered" into
Sheet2

In Sheet2
---------
Cell A1 is reserved for input of the code, e.g.: 33T, 32M, 22N, 11H, etc.
Input a code in A1, say: 33T

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$M:$M,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$M:$M,ROWS($A$1:A1)),Sheet1!$M:$M,0)))

Copy A2 across to L2, fill down to L51
(cover the same range size as done in col M in Sheet1)

Format col B as dates

Sheet2 will auto-return the desired results from Sheet1
depending on the code input in A1

For a cleaner look in Sheet2,
suppress the display of extraneous zeros via clicking:
Tools Options View tab Uncheck Zero values OK

Link to revised sample file for the above construct:
http://www.savefile.com/files/6175722
File: Calendar_Dates_Help_Nat2_misc.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Nat" wrote in message
...
Wow, Max this is so great and I see you've put so much effort into this,
thank you so much!! I just have 2 little questions :0)

1) My source data in Sheet 1 is Column A-L, how do I amend the formula to

include these extra colums.

2) When I input the code ie. 33T it shows the first date that has that
code, but then it lists every single date after that, rather than only the
dates that show the code 33T?

Thank you so much!!

Nat

Glad to hear it worked !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Nat" wrote in message
...
YOU'RE A LEGEND!!! THANK YOU SO MUCH!!!! PERFECT!!!

Nat