EXcel Formula
 

hi

can anyone help; I have a spreadsheet which lists the clients within
our business and the month that they are due to be visited - The client
name is in column a1 followed by 12 columns representing the months of
the year b1-m1: at present, to record that a visit is due, the month is
manually entered in the approriate columns b1-m1 for example as "jan06"
in column b1; it is usually the case that a further visit will be due
in 3 months therefore, my question is this: I would like a formula that
will autumatically return "apr06" in cell e1 if "jan06" is entered in
cell b1 - i would like to show this in the format described and not as
a number -

Sorry if it sounds confusing - but can anyone help?


--
martins
------------------------------------------------------------------------
martins's Profile: http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=513106

EXcel Formula
 

If you put a real date in B2 (you can format it as mmmyy), then you can use

=IF(MONTH(B2)+3=COLUMN()-1,TEXT(DATE(YEAR(B2),MONTH(B2)+3,1),"mmmyy"),"")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"martins" wrote in
message ...

hi

can anyone help; I have a spreadsheet which lists the clients within
our business and the month that they are due to be visited - The client
name is in column a1 followed by 12 columns representing the months of
the year b1-m1: at present, to record that a visit is due, the month is
manually entered in the approriate columns b1-m1 for example as "jan06"
in column b1; it is usually the case that a further visit will be due
in 3 months therefore, my question is this: I would like a formula that
will autumatically return "apr06" in cell e1 if "jan06" is entered in
cell b1 - i would like to show this in the format described and not as
a number -

Sorry if it sounds confusing - but can anyone help?


--
martins
------------------------------------------------------------------------
martins's Profile:
http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=513106

EXcel Formula
 

Hi

I think Bob meant to put a $ sign in front of the column reference
before you enter the formula in C2 and copy across through D2:M2
=IF(MONTH($B2)+3=COLUMN()-1,TEXT(DATE(YEAR($B2),MONTH($B2)+3,1),"mmmyy"),"")

If you perhaps wanted to make it a little more flexible,and allow for
appointments which are any number of months ahead, then if you used
column C to record the number of months, 3, 6 or whatever then a
modification to Bob's formula
=IF(MONTH($B2)+$C2=COLUMN()-2,TEXT(DATE(YEAR($B2),MONTH($B2)+$C2,1),"mmmyy")," ")
pasted into D2 and copied across through E2:N2 would permit this.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
If you put a real date in B2 (you can format it as mmmyy), then you
can use

=IF(MONTH(B2)+3=COLUMN()-1,TEXT(DATE(YEAR(B2),MONTH(B2)+3,1),"mmmyy"),"")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"martins" wrote
in
message ...

hi

can anyone help; I have a spreadsheet which lists the clients within
our business and the month that they are due to be visited - The
client
name is in column a1 followed by 12 columns representing the months
of
the year b1-m1: at present, to record that a visit is due, the month
is
manually entered in the approriate columns b1-m1 for example as
"jan06"
in column b1; it is usually the case that a further visit will be due
in 3 months therefore, my question is this: I would like a formula
that
will autumatically return "apr06" in cell e1 if "jan06" is entered in
cell b1 - i would like to show this in the format described and not
as
a number -

Sorry if it sounds confusing - but can anyone help?


--
martins
------------------------------------------------------------------------
martins's Profile:
http://www.excelforum.com/member.php...o&userid=31616
View this thread:
http://www.excelforum.com/showthread...hreadid=513106

EXcel Formula
 

thanks for your help

do i literally need to enter the formula as is, or do I need to add
information to complete the equasion?


--
martins
------------------------------------------------------------------------
martins's Profile: http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=513106

EXcel Formula
 

With Roger's mod for column,. enter as is.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"martins" wrote in
message ...

thanks for your help

do i literally need to enter the formula as is, or do I need to add
information to complete the equasion?


--
martins
------------------------------------------------------------------------
martins's Profile:
http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=513106