I would really appreciate if someone can help me with this issue.


I have a project that will last 6 months with 31 milestones (M1 through

M152)(deadlines), and I am trying to create a calendar showing the
milestones. My goal is to create a formula that would allow me to enter

just the milestone number, and have the rest of the text auto
populated. i.e. (1=Milestone 1 = draft outline, 2=Milestone 2=meeting 1

with engineer)


So have I have been succesful to come up with the following formula:


COLUM A:
Cell A1 M1: Draft outline
Cell A2 M2: Draft Presentation
Cell A3 M3: Draft Cover Letter
Cell A4 M4: Draft Management
Cell A5 M5: Staffing


COLUM B, cell B1:
=if(B1=1,A1, if(b1=2,A2,if((b1=3,A4, if(b1=5,A5)...


Since I have 31 of these milestones, I did this 5 a times and named the

range.
M1 through M7 = m1tom7
M8 through M14= m8tom14
M14 through M20 = m14tom20
M20 through M26 = m20tom26
M26 through M31 = m20tom26


Then I concatenated the formula, as follows:


=concatenate(m1tom7,m8tom14,m1*4tom20,m20tom26,m20 tom26)


This is where I am stuck. This only works for cell B1. I do not know
how to make this work for every single cell in the calendar.


I also cannot figure out how can I "hardcore" this fomula into each
cell, so I only enter the number into the cell, and it will
automatically know to change it to the milestone number. i.e.. I enter
1 in b2, b2 shows M2:Draft Presentation.


Any help will be greatly appreciated.


Eckecko.

B2: = INDIRECT("A"&B1)

Does that help?

--
HTH

Bob Phillips

wrote in message
ups.com...
I would really appreciate if someone can help me with this issue.


I have a project that will last 6 months with 31 milestones (M1 through

M152)(deadlines), and I am trying to create a calendar showing the
milestones. My goal is to create a formula that would allow me to enter

just the milestone number, and have the rest of the text auto
populated. i.e. (1=Milestone 1 = draft outline, 2=Milestone 2=meeting 1

with engineer)


So have I have been succesful to come up with the following formula:


COLUM A:
Cell A1 M1: Draft outline
Cell A2 M2: Draft Presentation
Cell A3 M3: Draft Cover Letter
Cell A4 M4: Draft Management
Cell A5 M5: Staffing


COLUM B, cell B1:
=if(B1=1,A1, if(b1=2,A2,if((b1=3,A4, if(b1=5,A5)...


Since I have 31 of these milestones, I did this 5 a times and named the

range.
M1 through M7 = m1tom7
M8 through M14= m8tom14
M14 through M20 = m14tom20
M20 through M26 = m20tom26
M26 through M31 = m20tom26


Then I concatenated the formula, as follows:


=concatenate(m1tom7,m8tom14,m1*4tom20,m20tom26,m20 tom26)


This is where I am stuck. This only works for cell B1. I do not know
how to make this work for every single cell in the calendar.


I also cannot figure out how can I "hardcore" this fomula into each
cell, so I only enter the number into the cell, and it will
automatically know to change it to the milestone number. i.e.. I enter
1 in b2, b2 shows M2:Draft Presentation.


Any help will be greatly appreciated.


Eckecko.

Easier would be to use a vlookup formula
Put your entries on column B (of another sheet, assume Sheet2) and put
numbers in column A of that sheet

1 M1: Draft Outline
2 M2: Draft Presentation
3 M3: Draft Cover Letter

then you can use a formula on the original sheet like like

=if(B1="","",Vlookup(B1,Sheet2!$A$1:$B$31),2,False )

you will be able to copy this formula to other cells.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
I would really appreciate if someone can help me with this issue.


I have a project that will last 6 months with 31 milestones (M1 through

M152)(deadlines), and I am trying to create a calendar showing the
milestones. My goal is to create a formula that would allow me to enter

just the milestone number, and have the rest of the text auto
populated. i.e. (1=Milestone 1 = draft outline, 2=Milestone 2=meeting 1

with engineer)


So have I have been succesful to come up with the following formula:


COLUM A:
Cell A1 M1: Draft outline
Cell A2 M2: Draft Presentation
Cell A3 M3: Draft Cover Letter
Cell A4 M4: Draft Management
Cell A5 M5: Staffing


COLUM B, cell B1:
=if(B1=1,A1, if(b1=2,A2,if((b1=3,A4, if(b1=5,A5)...


Since I have 31 of these milestones, I did this 5 a times and named the

range.
M1 through M7 = m1tom7
M8 through M14= m8tom14
M14 through M20 = m14tom20
M20 through M26 = m20tom26
M26 through M31 = m20tom26


Then I concatenated the formula, as follows:


=concatenate(m1tom7,m8tom14,m1*4tom20,m20tom26,m20 tom26)


This is where I am stuck. This only works for cell B1. I do not know
how to make this work for every single cell in the calendar.


I also cannot figure out how can I "hardcore" this fomula into each
cell, so I only enter the number into the cell, and it will
automatically know to change it to the milestone number. i.e.. I enter
1 in b2, b2 shows M2:Draft Presentation.


Any help will be greatly appreciated.


Eckecko.

Hi,
Possibly using vlookups might help? I put the vlookup value in E1 and then
concantinated only two lookips, looks like you want to return 6 values with
the vlookup, so the table is larger and the return is larger too.

=VLOOKUP(E1,A1:D4,2,FALSE)&VLOOKUP(E1,A1:D4,3,FALS E)

Thanks,

"Bob Phillips" wrote:

B2: = INDIRECT("A"&B1)

Does that help?

--
HTH

Bob Phillips

wrote in message
ups.com...
I would really appreciate if someone can help me with this issue.


I have a project that will last 6 months with 31 milestones (M1 through

M152)(deadlines), and I am trying to create a calendar showing the
milestones. My goal is to create a formula that would allow me to enter

just the milestone number, and have the rest of the text auto
populated. i.e. (1=Milestone 1 = draft outline, 2=Milestone 2=meeting 1

with engineer)


So have I have been succesful to come up with the following formula:


COLUM A:
Cell A1 M1: Draft outline
Cell A2 M2: Draft Presentation
Cell A3 M3: Draft Cover Letter
Cell A4 M4: Draft Management
Cell A5 M5: Staffing


COLUM B, cell B1:
=if(B1=1,A1, if(b1=2,A2,if((b1=3,A4, if(b1=5,A5)...


Since I have 31 of these milestones, I did this 5 a times and named the

range.
M1 through M7 = m1tom7
M8 through M14= m8tom14
M14 through M20 = m14tom20
M20 through M26 = m20tom26
M26 through M31 = m20tom26


Then I concatenated the formula, as follows:


=concatenate(m1tom7,m8tom14,m1Â*4tom20,m20tom26,m2 0tom26)


This is where I am stuck. This only works for cell B1. I do not know
how to make this work for every single cell in the calendar.


I also cannot figure out how can I "hardcore" this fomula into each
cell, so I only enter the number into the cell, and it will
automatically know to change it to the milestone number. i.e.. I enter
1 in b2, b2 shows M2:Draft Presentation.


Any help will be greatly appreciated.


Eckecko.

Thank you very much Bob Phillips. I do not understand, but I will try
it. :)

Thank you very muchf Tom. I will try this right now. :)