I'm trying to create a calendar speadsheet from a list of information. Let's
say my data is in the form of three COL. The first is a vehicle ID, second
is a due date, and lastly number three is what work is due for that vehicle
on that date. I'm trying to bring it over into a spreedsheet with the
vehicles listed in down in col 1 then the months spread accorross the top
from Jan in col 2 through Dec in col 13. Any help you can give me is greatly
appreciated.

Hi!

This can be done if you set it up properly. It would also
require a lot of formulas (depending on how many vehicles
you have)- 12 * number of vehicles.

To make things as easy as possible the dates in your
columnar data and the 12 months in the calander must be
true Excel dates.

For the 12 calander months you can enter any date of a
particular month and just format the cells as MMM. For
example, you can enter 1/1 for January and then format as
MMM to give you the displayed value of Jan.

For the purposes of this example I will use this sample
data as your list and it's located in the range O1:Q10

1 1-Jan brake
2 5-Nov tune
3 6-Oct insp
4 1-Feb insp
5 1-Mar tune
6 1-Apr brake
7 1-May align
8 1-Aug muff
9 1-Sep tires
10 1-Jun oil

Now, assume that in your "calander" the vehicle ID's start
in A2. The months are listed in B1:M1.

In B2 enter this array formula using the key combo of
CTRL,SHIFT,ENTER:

=INDEX($Q$1:$Q$10,MATCH(1,($O$1:$O$10=$A2)*(MONTH
($P$1:$P$10)=MONTH(B$1)),0))

Copy across then down to fill the calander. You will get a
lot of #N/A errors where there is no matching data. You
can either suppress the #N/A's from being displayed by use
of the formula or you can hide the #N/A's using
conditional formatting. Personally, I would use cf.

Biff

-----Original Message-----
I'm trying to create a calendar speadsheet from a list of
information. Let's
say my data is in the form of three COL. The first is a
vehicle ID, second
is a due date, and lastly number three is what work is
due for that vehicle
on that date. I'm trying to bring it over into a
spreedsheet with the
vehicles listed in down in col 1 then the months spread
accorross the top
from Jan in col 2 through Dec in col 13. Any help you
can give me is greatly
appreciated.
.

I tried it and it works very much like the several VLOOKUPs i've done. The
real problem i have is that at times there are several items due each month
and this only brings up the first item due. I'm dealing with with 14
vehicles each with 150-160 inspections due at various times. I have a data
miner that strips a txt file and imports a 2200 row by 41 col data set. I'm
currently using 6 rows per vehicle per month and manually typing the data.
What i need is excel to save me some time and do it for me. I've always had
the problem of finding the next item due in a list of items due on thew same
vehicle in the same month. Another thing is that i have to use a revolving
calendar starting at the current month and going out for the year.

HELP,
O'C

"Biff" wrote:

Hi!

This can be done if you set it up properly. It would also
require a lot of formulas (depending on how many vehicles
you have)- 12 * number of vehicles.

To make things as easy as possible the dates in your
columnar data and the 12 months in the calander must be
true Excel dates.

For the 12 calander months you can enter any date of a
particular month and just format the cells as MMM. For
example, you can enter 1/1 for January and then format as
MMM to give you the displayed value of Jan.

For the purposes of this example I will use this sample
data as your list and it's located in the range O1:Q10

1 1-Jan brake
2 5-Nov tune
3 6-Oct insp
4 1-Feb insp
5 1-Mar tune
6 1-Apr brake
7 1-May align
8 1-Aug muff
9 1-Sep tires
10 1-Jun oil

Now, assume that in your "calander" the vehicle ID's start
in A2. The months are listed in B1:M1.

In B2 enter this array formula using the key combo of
CTRL,SHIFT,ENTER:

=INDEX($Q$1:$Q$10,MATCH(1,($O$1:$O$10=$A2)*(MONTH
($P$1:$P$10)=MONTH(B$1)),0))

Copy across then down to fill the calander. You will get a
lot of #N/A errors where there is no matching data. You
can either suppress the #N/A's from being displayed by use
of the formula or you can hide the #N/A's using
conditional formatting. Personally, I would use cf.

Biff

-----Original Message-----
I'm trying to create a calendar speadsheet from a list of
information. Let's
say my data is in the form of three COL. The first is a
vehicle ID, second
is a due date, and lastly number three is what work is
due for that vehicle
on that date. I'm trying to bring it over into a
spreedsheet with the
vehicles listed in down in col 1 then the months spread
accorross the top
from Jan in col 2 through Dec in col 13. Any help you
can give me is greatly
appreciated.
.

Hi!

Ok, so you allot 6 rows for 6 possible items per vehicle
per month, no problem.

array entered:

=INDEX($Q$1:$Q$10,SMALL(IF(($O$1:$O$10=$A$2)*(MONT H
($P$1:$P$10)=MONTH(B$1))=1,ROW($A$1:$A$10)),ROW(1: 1)))

Now, this will take some tweaking because you'll have to
manually change the reference cell to each vehicle instead
of just being able to create one formula and drag copying
it. You can copy this formula across then down for the
first vehicle only. Then you would need to change the
reference cell to vehicle 2.

ROW($A$1:$A$10) refers to the length of your raw data
list. If your actual list is 2200 rows long just change
the range reference to A1:A2200. If you don't know the
exact length and don't feel like counting it you can use
something like this:

ROW(INDIRECT("1:"&COUNTA(Q:Q)))

As for the revolving calender thing, ???????

You may be better off getting some preventative
maintenance scheduling software.

Biff

-----Original Message-----
I tried it and it works very much like the several
VLOOKUPs i've done. The
real problem i have is that at times there are several
items due each month
and this only brings up the first item due. I'm dealing
with with 14
vehicles each with 150-160 inspections due at various
times. I have a data
miner that strips a txt file and imports a 2200 row by 41
col data set. I'm
currently using 6 rows per vehicle per month and manually
typing the data.
What i need is excel to save me some time and do it for
me. I've always had
the problem of finding the next item due in a list of
items due on thew same
vehicle in the same month. Another thing is that i have
to use a revolving
calendar starting at the current month and going out for
the year.

HELP,
O'C

"Biff" wrote:

Hi!

This can be done if you set it up properly. It would
also
require a lot of formulas (depending on how many
vehicles
you have)- 12 * number of vehicles.

To make things as easy as possible the dates in your
columnar data and the 12 months in the calander must be
true Excel dates.

For the 12 calander months you can enter any date of a
particular month and just format the cells as MMM. For
example, you can enter 1/1 for January and then format
as
MMM to give you the displayed value of Jan.

For the purposes of this example I will use this sample
data as your list and it's located in the range O1:Q10

1 1-Jan brake
2 5-Nov tune
3 6-Oct insp
4 1-Feb insp
5 1-Mar tune
6 1-Apr brake
7 1-May align
8 1-Aug muff
9 1-Sep tires
10 1-Jun oil

Now, assume that in your "calander" the vehicle ID's
start
in A2. The months are listed in B1:M1.

In B2 enter this array formula using the key combo of
CTRL,SHIFT,ENTER:

=INDEX($Q$1:$Q$10,MATCH(1,($O$1:$O$10=$A2)*(MONTH
($P$1:$P$10)=MONTH(B$1)),0))

Copy across then down to fill the calander. You will
get a
lot of #N/A errors where there is no matching data. You
can either suppress the #N/A's from being displayed by
use
of the formula or you can hide the #N/A's using
conditional formatting. Personally, I would use cf.

Biff

-----Original Message-----
I'm trying to create a calendar speadsheet from a list
of
information. Let's
say my data is in the form of three COL. The first is
a
vehicle ID, second
is a due date, and lastly number three is what work is
due for that vehicle
on that date. I'm trying to bring it over into a
spreedsheet with the
vehicles listed in down in col 1 then the months
spread
accorross the top
from Jan in col 2 through Dec in col 13. Any help you
can give me is greatly
appreciated.
.


.

I came up with this one today and it works like a champ. Now the only
problem i have is with the #num! error showing up. I think I'm against the
wall in the nested functions area because i keep getting an error in my
formula when i try inserting the old IF(ISERROR(INDEX(...),"",INDEX(...). I
can't figure out how to hide with the CONDIT Formatting. Got any ideas???

=INDEX(TRUCKS!$C$1:$C$21,SMALL(IF(TRUCKS!$A1:$A22= SHEET!$A$12,IF(TRUCKS!$B$1:$B$22<=DATE(2005,MONTH( F1),31),IF(TRUCKS!$B$1:$B22=DATE(2005,MONTH(F1),1 ),ROW($A$1:$A$21),""),""),""),ROW($A$1:$A$21)))

Thanks,
O'C

"Biff" wrote:

Hi!

Ok, so you allot 6 rows for 6 possible items per vehicle
per month, no problem.

array entered:

=INDEX($Q$1:$Q$10,SMALL(IF(($O$1:$O$10=$A$2)*(MONT H
($P$1:$P$10)=MONTH(B$1))=1,ROW($A$1:$A$10)),ROW(1: 1)))

Now, this will take some tweaking because you'll have to
manually change the reference cell to each vehicle instead
of just being able to create one formula and drag copying
it. You can copy this formula across then down for the
first vehicle only. Then you would need to change the
reference cell to vehicle 2.

ROW($A$1:$A$10) refers to the length of your raw data
list. If your actual list is 2200 rows long just change
the range reference to A1:A2200. If you don't know the
exact length and don't feel like counting it you can use
something like this:

ROW(INDIRECT("1:"&COUNTA(Q:Q)))

As for the revolving calender thing, ???????

You may be better off getting some preventative
maintenance scheduling software.

Biff

-----Original Message-----
I tried it and it works very much like the several
VLOOKUPs i've done. The
real problem i have is that at times there are several
items due each month
and this only brings up the first item due. I'm dealing
with with 14
vehicles each with 150-160 inspections due at various
times. I have a data
miner that strips a txt file and imports a 2200 row by 41
col data set. I'm
currently using 6 rows per vehicle per month and manually
typing the data.
What i need is excel to save me some time and do it for
me. I've always had
the problem of finding the next item due in a list of
items due on thew same
vehicle in the same month. Another thing is that i have
to use a revolving
calendar starting at the current month and going out for
the year.

HELP,
O'C

"Biff" wrote:

Hi!

This can be done if you set it up properly. It would
also
require a lot of formulas (depending on how many
vehicles
you have)- 12 * number of vehicles.

To make things as easy as possible the dates in your
columnar data and the 12 months in the calander must be
true Excel dates.

For the 12 calander months you can enter any date of a
particular month and just format the cells as MMM. For
example, you can enter 1/1 for January and then format
as
MMM to give you the displayed value of Jan.

For the purposes of this example I will use this sample
data as your list and it's located in the range O1:Q10

1 1-Jan brake
2 5-Nov tune
3 6-Oct insp
4 1-Feb insp
5 1-Mar tune
6 1-Apr brake
7 1-May align
8 1-Aug muff
9 1-Sep tires
10 1-Jun oil

Now, assume that in your "calander" the vehicle ID's
start
in A2. The months are listed in B1:M1.

In B2 enter this array formula using the key combo of
CTRL,SHIFT,ENTER:

=INDEX($Q$1:$Q$10,MATCH(1,($O$1:$O$10=$A2)*(MONTH
($P$1:$P$10)=MONTH(B$1)),0))

Copy across then down to fill the calander. You will
get a
lot of #N/A errors where there is no matching data. You
can either suppress the #N/A's from being displayed by
use
of the formula or you can hide the #N/A's using
conditional formatting. Personally, I would use cf.

Biff

-----Original Message-----
I'm trying to create a calendar speadsheet from a list
of
information. Let's
say my data is in the form of three COL. The first is
a
vehicle ID, second
is a due date, and lastly number three is what work is
due for that vehicle
on that date. I'm trying to bring it over into a
spreedsheet with the
vehicles listed in down in col 1 then the months
spread
accorross the top
from Jan in col 2 through Dec in col 13. Any help you
can give me is greatly
appreciated.
.


.

Ok I figured it out:

=IF(ISERROR(F2),TRUE,FALSE)

I'm just a little slow, i'll get it eventually. Thanks for all your help.
This was the first time i ever used the online help/chat area.

O'C


"O'C" wrote:

I came up with this one today and it works like a champ. Now the only
problem i have is with the #num! error showing up. I think I'm against the
wall in the nested functions area because i keep getting an error in my
formula when i try inserting the old IF(ISERROR(INDEX(...),"",INDEX(...). I
can't figure out how to hide with the CONDIT Formatting. Got any ideas???

=INDEX(TRUCKS!$C$1:$C$21,SMALL(IF(TRUCKS!$A1:$A22= SHEET!$A$12,IF(TRUCKS!$B$1:$B$22<=DATE(2005,MONTH( F1),31),IF(TRUCKS!$B$1:$B22=DATE(2005,MONTH(F1),1 ),ROW($A$1:$A$21),""),""),""),ROW($A$1:$A$21)))

Thanks,
O'C

"Biff" wrote:

Hi!

Ok, so you allot 6 rows for 6 possible items per vehicle
per month, no problem.

array entered:

=INDEX($Q$1:$Q$10,SMALL(IF(($O$1:$O$10=$A$2)*(MONT H
($P$1:$P$10)=MONTH(B$1))=1,ROW($A$1:$A$10)),ROW(1: 1)))

Now, this will take some tweaking because you'll have to
manually change the reference cell to each vehicle instead
of just being able to create one formula and drag copying
it. You can copy this formula across then down for the
first vehicle only. Then you would need to change the
reference cell to vehicle 2.

ROW($A$1:$A$10) refers to the length of your raw data
list. If your actual list is 2200 rows long just change
the range reference to A1:A2200. If you don't know the
exact length and don't feel like counting it you can use
something like this:

ROW(INDIRECT("1:"&COUNTA(Q:Q)))

As for the revolving calender thing, ???????

You may be better off getting some preventative
maintenance scheduling software.

Biff

-----Original Message-----
I tried it and it works very much like the several
VLOOKUPs i've done. The
real problem i have is that at times there are several
items due each month
and this only brings up the first item due. I'm dealing
with with 14
vehicles each with 150-160 inspections due at various
times. I have a data
miner that strips a txt file and imports a 2200 row by 41
col data set. I'm
currently using 6 rows per vehicle per month and manually
typing the data.
What i need is excel to save me some time and do it for
me. I've always had
the problem of finding the next item due in a list of
items due on thew same
vehicle in the same month. Another thing is that i have
to use a revolving
calendar starting at the current month and going out for
the year.

HELP,
O'C

"Biff" wrote:

Hi!

This can be done if you set it up properly. It would
also
require a lot of formulas (depending on how many
vehicles
you have)- 12 * number of vehicles.

To make things as easy as possible the dates in your
columnar data and the 12 months in the calander must be
true Excel dates.

For the 12 calander months you can enter any date of a
particular month and just format the cells as MMM. For
example, you can enter 1/1 for January and then format
as
MMM to give you the displayed value of Jan.

For the purposes of this example I will use this sample
data as your list and it's located in the range O1:Q10

1 1-Jan brake
2 5-Nov tune
3 6-Oct insp
4 1-Feb insp
5 1-Mar tune
6 1-Apr brake
7 1-May align
8 1-Aug muff
9 1-Sep tires
10 1-Jun oil

Now, assume that in your "calander" the vehicle ID's
start
in A2. The months are listed in B1:M1.

In B2 enter this array formula using the key combo of
CTRL,SHIFT,ENTER:

=INDEX($Q$1:$Q$10,MATCH(1,($O$1:$O$10=$A2)*(MONTH
($P$1:$P$10)=MONTH(B$1)),0))

Copy across then down to fill the calander. You will
get a
lot of #N/A errors where there is no matching data. You
can either suppress the #N/A's from being displayed by
use
of the formula or you can hide the #N/A's using
conditional formatting. Personally, I would use cf.

Biff

-----Original Message-----
I'm trying to create a calendar speadsheet from a list
of
information. Let's
say my data is in the form of three COL. The first is
a
vehicle ID, second
is a due date, and lastly number three is what work is
due for that vehicle
on that date. I'm trying to bring it over into a
spreedsheet with the
vehicles listed in down in col 1 then the months
spread
accorross the top
from Jan in col 2 through Dec in col 13. Any help you
can give me is greatly
appreciated.
.


.

Hi!

If that's the formula you want to use and it works, that's
great. However, it can be shortened by removing the 2nd
and 3rd nested IF's. They're really not needed and then
you could probably fit in the error trapping.

Extracting data and working on timesheets is my specialty!

Good luck!

Biff

-----Original Message-----
I came up with this one today and it works like a champ.
Now the only
problem i have is with the #num! error showing up. I
think I'm against the
wall in the nested functions area because i keep getting
an error in my
formula when i try inserting the old IF(ISERROR(INDEX
(...),"",INDEX(...). I
can't figure out how to hide with the CONDIT Formatting.
Got any ideas???

=INDEX(TRUCKS!$C$1:$C$21,SMALL(IF(TRUCKS!$A1:$A22 =SHEET!
$A$12,IF(TRUCKS!$B$1:$B$22<=DATE(2005,MONTH(F1),31 ),IF
(TRUCKS!$B$1:$B22=DATE(2005,MONTH(F1),1),ROW
($A$1:$A$21),""),""),""),ROW($A$1:$A$21)))

Thanks,
O'C

"Biff" wrote:

Hi!

Ok, so you allot 6 rows for 6 possible items per
vehicle
per month, no problem.

array entered:

=INDEX($Q$1:$Q$10,SMALL(IF(($O$1:$O$10=$A$2)*(MONT H
($P$1:$P$10)=MONTH(B$1))=1,ROW($A$1:$A$10)),ROW(1: 1)))

Now, this will take some tweaking because you'll have
to
manually change the reference cell to each vehicle
instead
of just being able to create one formula and drag
copying
it. You can copy this formula across then down for the
first vehicle only. Then you would need to change the
reference cell to vehicle 2.

ROW($A$1:$A$10) refers to the length of your raw data
list. If your actual list is 2200 rows long just change
the range reference to A1:A2200. If you don't know the
exact length and don't feel like counting it you can
use
something like this:

ROW(INDIRECT("1:"&COUNTA(Q:Q)))

As for the revolving calender thing, ???????

You may be better off getting some preventative
maintenance scheduling software.

Biff

-----Original Message-----
I tried it and it works very much like the several
VLOOKUPs i've done. The
real problem i have is that at times there are several
items due each month
and this only brings up the first item due. I'm
dealing
with with 14
vehicles each with 150-160 inspections due at various
times. I have a data
miner that strips a txt file and imports a 2200 row by
41
col data set. I'm
currently using 6 rows per vehicle per month and
manually
typing the data.
What i need is excel to save me some time and do it
for
me. I've always had
the problem of finding the next item due in a list of
items due on thew same
vehicle in the same month. Another thing is that i
have
to use a revolving
calendar starting at the current month and going out
for
the year.

HELP,
O'C

"Biff" wrote:

Hi!

This can be done if you set it up properly. It would
also
require a lot of formulas (depending on how many
vehicles
you have)- 12 * number of vehicles.

To make things as easy as possible the dates in your
columnar data and the 12 months in the calander must
be
true Excel dates.

For the 12 calander months you can enter any date of
a
particular month and just format the cells as MMM.
For
example, you can enter 1/1 for January and then
format
as
MMM to give you the displayed value of Jan.

For the purposes of this example I will use this
sample
data as your list and it's located in the range
O1:Q10

1 1-Jan brake
2 5-Nov tune
3 6-Oct insp
4 1-Feb insp
5 1-Mar tune
6 1-Apr brake
7 1-May align
8 1-Aug muff
9 1-Sep tires
10 1-Jun oil

Now, assume that in your "calander" the vehicle ID's
start
in A2. The months are listed in B1:M1.

In B2 enter this array formula using the key combo
of
CTRL,SHIFT,ENTER:

=INDEX($Q$1:$Q$10,MATCH(1,($O$1:$O$10=$A2)*(MONTH
($P$1:$P$10)=MONTH(B$1)),0))

Copy across then down to fill the calander. You will
get a
lot of #N/A errors where there is no matching data.
You
can either suppress the #N/A's from being displayed
by
use
of the formula or you can hide the #N/A's using
conditional formatting. Personally, I would use cf.

Biff

-----Original Message-----
I'm trying to create a calendar speadsheet from a
list
of
information. Let's
say my data is in the form of three COL. The first
is
a
vehicle ID, second
is a due date, and lastly number three is what work
is
due for that vehicle
on that date. I'm trying to bring it over into a
spreedsheet with the
vehicles listed in down in col 1 then the months
spread
accorross the top
from Jan in col 2 through Dec in col 13. Any help
you
can give me is greatly
appreciated.
.


.


.