Next Date given one date

At this time, I cannot use VBA.  Here is my objective:

For a given treasury note security:
Cell A1, Date 6/17/2004 - today's date
Cell A2, Date 7/15/1997 - first coupon date 

I want to identify the "previous coupon date" and "next 
coupon date" from today's date based on the frequency of 
the "first coupon date".

Logically speaking: 
* Security pays 2 times each year
* Based on first coupon date, pay dates are: 1/15 & 7/15
* Given today is 6/17/04, the next pay date is 7/15/04.
* Given today is 6/17/04, the previous pay date is 1/15/04.

Can this be accomplished without VBA.

Any help is most appreciated.

Thank you.
0
anonymous (74722)
6/17/2004 1:45:49 PM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
1530 Views

Similar Articles

[PageSpeed] 33

If you're willing to use the Analysis Toolpak Add-in (Tools/Add-ins...), 
you can use COUPNCD:


    =COUPNCD(TODAY(),DATE(2029,1,15),2)


In article <1dfa901c45471$65dd3510$a001280a@phx.gbl>,
 "Katharine" <anonymous@discussions.microsoft.com> wrote:

> At this time, I cannot use VBA.  Here is my objective:
> 
> For a given treasury note security:
> Cell A1, Date 6/17/2004 - today's date
> Cell A2, Date 7/15/1997 - first coupon date 
> 
> I want to identify the "previous coupon date" and "next 
> coupon date" from today's date based on the frequency of 
> the "first coupon date".
> 
> Logically speaking: 
> * Security pays 2 times each year
> * Based on first coupon date, pay dates are: 1/15 & 7/15
> * Given today is 6/17/04, the next pay date is 7/15/04.
> * Given today is 6/17/04, the previous pay date is 1/15/04.
> 
> Can this be accomplished without VBA.
> 
> Any help is most appreciated.
> 
> Thank you.
0
jemcgimpsey (6723)
6/17/2004 2:20:44 PM
I am looking up a text string in a1 and seeing if I have a match in a row
For example row B EXAMPLE If I have a match in B2-Z25 I use the index
command to exract a value from within the row AA2 to AX2. No problem so
far! This I can do.However what happens if the same text string turns up in
B2,G2,H2,Y2 or in more  cells from columns 1-25, how do I pull out using
the index function values in the range AA2-AX2 and combine their values(ie
get their sum).


Maybe there is another way.


What I am doing is looking up a fund name in A1. Clients names are in
Column A. If a client holds the fund I extract the number of units he holds
by using the index command to pull the number of units he holds for that
fund. Columns B-Z contain Fund names. Columns AA-AY contain number of units
held fror each fund. eg Col B is Fund 1, Colum AA is number of units held
in Fund 1. If the fund in a1 turns up more than once for a client the
problem is to determine his total number of units he holds for that fund


0
rcayne (5)
6/20/2004 4:46:54 AM
This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/xindexvba.zip
It's in the "Excel for Lotus 123 Users" section on page:
http://www.bygsoftware.com/examples/examples.htm

In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
workbook shows two Excel formula constructions that achieve the same result.

The first example uses two additional inputs. It uses the Excel functions:
INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
function. It uses the Excel functions: INDEX, MATCH and OFFSET.

There are also two additional pieces of VBA showing how to use this in code
and capture an error condition.

The code is open and commented.

There's also an alternative to this using SUMPRODUCT at:
http://www.bygsoftware.com/Excel/functions/sumproduct.htm

(and please don't cross post ;-)
-- 
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Ronald Cayne" <rcayne@videotron.ca> wrote in message
news:40D516BC.8AF556EF@videotron.ca...
> I am looking up a text string in a1 and seeing if I have a match in a row
> For example row B EXAMPLE If I have a match in B2-Z25 I use the index
> command to exract a value from within the row AA2 to AX2. No problem so
> far! This I can do.However what happens if the same text string turns up
in
> B2,G2,H2,Y2 or in more  cells from columns 1-25, how do I pull out using
> the index function values in the range AA2-AX2 and combine their values(ie
> get their sum).
>
>
> Maybe there is another way.
>
>
> What I am doing is looking up a fund name in A1. Clients names are in
> Column A. If a client holds the fund I extract the number of units he
holds
> by using the index command to pull the number of units he holds for that
> fund. Columns B-Z contain Fund names. Columns AA-AY contain number of
units
> held fror each fund. eg Col B is Fund 1, Colum AA is number of units held
> in Fund 1. If the fund in a1 turns up more than once for a client the
> problem is to determine his total number of units he holds for that fund
>
>


0
6/20/2004 8:37:03 AM
I think I don;t quite understand this!.

Clients are in column A. Eg. Products in B2-Z26. Units held in products are
held in Columns Eg.  AA2-AX2. where AA2 contains the number of units held by
under product 1 etc.  If Client holds more than 1 of the same product. eg. XXX
Fund in B2 and XXX Fund in G2 and XXX Fund in H2. How do I get the total of
units held in this fund?? That is the problem . I do not have a problem in
establishing the amount of units on the first match of the fund lookup in A1.
The problem occurs if the same fund is held by the same client eg Product G2 or
G3 which may have been acquired at a different point in time.

Clear as mud !

Andy Wiggins wrote:

> This file might be a help:
> http://www.bygsoftware.com/examples/zipfiles/xindexvba.zip
> It's in the "Excel for Lotus 123 Users" section on page:
> http://www.bygsoftware.com/examples/examples.htm
>
> In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
> workbook shows two Excel formula constructions that achieve the same result.
>
> The first example uses two additional inputs. It uses the Excel functions:
> INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
> function. It uses the Excel functions: INDEX, MATCH and OFFSET.
>
> There are also two additional pieces of VBA showing how to use this in code
> and capture an error condition.
>
> The code is open and commented.
>
> There's also an alternative to this using SUMPRODUCT at:
> http://www.bygsoftware.com/Excel/functions/sumproduct.htm
>
> (and please don't cross post ;-)
> --
> Regards
> Andy Wiggins
> www.BygSoftware.com
> Home of "Save and BackUp",
> "The Excel Auditor" and "Byg Tools for VBA"
>
> "Ronald Cayne" <rcayne@videotron.ca> wrote in message
> news:40D516BC.8AF556EF@videotron.ca...
> > I am looking up a text string in a1 and seeing if I have a match in a row
> > For example row B EXAMPLE If I have a match in B2-Z25 I use the index
> > command to exract a value from within the row AA2 to AX2. No problem so
> > far! This I can do.However what happens if the same text string turns up
> in
> > B2,G2,H2,Y2 or in more  cells from columns 1-25, how do I pull out using
> > the index function values in the range AA2-AX2 and combine their values(ie
> > get their sum).
> >
> >
> > Maybe there is another way.
> >
> >
> > What I am doing is looking up a fund name in A1. Clients names are in
> > Column A. If a client holds the fund I extract the number of units he
> holds
> > by using the index command to pull the number of units he holds for that
> > fund. Columns B-Z contain Fund names. Columns AA-AY contain number of
> units
> > held fror each fund. eg Col B is Fund 1, Colum AA is number of units held
> > in Fund 1. If the fund in a1 turns up more than once for a client the
> > problem is to determine his total number of units he holds for that fund
> >
> >

0
rcayne (5)
6/20/2004 8:20:31 PM
Unless I misunderstand the exercise (a small illustration, say 3 rows 
and 9 columns, would have been immensely helpful), with the client name 
in BA1 and the product name in BB1, then unless I made an error in 
converting the formula in my small test range to the one needed in your 
large stated range

=SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z"&MATCH(BA1,A2:A26,0)),BB1,INDIRECT("AA"&MATCH(BA1,A2:A26,0)&":AX"&MATCH(BA1,A2:A26,0)))

By the way, you stated 25 columns of products and 24 columns of units.

Alan Beban

Ronald Cayne wrote:

> I think I don;t quite understand this!.
> 
> Clients are in column A. Eg. Products in B2-Z26. Units held in products are
> held in Columns Eg.  AA2-AX2. where AA2 contains the number of units held by 
> under product 1 etc.  If Client holds more than 1 of the same product. eg. XXX
> Fund in B2 and XXX Fund in G2 and XXX Fund in H2. How do I get the total of
> units held in this fund?? That is the problem . I do not have a problem in
> establishing the amount of units on the first match of the fund lookup in A1.
> The problem occurs if the same fund is held by the same client eg Product G2 or
> G3 which may have been acquired at a different point in time.
> 
> Clear as mud !
> 
> Andy Wiggins wrote:
> 
> 
>>This file might be a help:
>>http://www.bygsoftware.com/examples/zipfiles/xindexvba.zip
>>It's in the "Excel for Lotus 123 Users" section on page:
>>http://www.bygsoftware.com/examples/examples.htm
>>
>>In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
>>workbook shows two Excel formula constructions that achieve the same result.
>>
>>The first example uses two additional inputs. It uses the Excel functions:
>>INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
>>function. It uses the Excel functions: INDEX, MATCH and OFFSET.
>>
>>There are also two additional pieces of VBA showing how to use this in code
>>and capture an error condition.
>>
>>The code is open and commented.
>>
>>There's also an alternative to this using SUMPRODUCT at:
>>http://www.bygsoftware.com/Excel/functions/sumproduct.htm
>>
>>(and please don't cross post ;-)
>>--
>>Regards
>>Andy Wiggins
>>www.BygSoftware.com
>>Home of "Save and BackUp",
>>"The Excel Auditor" and "Byg Tools for VBA"
>>
>>"Ronald Cayne" <rcayne@videotron.ca> wrote in message
>>news:40D516BC.8AF556EF@videotron.ca...
>>
>>>I am looking up a text string in a1 and seeing if I have a match in a row
>>>For example row B EXAMPLE If I have a match in B2-Z25 I use the index
>>>command to exract a value from within the row AA2 to AX2. No problem so
>>>far! This I can do.However what happens if the same text string turns up
>>
>>in
>>
>>>B2,G2,H2,Y2 or in more  cells from columns 1-25, how do I pull out using
>>>the index function values in the range AA2-AX2 and combine their values(ie
>>>get their sum).
>>>
>>>
>>>Maybe there is another way.
>>>
>>>
>>>What I am doing is looking up a fund name in A1. Clients names are in
>>>Column A. If a client holds the fund I extract the number of units he
>>
>>holds
>>
>>>by using the index command to pull the number of units he holds for that
>>>fund. Columns B-Z contain Fund names. Columns AA-AY contain number of
>>
>>units
>>
>>>held fror each fund. eg Col B is Fund 1, Colum AA is number of units held
>>>in Fund 1. If the fund in a1 turns up more than once for a client the
>>>problem is to determine his total number of units he holds for that fund
>>>
>>>
> 
> 
0
unavailable (273)
6/20/2004 9:23:25 PM
"Alan Beban" <unavailable@no.com> wrote...
....
>=SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z"
>&MATCH(BA1,A2:A26,0)),BB1,INDIRECT("AA"
>&MATCH(BA1,A2:A26,0)&":AX"&MATCH(BA1,A2:A26,0)))
....

One problem: if BA1 matched the value in cell A2, then MATCH would return 1,
making the formula equivalent to

=SUMIF(B1:Z1,BB1,AA1:AX1)

Looks like an off-by-one index bug.

Aside from this, if the formula above does what the OP wants, so would

=SUMIF(INDEX(B2:Z26,MATCH(BA1,A2:A26,0),0),BB1,
INDEX(AA2:AY26,MATCH(BA1,A2:A26,0),0))


0
hrlngrv (1990)
6/21/2004 6:36:38 AM
Example file is at the following location. I had trouble uploading a file here

ftp://guest.crescentauctions.com:guest@crescentauctions.com/

The file name is test_file


Many thanks Alan for your help. If you can solve my problem using the indirect command would you kindly explain how it is working for
this solutione.



Regards

Ron

Alan Beban wrote:

> Unless I misunderstand the exercise (a small illustration, say 3 rows
> and 9 columns, would have been immensely helpful), with the client name
> in BA1 and the product name in BB1, then unless I made an error in
> converting the formula in my small test range to the one needed in your
> large stated range
>
> =SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z"&MATCH(BA1,A2:A26,0)),BB1,INDIRECT("AA"&MATCH(BA1,A2:A26,0)&":AX"&MATCH(BA1,A2:A26,0)))
>
> By the way, you stated 25 columns of products and 24 columns of units.
>
> Alan Beban
>
> Ronald Cayne wrote:
>
> > I think I don;t quite understand this!.
> >
> > Clients are in column A. Eg. Products in B2-Z26. Units held in products are
> > held in Columns Eg.  AA2-AX2. where AA2 contains the number of units held by
> > under product 1 etc.  If Client holds more than 1 of the same product. eg. XXX
> > Fund in B2 and XXX Fund in G2 and XXX Fund in H2. How do I get the total of
> > units held in this fund?? That is the problem . I do not have a problem in
> > establishing the amount of units on the first match of the fund lookup in A1.
> > The problem occurs if the same fund is held by the same client eg Product G2 or
> > G3 which may have been acquired at a different point in time.
> >
> > Clear as mud !
> >
> > Andy Wiggins wrote:
> >
> >
> >>This file might be a help:
> >>http://www.bygsoftware.com/examples/zipfiles/xindexvba.zip
> >>It's in the "Excel for Lotus 123 Users" section on page:
> >>http://www.bygsoftware.com/examples/examples.htm
> >>
> >>In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
> >>workbook shows two Excel formula constructions that achieve the same result.
> >>
> >>The first example uses two additional inputs. It uses the Excel functions:
> >>INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
> >>function. It uses the Excel functions: INDEX, MATCH and OFFSET.
> >>
> >>There are also two additional pieces of VBA showing how to use this in code
> >>and capture an error condition.
> >>
> >>The code is open and commented.
> >>
> >>There's also an alternative to this using SUMPRODUCT at:
> >>http://www.bygsoftware.com/Excel/functions/sumproduct.htm
> >>
> >>(and please don't cross post ;-)
> >>--
> >>Regards
> >>Andy Wiggins
> >>www.BygSoftware.com
> >>Home of "Save and BackUp",
> >>"The Excel Auditor" and "Byg Tools for VBA"
> >>
> >>"Ronald Cayne" <rcayne@videotron.ca> wrote in message
> >>news:40D516BC.8AF556EF@videotron.ca...
> >>
> >>>I am looking up a text string in a1 and seeing if I have a match in a row
> >>>For example row B EXAMPLE If I have a match in B2-Z25 I use the index
> >>>command to exract a value from within the row AA2 to AX2. No problem so
> >>>far! This I can do.However what happens if the same text string turns up
> >>
> >>in
> >>
> >>>B2,G2,H2,Y2 or in more  cells from columns 1-25, how do I pull out using
> >>>the index function values in the range AA2-AX2 and combine their values(ie
> >>>get their sum).
> >>>
> >>>
> >>>Maybe there is another way.
> >>>
> >>>
> >>>What I am doing is looking up a fund name in A1. Clients names are in
> >>>Column A. If a client holds the fund I extract the number of units he
> >>
> >>holds
> >>
> >>>by using the index command to pull the number of units he holds for that
> >>>fund. Columns B-Z contain Fund names. Columns AA-AY contain number of
> >>
> >>units
> >>
> >>>held fror each fund. eg Col B is Fund 1, Colum AA is number of units held
> >>>in Fund 1. If the fund in a1 turns up more than once for a client the
> >>>problem is to determine his total number of units he holds for that fund
> >>>
> >>>
> >
> >

0
rcayne (5)
6/24/2004 3:18:26 AM
The ftp address should have been:

ftp://guest.crescentauctions.com:guest@crescentauctions.com

Ronald Cayne wrote:

> Example file is at the following location. I had trouble uploading a file here
>
> ftp://guest.crescentauctions.com:guest@crescentauctions.com/
>
> The file name is test_file
>
> Many thanks Alan for your help. If you can solve my problem using the indirect command would you kindly explain how it is working for
> this solutione.
>
> Regards
>
> Ron
>
> Alan Beban wrote:
>
> > Unless I misunderstand the exercise (a small illustration, say 3 rows
> > and 9 columns, would have been immensely helpful), with the client name
> > in BA1 and the product name in BB1, then unless I made an error in
> > converting the formula in my small test range to the one needed in your
> > large stated range
> >
> > =SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z"&MATCH(BA1,A2:A26,0)),BB1,INDIRECT("AA"&MATCH(BA1,A2:A26,0)&":AX"&MATCH(BA1,A2:A26,0)))
> >
> > By the way, you stated 25 columns of products and 24 columns of units.
> >
> > Alan Beban
> >
> > Ronald Cayne wrote:
> >
> > > I think I don;t quite understand this!.
> > >
> > > Clients are in column A. Eg. Products in B2-Z26. Units held in products are
> > > held in Columns Eg.  AA2-AX2. where AA2 contains the number of units held by
> > > under product 1 etc.  If Client holds more than 1 of the same product. eg. XXX
> > > Fund in B2 and XXX Fund in G2 and XXX Fund in H2. How do I get the total of
> > > units held in this fund?? That is the problem . I do not have a problem in
> > > establishing the amount of units on the first match of the fund lookup in A1.
> > > The problem occurs if the same fund is held by the same client eg Product G2 or
> > > G3 which may have been acquired at a different point in time.
> > >
> > > Clear as mud !
> > >
> > > Andy Wiggins wrote:
> > >
> > >
> > >>This file might be a help:
> > >>http://www.bygsoftware.com/examples/zipfiles/xindexvba.zip
> > >>It's in the "Excel for Lotus 123 Users" section on page:
> > >>http://www.bygsoftware.com/examples/examples.htm
> > >>
> > >>In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
> > >>workbook shows two Excel formula constructions that achieve the same result.
> > >>
> > >>The first example uses two additional inputs. It uses the Excel functions:
> > >>INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
> > >>function. It uses the Excel functions: INDEX, MATCH and OFFSET.
> > >>
> > >>There are also two additional pieces of VBA showing how to use this in code
> > >>and capture an error condition.
> > >>
> > >>The code is open and commented.
> > >>
> > >>There's also an alternative to this using SUMPRODUCT at:
> > >>http://www.bygsoftware.com/Excel/functions/sumproduct.htm
> > >>
> > >>(and please don't cross post ;-)
> > >>--
> > >>Regards
> > >>Andy Wiggins
> > >>www.BygSoftware.com
> > >>Home of "Save and BackUp",
> > >>"The Excel Auditor" and "Byg Tools for VBA"
> > >>
> > >>"Ronald Cayne" <rcayne@videotron.ca> wrote in message
> > >>news:40D516BC.8AF556EF@videotron.ca...
> > >>
> > >>>I am looking up a text string in a1 and seeing if I have a match in a row
> > >>>For example row B EXAMPLE If I have a match in B2-Z25 I use the index
> > >>>command to exract a value from within the row AA2 to AX2. No problem so
> > >>>far! This I can do.However what happens if the same text string turns up
> > >>
> > >>in
> > >>
> > >>>B2,G2,H2,Y2 or in more  cells from columns 1-25, how do I pull out using
> > >>>the index function values in the range AA2-AX2 and combine their values(ie
> > >>>get their sum).
> > >>>
> > >>>
> > >>>Maybe there is another way.
> > >>>
> > >>>
> > >>>What I am doing is looking up a fund name in A1. Clients names are in
> > >>>Column A. If a client holds the fund I extract the number of units he
> > >>
> > >>holds
> > >>
> > >>>by using the index command to pull the number of units he holds for that
> > >>>fund. Columns B-Z contain Fund names. Columns AA-AY contain number of
> > >>
> > >>units
> > >>
> > >>>held fror each fund. eg Col B is Fund 1, Colum AA is number of units held
> > >>>in Fund 1. If the fund in a1 turns up more than once for a client the
> > >>>problem is to determine his total number of units he holds for that fund
> > >>>
> > >>>
> > >
> > >

0
rcayne (5)
6/24/2004 3:20:25 AM
Please see a sample file at the following ftp site:

ftp://guest.crescentauctions.com:guest@crescentauctions.com

I really want to understand the solution to this problem

Ron

Alan Beban wrote:

> Unless I misunderstand the exercise (a small illustration, say 3 rows
> and 9 columns, would have been immensely helpful), with the client name
> in BA1 and the product name in BB1, then unless I made an error in
> converting the formula in my small test range to the one needed in your
> large stated range
>
> =SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z"&MATCH(BA1,A2:A26,0)),BB1,INDIRECT("AA"&MATCH(BA1,A2:A26,0)&":AX"&MATCH(BA1,A2:A26,0)))
>
> By the way, you stated 25 columns of products and 24 columns of units.
>
> Alan Beban
>
> Ronald Cayne wrote:
>
> > I think I don;t quite understand this!.
> >
> > Clients are in column A. Eg. Products in B2-Z26. Units held in products are
> > held in Columns Eg.  AA2-AX2. where AA2 contains the number of units held by
> > under product 1 etc.  If Client holds more than 1 of the same product. eg. XXX
> > Fund in B2 and XXX Fund in G2 and XXX Fund in H2. How do I get the total of
> > units held in this fund?? That is the problem . I do not have a problem in
> > establishing the amount of units on the first match of the fund lookup in A1.
> > The problem occurs if the same fund is held by the same client eg Product G2 or
> > G3 which may have been acquired at a different point in time.
> >
> > Clear as mud !
> >
> > Andy Wiggins wrote:
> >
> >
> >>This file might be a help:
> >>http://www.bygsoftware.com/examples/zipfiles/xindexvba.zip
> >>It's in the "Excel for Lotus 123 Users" section on page:
> >>http://www.bygsoftware.com/examples/examples.htm
> >>
> >>In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
> >>workbook shows two Excel formula constructions that achieve the same result.
> >>
> >>The first example uses two additional inputs. It uses the Excel functions:
> >>INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
> >>function. It uses the Excel functions: INDEX, MATCH and OFFSET.
> >>
> >>There are also two additional pieces of VBA showing how to use this in code
> >>and capture an error condition.
> >>
> >>The code is open and commented.
> >>
> >>There's also an alternative to this using SUMPRODUCT at:
> >>http://www.bygsoftware.com/Excel/functions/sumproduct.htm
> >>
> >>(and please don't cross post ;-)
> >>--
> >>Regards
> >>Andy Wiggins
> >>www.BygSoftware.com
> >>Home of "Save and BackUp",
> >>"The Excel Auditor" and "Byg Tools for VBA"
> >>
> >>"Ronald Cayne" <rcayne@videotron.ca> wrote in message
> >>news:40D516BC.8AF556EF@videotron.ca...
> >>
> >>>I am looking up a text string in a1 and seeing if I have a match in a row
> >>>For example row B EXAMPLE If I have a match in B2-Z25 I use the index
> >>>command to exract a value from within the row AA2 to AX2. No problem so
> >>>far! This I can do.However what happens if the same text string turns up
> >>
> >>in
> >>
> >>>B2,G2,H2,Y2 or in more  cells from columns 1-25, how do I pull out using
> >>>the index function values in the range AA2-AX2 and combine their values(ie
> >>>get their sum).
> >>>
> >>>
> >>>Maybe there is another way.
> >>>
> >>>
> >>>What I am doing is looking up a fund name in A1. Clients names are in
> >>>Column A. If a client holds the fund I extract the number of units he
> >>
> >>holds
> >>
> >>>by using the index command to pull the number of units he holds for that
> >>>fund. Columns B-Z contain Fund names. Columns AA-AY contain number of
> >>
> >>units
> >>
> >>>held fror each fund. eg Col B is Fund 1, Colum AA is number of units held
> >>>in Fund 1. If the fund in a1 turns up more than once for a client the
> >>>problem is to determine his total number of units he holds for that fund
> >>>
> >>>
> >
> >

0
rcayne (5)
6/25/2004 2:33:07 AM
Reply:

Similar Artilces:

Multiple addresses for one company
Hi Everybody, Hope you can help me. I have a company which manages two schools. They need or want to manage email from the three entities in Outlook separately. So what is the best way to have three mailboxes in one instance of Outlook with Exchange2k3/Win2k3 server. A little clarification I need to manage email for bob@company.com bob@school1.edu bob@school2.edu all in Outlook. hopefully separate mailboxes that don't talk to each other. I am not opposed to having multiple servers or whatever it takes. A kb article on the subject mught help. Thanks a bunch Tom virtual ORG http...

Counting Dates #2
I have 4 different cells that have dates in them C5, D5, E5, F5 and want to enter a formula in G5 that counts the dates. If there is a dat in C5 I want it to count 1, if there is a date in D5 I want it to coun another 1 and so on. How do I do this -- Message posted from http://www.ExcelForum.com Hi if these cells are either blank or have a date value try =COUNTA(C5:F5) >-----Original Message----- >I have 4 different cells that have dates in them C5, D5, E5, F5 and I >want to enter a formula in G5 that counts the dates. If there is a date >in C5 I want it to count 1, if there...

How to stop excel 2007 automatically changing my date format?
My spreadsheet has cells with the number format "hh:mm" into which users type start and finish times. When the start and finish times are on different days (night shift), the input must also contain the date like "15/5/2010 22:00" to make the sheet calc correctly. In these cases, excel changes my numberformat from "hh:mm" to "d/m/yy hh:mm" even though I specifically set it to "hh:mm". Can this be stopped? The more these new versions of excel try to anticipate our needs, the more annoying they become! TIA Damien ps I've worked arou...

Two columns one stacked
I am trying to create a chart that has three pieces of data (one objective and two actuals) and have them combined into two columns for specific areas. I'd like the objective to be one color and the actuals to be a stack of the two sets of actuals to show the detail between the two. I have been unsuccessful in creating such a chart. I'm not sure if this is even possible. I'm using Excel 2003. Thank you. See the second example at http://people.stfx.ca/bliengme/ExcelTips/Columns.htm best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ir...

manually displaying on the next line
How do I manually display words on the next line inside a cell? I always put extra spaces until the word is shifted to the next lnie. (Not using 'wrap' option and adjust the width of a cell) Albert, alt + enter will move your cursor to the next line. Cesar Zapata Albert Byun wrote: > How do I manually display words on the next line inside a cell? > I always put extra spaces until the word is shifted to the next lnie. > (Not using 'wrap' option and adjust the width of a cell) > > ...

Could I install MM Delux in the next year on the new maschine?
I thing my previous mail it the above subject gonne into the net:( So I reapet the question - my MM Delux Plus expires 11.10.2009. Could I install this MM on the new maschine in the nex year - I am goin go buy the new computer with Win 7. Greetings If you have available Activations you obtained prior to 6/30/2009 and activate prior to 1/31/2011, this is correct. Oh, and be sure and do an Internet Update to get the patches prior to 1/31/2011. One strategy I would recommend considering: install the M+ in a Win7 XP VPC VHD. Then protect that VHD file very carefully so that you might be a...

need to convert from one time expression to another
i get data from a billing system that stores time like 1:50 (which means 1 hour & 50 minutes) but i have to put it into a database that wants the time to be 110 minutes. the information that i get comes as a delimited text file that i open in excel clean up (i love macros) & then import into an access database. every month some one did the math to convert the time but i think there has to be a better way. any & all suggestion/ideas/help is appreciated. =A1*24*60, Format as General or Number. (not Time, which is what Excel does automatically) -- Kind regards, Niek Otten Micro...

Changing a 'Static' report to a 'Dynamic' one
Hi I'd be really grateful for any pointers for a problem that is driving me nuts! I am trying to change a 'static' report into a 'dynamic' one. I have a scheduling application, originally written in Access 97 which generates service dates for trucks - usually around 8-10 'Service Dates' per year. One of the features of the app is that 1 report shows on one page (A4 landscape) all the trucks, grouped by Owner, that have a Service Date in each week - 53 columns wide! I managed that by creating a select query and then into a CrossTab Query, where the Ow...

copy some data to next sheet
My sheets are named 1,2,3....100 at any time i may be located at any sheet. I wish to copy selected values to the same position from sheet "m" to the next "n" sheets where n is not going to run past 100. The from and to positions are concrete and the same. If you want colour they are mens wedding suit items and party name. Maybe you want this (there is no error check in this example if you are in sheet 100) Sub CopySelection() Dim myarea As Range For Each myarea In Selection.Areas With myarea .Copy Destination:=.Parent.Next.Range(.Address)...

Dates Within A Range
I have birthdates in a spreadsheet two ways. A1=Birth Mont A2=Birth Day or A3=mm/dd/yyyy I am trying to calculate zodiac sign from this, so I want to enter a formula for each sign to see if the date falls within a specific date range. For example, any birthdates from 03/21-04/20 would be Aires. I tried entering formula as follows: IF(A1=3)AND(A2>=21)OR(A1=4)AND(A2<=20),1,0 This does not work. Any suggestions either using the two column method of A1 & A2 cells or the combined bithdate in cell A3? Thanks. where your daterange is a proper date and your start date in b1, end dat...

1-9 not as a date
How can i make 1-9 to be just like it is. Before entering the value: Either format the cell as text or use an apostrophe as the first character in the cell '1-9 "Confused" wrote: > How can i make 1-9 to be just like it is. the simplest way is to put a apostrophy in front of it "Confused" wrote: > How can i make 1-9 to be just like it is. ...

How to change date from US to UK format
Hi all, I have a large-ish spreadsheet (4000 entries) with a date of birt column. For some reason when this file was ported from SPSS into Excel before being sent to me, the dates of birth got a bit screwed up. ABout a third of them are in the format I want. UK format wit day-month-year. The rest are in US format with the month first. At th moment I'm having to just go through them and type or copy hem into new column. Its essential I have the whole column in UK format. Is there any formulas that can do this for me to save me typing it al out?!! Any help would be MUCH appreciated!! -- ...

Language Changes when tabbing to next field.
I am running WinXP with Proofing Tools. My main language is EN, with CH Chinese PRC as a second language. I created the form in English, with only two Chinese characters on the form. When I try to enter data, with my language set to English, the language changed CH every time I go to a new cell. Since nearly all my data is English, this is incredible frustrating. The same thing happens enter data directly into the table. Does anyone have a solution. Anyone who can solve this shall be declared very clever---for life. ...

How to save in one step attachments in 10 emails?
A have about ten email and in each of them is attachement. How to save them to my folder in HDD? Outlook XP. http://www.slipstick.com/addins/housekeeping.htm should have something that you can use. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer Voslogin <voslogin@seznam.cz> asked: | A have about ten email and in each of them is attachement. How to | save them to my folder in HDD? | Outlook XP. THX "Milly Staples [MVP - Outlook]" <MillyS@mvps.org> p�e v disku...

Transaction dates a day off after daylight savings time
I hope someone can help, I've searched online and cannot find anything. Since the daylight savings time change, the transactions are downloaded as a day earlier. For example, in my online banking the date says 4/2, but when it downloads, it shows 4/1, in which I have to manually change so it can match my acct. The dates are correct in my online banking acct. I'm using Money 2006 deluxe. Please help! In microsoft.public.money, maxine97 wrote: >I hope someone can help, I've searched online and cannot find anything. >Since the daylight savings time change, the transa...

merge unformatted date with text
My original date column was formatted 02/01/71. I created a new column and created a custom date format; 020171. I would like to take this column and merge it together with the last 3 digits of a column where a ss# resides. This column is a text column. For example, the new column should look like 020171008. Can anyone help? I tried this based on your example and used the =Text (originaldate,mmddyy) function and used a =Right(ss#,3) to create a new column for the last three of the ss#. Then Concatenated the two fields resulting in 02171999. Good Luck! >-----Original Message...

How do to e-mail only one page of a workbook?
One page or one worksheet? Whatever the case see Ron de Bruin's site for methods. Also see his SendMail add-in which is very handy. http://www.rondebruin.nl/sendmail.htm Gord Dibben MS Excel MVP On Sun, 17 Jun 2007 17:38:01 -0700, william <william@discussions.microsoft.com> wrote: ...

Conditional Formating Based on Date #2
Hi, I need some help on conditional formating. What I want to do is change the color of a cell based on the date, i.e. if the date in the cell is today or greater I want the cell color to change to red, if the date is 3 days before the date in the cell I want it to change color to yellow. condition 1 formula is =a3>=today() cond 2 etc -- Don Guillett SalesAid Software donaldb@281.com "Roy" <Roy@discussions.microsoft.com> wrote in message news:08188B4D-4A0D-4121-834E-AAF96FB033EC@microsoft.com... > Hi, > > I need some help on conditional formating. What I wa...

Date and Time on Reports
How do I print the current/report date and time on reports as well as on receipt templates (POs, PORcv etc)? hi Alex, you can use the formula to translate the time: below is the code for you as example: (timesold is the datetime field) in RMs all transaction date field contain also time. Begin Column FieldName = "TimeSold" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Time Sold" VBDataType = vbString Formula = "replicate('0',2-len(cast(DatePart(Hh,[Transaction].Time) a...

Probably very easy question conerning auto-changing dates
I've got a list of dates in column B. Row 1 is labeled "date", Row 2 starts my date data (May-04). I want a list in column B of the preceding 23 months, so columb B ends with Jun-02. Obviously, I can manually input this information. What I can't figure out is how if I go and change the first date (say, to Jun-04) it will auto-change my list of dates accordingly (so my list would now end in Jul-02). NOTE: I know how to create a handle-fill, but it won't update any changes I make to the dates, nor can I get it to go backwards, chronologically. Thanks, Jacob jfarino@ameris...

How to show 5 data series on one chart
Hello, Here are the data that I have: - a category that will be my X axis - a percentage that will be my Y axis. I have a percentage value per category per year. - 3 company names - 2 years (2004 and 2009) What I want to show is the following: For each company, I would like to plot a solid colored line representing the different % for each category for 2004 and a dotted colored (same) line representing different % for each category for 2009. As a result I would get three sets of lines, three different colors, and within each set a solid and a dotted line. I currently have my table orga...

Combining Several Worksheet into one #3
I have over 30 excel worksheets that are: 1. Password protected 2. The sheet is also password protected 3. Each worksheet contains only one tab call "All" and this tab is in the same format and contains the same column in every sheet. 4. It's located in the same folder I need to write a macro that will open all these workbook in this folder and combine the data into one new sheet with only one tab called "All". I am able to write the code to open all the workbook but am having a difficult time figuring out how to copy only the cells with data into the new workbook...

Not receiving read-receipts from one user
Hello all, I have Exchange Server 2003 and many users on it which use read- receipts in Outlook. One user is having trouble receiving read- receipts from another, even though the person would like to allow read- receipts to go to the sender. I'd like to explain a bit more to make sure I am making good sense. User A is sending e-mail to User B with read-receipt turned on. User B would like to allow the read-receipts to go to User A as User B does not mind sending back read-receipts. However, when User B receives e- mail from User A, no pop up box comes up to allow the read-receipt to ...

Connectors from one page to the next
I am preparing a large Basic Flowchart that will take up several A3 pages. How do I make the connection between say a decision on page 1 with a process on page 2. I would like to print the document so that it looks like one continuous document The physical size of the drawing does not have to match the size of the paper used in the printer. You can create a single drawing and then let the printer split the drawing across three pages of paper. The page breaks will be visible in the Visio drawing so you can avoid placing items on the seams. John... Visio MVP Need stencils or ideas? h...

Sending several pictures as one whole imagen
Publisher 2007 Designing my Flyer I need to use several pictures . I want to send it as a WHOLE UNIQUE MESSAGE ...... NOT SHOWING MULTIPLE ATTATCHMENTS at the upper part of my Flyer. How can I avoid those references to the original pictures used to compose the flyer.? PDF is the preferred way. You could change your publication to a web page(HTML), but many folks do not accept HTML emails and the images will end up being attachments. There is an add-in for PDF on the Microsoft download web site. http://office.microsoft.com/en-us/downloads/CD101950461033.aspx -- Mary Sauer MSFT M...