Hi All,

I am trying to build a maintenance cntract tracking system. This workbook
contains all my infrastructure assets, maintenance start/end dates, contact
details, contract numbers, costs and other miscellaneous data. One of the
results I need to produce is a list of all maintenance contracts that will
expire by 30th of the next calendar month. Eg. If we are in August, I want to
return anything that will expire before 30th September in the current year.

I have the asset name in column A of sheet1 and sheet2. I need to extract
only the rows based on the date in column U in sheet2 and merge colums A and
U from sheet 1 with columns O, S, U and V from sheet2 and place the results
in Sheet3.

Sheet3 has headings and other content, so the placement needs to start at
row 10. As the result list length could be variable, and my total length of
the table in sheets 1 & 2 is 280 rows I woulkd want to ensure that rows after
the end of the extract in sheet3 are blanked out.

Is this possible in Excel 2003 natively? or will it require scripting? If
so, could you please suggest appropriate code.

Many thanks,
Phillip Morgan

One approach using non-array formulas which should deliver it for you ..

Illustrated in this "plug-n-play" sample construct:
http://www.savefile.com/files/994757
Maintenance Contract Tracking Sys.xls

Assume source data as posted in row2 down
in sheets: X (your sheet1) and Y (your sheet2)
[I like to use v.short sheetnames, makes formulas v.shorter]

In a sheet: Z (this is your sheet3),

In A10:
=IF(Y!U2="","",IF(Y!U2<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0),ROWS($1:1),""))

In B10
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!A:A,SMALL($A$10:$A$300,ROWS($1:1))+1))

In C10
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!O:O,SMALL($A$10:$A$300,ROWS($1:1))+1))

In D10
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!S:S,SMALL($A$10:$A$300,ROWS($1:1))+1))

In E10
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!U:U,SMALL($A$10:$A$300,ROWS($1:1))+1))

In F10
=IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!V:V,SMALL($A$10:$A$300,ROWS($1:1))+1))

In G10:
=IF(B10="","",INDEX(X!$U:$U,MATCH(B10,X!$A:$A,0)))

Select A10:G10, copy down to G300. Cols B to G will auto-return the required
results from sheets X and Y, with all lines neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Phill_Morgan" wrote:
> Hi All,
>
> I am trying to build a maintenance cntract tracking system. This workbook
> contains all my infrastructure assets, maintenance start/end dates, contact
> details, contract numbers, costs and other miscellaneous data. One of the
> results I need to produce is a list of all maintenance contracts that will
> expire by 30th of the next calendar month. Eg. If we are in August, I want to
> return anything that will expire before 30th September in the current year.
>
> I have the asset name in column A of sheet1 and sheet2. I need to extract
> only the rows based on the date in column U in sheet2 and merge colums A and
> U from sheet 1 with columns O, S, U and V from sheet2 and place the results
> in Sheet3.
>
> Sheet3 has headings and other content, so the placement needs to start at
> row 10. As the result list length could be variable, and my total length of
> the table in sheets 1 & 2 is 280 rows I woulkd want to ensure that rows after
> the end of the extract in sheet3 are blanked out.
>
> Is this possible in Excel 2003 natively? or will it require scripting? If
> so, could you please suggest appropriate code.
>
> Many thanks,
> Phillip Morgan
>
>
>

Hi Max,

Thanks for your response. Unfortunately it did not yeild the expected results.

I got no asset names in sheet 3, just blank cells and numbers.
Dates were retured as 01/01/1900 for some assets. Column B returned 0 for
each row. All $ values were 0. The last column, which should have been a text
field (contract number), just returned #N/A. The rows were not bunched.

Also, I should have mentioned, ALL sheets have the headings on row 9. Data
always starts at row 10 in every sheet (as such I changed U2 in formula in
A10 to U10).

Phill


"Max" wrote:

> One approach using non-array formulas which should deliver it for you ..
>
> Illustrated in this "plug-n-play" sample construct:
> http://www.savefile.com/files/994757
> Maintenance Contract Tracking Sys.xls
>
> Assume source data as posted in row2 down
> in sheets: X (your sheet1) and Y (your sheet2)
> [I like to use v.short sheetnames, makes formulas v.shorter]
>
> In a sheet: Z (this is your sheet3),
>
> In A10:
> =IF(Y!U2="","",IF(Y!U2<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0),ROWS($1:1),""))
>
> In B10:
> =IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!A:A,SMALL($A$10:$A$300,ROWS($1:1))+1))
>
> In C10:
> =IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!O:O,SMALL($A$10:$A$300,ROWS($1:1))+1))
>
> In D10:
> =IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!S:S,SMALL($A$10:$A$300,ROWS($1:1))+1))
>
> In E10:
> =IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!U:U,SMALL($A$10:$A$300,ROWS($1:1))+1))
>
> In F10:
> =IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!V:V,SMALL($A$10:$A$300,ROWS($1:1))+1))
>
> In G10:
> =IF(B10="","",INDEX(X!$U:$U,MATCH(B10,X!$A:$A,0)))
>
> Select A10:G10, copy down to G300. Cols B to G will auto-return the required
> results from sheets X and Y, with all lines neatly bunched at the top.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Phill_Morgan" wrote:
> > Hi All,
> >
> > I am trying to build a maintenance cntract tracking system. This workbook
> > contains all my infrastructure assets, maintenance start/end dates, contact
> > details, contract numbers, costs and other miscellaneous data. One of the
> > results I need to produce is a list of all maintenance contracts that will
> > expire by 30th of the next calendar month. Eg. If we are in August, I want to
> > return anything that will expire before 30th September in the current year.
> >
> > I have the asset name in column A of sheet1 and sheet2. I need to extract
> > only the rows based on the date in column U in sheet2 and merge colums A and
> > U from sheet 1 with columns O, S, U and V from sheet2 and place the results
> > in Sheet3.
> >
> > Sheet3 has headings and other content, so the placement needs to start at
> > row 10. As the result list length could be variable, and my total length of
> > the table in sheets 1 & 2 is 280 rows I woulkd want to ensure that rows after
> > the end of the extract in sheet3 are blanked out.
> >
> > Is this possible in Excel 2003 natively? or will it require scripting? If
> > so, could you please suggest appropriate code.
> >
> > Many thanks,
> > Phillip Morgan
> >
> >
> >

jeez, how do I edit my own posts...

I adjusted the +1 in the rows to start at row 10 and the dates and dollars
show up fine, the only things not working is showing the asset name, and the
contract number is not finding a match, and so returning #N/A.

Also, does it matter than column U in sheet 2 is a calculated value? The
maintenance start date + number of years of contract generates the end date
of the contract.

phill
"Phill_Morgan" wrote:

> Hi Max,
>
> Thanks for your response. Unfortunately it did not yeild the expected results.
>
> I got no asset names in sheet 3, just blank cells and numbers.
> Dates were retured as 01/01/1900 for some assets. Column B returned 0 for
> each row. All $ values were 0. The last column, which should have been a text
> field (contract number), just returned #N/A. The rows were not bunched.
>
> Also, I should have mentioned, ALL sheets have the headings on row 9. Data
> always starts at row 10 in every sheet (as such I changed U2 in formula in
> A10 to U10).
>
> Phill
>
>
> "Max" wrote:
>
> > One approach using non-array formulas which should deliver it for you ..
> >
> > Illustrated in this "plug-n-play" sample construct:
> > http://www.savefile.com/files/994757
> > Maintenance Contract Tracking Sys.xls
> >
> > Assume source data as posted in row2 down
> > in sheets: X (your sheet1) and Y (your sheet2)
> > [I like to use v.short sheetnames, makes formulas v.shorter]
> >
> > In a sheet: Z (this is your sheet3),
> >
> > In A10:
> > =IF(Y!U2="","",IF(Y!U2<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0),ROWS($1:1),""))
> >
> > In B10:
> > =IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!A:A,SMALL($A$10:$A$300,ROWS($1:1))+1))
> >
> > In C10:
> > =IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!O:O,SMALL($A$10:$A$300,ROWS($1:1))+1))
> >
> > In D10:
> > =IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!S:S,SMALL($A$10:$A$300,ROWS($1:1))+1))
> >
> > In E10:
> > =IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!U:U,SMALL($A$10:$A$300,ROWS($1:1))+1))
> >
> > In F10:
> > =IF(ROWS($1:1)>COUNT($A$10:$A$300),"",INDEX(Y!V:V,SMALL($A$10:$A$300,ROWS($1:1))+1))
> >
> > In G10:
> > =IF(B10="","",INDEX(X!$U:$U,MATCH(B10,X!$A:$A,0)))
> >
> > Select A10:G10, copy down to G300. Cols B to G will auto-return the required
> > results from sheets X and Y, with all lines neatly bunched at the top.
> > --
> > Max
> > Singapore
> > http://savefile.com/projects/236895
> > xdemechanik
> > ---
> > "Phill_Morgan" wrote:
> > > Hi All,
> > >
> > > I am trying to build a maintenance cntract tracking system. This workbook
> > > contains all my infrastructure assets, maintenance start/end dates, contact
> > > details, contract numbers, costs and other miscellaneous data. One of the
> > > results I need to produce is a list of all maintenance contracts that will
> > > expire by 30th of the next calendar month. Eg. If we are in August, I want to
> > > return anything that will expire before 30th September in the current year.
> > >
> > > I have the asset name in column A of sheet1 and sheet2. I need to extract
> > > only the rows based on the date in column U in sheet2 and merge colums A and
> > > U from sheet 1 with columns O, S, U and V from sheet2 and place the results
> > > in Sheet3.
> > >
> > > Sheet3 has headings and other content, so the placement needs to start at
> > > row 10. As the result list length could be variable, and my total length of
> > > the table in sheets 1 & 2 is 280 rows I woulkd want to ensure that rows after
> > > the end of the extract in sheet3 are blanked out.
> > >
> > > Is this possible in Excel 2003 natively? or will it require scripting? If
> > > so, could you please suggest appropriate code.
> > >
> > > Many thanks,
> > > Phillip Morgan
> > >
> > >
> > >

"Phill_Morgan" wrote:
> jeez, how do I edit my own posts...

You can't <g>. But you can always post a reply to your own post, like you
did here.

> I adjusted the +1 in the rows to start at row 10 and the dates and dollars
> show up fine, the only things not working is showing the asset name, and the
> contract number is not finding a match, and so returning #N/A.

Here's the revised working construct to suit your actuals:
http://cjoint.com/?iygs4bCaH0
Maintenance Contract Tracking Sys_v1.xls

The adjustment to the formulas in B10:F10 is just change the "+1" to "+9"

> Also, does it matter than column U in sheet 2 is a calculated value? The
> maintenance start date + number of years of contract generates the end date
> of the contract.

Provided the dates calculated are real dates (like the dummy real dates in
my sample), it shouldn't matter. If you experience problems, post your
formula calculating the dates.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sorry for the delay in responding. Weekend :-)

It doesn't work. This is the results I get.


0 1/01/2001 13/08/200711/08/2008 55641.36 #N/A
0 1/12/2006 1/12/2008 30/11/2010 144000.00 #N/A
0 0/01/1900 0/01/1900 0.00 #N/A

Column A is blank. B is not showing the Asset name, and Contract # is #N/A.

The date being calcualted for End date in sheet 2 (column U), is

=IF(S10<>"",S10+((T10-1)*365)+364,"")

T10 is ther term of the contract in years. For leap years, this ends up
being a day out, but I can live with that. This date is then formatted as
DD/MM/YYYY (AU format)

Phill

"Max" wrote:

> "Phill_Morgan" wrote:
> > jeez, how do I edit my own posts...
>
> You can't <g>. But you can always post a reply to your own post, like you
> did here.
>
> > I adjusted the +1 in the rows to start at row 10 and the dates and dollars
> > show up fine, the only things not working is showing the asset name, and the
> > contract number is not finding a match, and so returning #N/A.
>
> Here's the revised working construct to suit your actuals:
> http://cjoint.com/?iygs4bCaH0
> Maintenance Contract Tracking Sys_v1.xls
>
> The adjustment to the formulas in B10:F10 is just change the "+1" to "+9"
>
> > Also, does it matter than column U in sheet 2 is a calculated value? The
> > maintenance start date + number of years of contract generates the end date
> > of the contract.
>
> Provided the dates calculated are real dates (like the dummy real dates in
> my sample), it shouldn't matter. If you experience problems, post your
> formula calculating the dates.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

> It doesn't work.

well, think it works, as evidenced in the latest sample earlier, which I had
thought was customised to fit 99.9%.

Presume this is in U10:
> =IF(S10<>"",S10+((T10-1)*365)+364,"")
where S10 contains the start-date, and T10 contains a number (no. of years
of contract)

To derive the end-dates automatically, suggest you replace the above with
this expression using DATE in U10:
=IF(S10<>"",DATE(YEAR(S10)+T10,MONTH(S10),DAY(S10)-1))
Copy U10 down. That should return the exact end-dates in col U.

As for your overall adaptation difficulties, perhaps you could use either of
the 2 filehosts below to upload a sample of your actuals (Desensitize it
first). Then copy n paste the generated link to your sample file in response
here:

http://www.flypicture.com/
http://cjoint.com/index.php

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Phill_Morgan" <(E-Mail Removed)> wrote in message
news:C1866F51-3E53-4EE5-B66B-(E-Mail Removed)...
> Sorry for the delay in responding. Weekend :-)
>
> It doesn't work. This is the results I get.
>
>
> 0 1/01/2001 13/08/200711/08/2008 55641.36 #N/A
> 0 1/12/2006 1/12/2008 30/11/2010 144000.00 #N/A
> 0 0/01/1900 0/01/1900 0.00 #N/A
>
> Column A is blank. B is not showing the Asset name, and Contract # is
> #N/A.
>
> The date being calcualted for End date in sheet 2 (column U), is
>
> =IF(S10<>"",S10+((T10-1)*365)+364,"")
>
> T10 is ther term of the contract in years. For leap years, this ends up
> being a day out, but I can live with that. This date is then formatted as
> DD/MM/YYYY (AU format)
>
> Phill

Errata:
> =IF(S10<>"",DATE(YEAR(S10)+T10,MONTH(S10),DAY(S10)-1))

should have read:
=IF(AND(S10<>"",T10<>""),DATE(YEAR(S10)+T10,MONTH(S10),DAY(S10)-1),"")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,

thanks so much for your help. I found the problem.
I had column A hidden in sheet one. The asset name was actually in column B,
so the reference to A:A and $A$A needed to be changed to B:B and $B$B
respectively.

this fixed the problem.

thanks so much for your assistance. Greatly appreciated.

Phill

"Max" wrote:

> Errata:
> > =IF(S10<>"",DATE(YEAR(S10)+T10,MONTH(S10),DAY(S10)-1))
>
> should have read:
> =IF(AND(S10<>"",T10<>""),DATE(YEAR(S10)+T10,MONTH(S10),DAY(S10)-1),"")
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
>
>
>

Welcome, glad you got it working.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Phill_Morgan" <(E-Mail Removed)> wrote in message
news:8E38849A-577E-424B-9A45-(E-Mail Removed)...
> Max,
>
> thanks so much for your help. I found the problem.
> I had column A hidden in sheet one. The asset name was actually in column
> B,
> so the reference to A:A and $A$A needed to be changed to B:B and $B$B
> respectively.
>
> this fixed the problem.
>
> thanks so much for your assistance. Greatly appreciated.
>
> Phill