Hi,

Hopefully someone can help, this seems easy but just can't think how to do
it without macros etc...

I have 2 sheets, on sheet 1 i have a list of project titles in column C, now
in sheet 2 i have a resource planner that dedicates 3 rows to each project in
sheet 1.

As i am doing VLOOKUPS in Sheet 2 i need to reference the project titles, so
in the second sheet in column A i want to copy each project title 3 times in
column A on consecutive rows, then move onto the next for a further 3 times
etc...

So i want Sheet 2 to look like this:


Project A
Project A
Project A
Project B
Project B
Project B
etc...

and i need to copy paste this all the way down the spreadsheet for any newly
added project in Sheet1.

What formula do i need to use so i can copy down easily and pull in the
project names to sheet 2 in the way that i need.

Thanks for any help in advance.

NP

That would require an event macro
I will assume that the first project name on Sheet1 is in A2
Use a formula such as =IF(ISBLANK(Sheet1!A2),"",Sheet1!A2)
copy this down the column. If you think you may have 100 projects, copy it
to A200 to be safe!
If A2 on sheet 1 is not blank then the project name appears in the cell on
Sheet 2, otherwise you see nothing in that cell

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Seftyby" wrote in message
...

Hi,

Hopefully someone can help, this seems easy but just can't think how to do
it without macros etc...

I have 2 sheets, on sheet 1 i have a list of project titles in column C,
now
in sheet 2 i have a resource planner that dedicates 3 rows to each project
in
sheet 1.

As i am doing VLOOKUPS in Sheet 2 i need to reference the project titles,
so
in the second sheet in column A i want to copy each project title 3 times
in
column A on consecutive rows, then move onto the next for a further 3
times
etc...

So i want Sheet 2 to look like this:


Project A
Project A
Project A
Project B
Project B
Project B
etc...

and i need to copy paste this all the way down the spreadsheet for any
newly
added project in Sheet1.

What formula do i need to use so i can copy down easily and pull in the
project names to sheet 2 in the way that i need.

Thanks for any help in advance.

NP

I guess a macro would also do, seeing as though i only have to do this once!

"Seftyby" wrote:


Hi,

Hopefully someone can help, this seems easy but just can't think how to do
it without macros etc...

I have 2 sheets, on sheet 1 i have a list of project titles in column C, now
in sheet 2 i have a resource planner that dedicates 3 rows to each project in
sheet 1.

As i am doing VLOOKUPS in Sheet 2 i need to reference the project titles, so
in the second sheet in column A i want to copy each project title 3 times in
column A on consecutive rows, then move onto the next for a further 3 times
etc...

So i want Sheet 2 to look like this:


Project A
Project A
Project A
Project B
Project B
Project B
etc...

and i need to copy paste this all the way down the spreadsheet for any newly
added project in Sheet1.

What formula do i need to use so i can copy down easily and pull in the
project names to sheet 2 in the way that i need.

Thanks for any help in advance.

NP

Hi Bernard,

There are no blank cells, will this copy the first project name 3 times and
then move onto the next though?

"Bernard Liengme" wrote:

That would require an event macro
I will assume that the first project name on Sheet1 is in A2
Use a formula such as =IF(ISBLANK(Sheet1!A2),"",Sheet1!A2)
copy this down the column. If you think you may have 100 projects, copy it
to A200 to be safe!
If A2 on sheet 1 is not blank then the project name appears in the cell on
Sheet 2, otherwise you see nothing in that cell

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Seftyby" wrote in message
...

Hi,

Hopefully someone can help, this seems easy but just can't think how to do
it without macros etc...

I have 2 sheets, on sheet 1 i have a list of project titles in column C,
now
in sheet 2 i have a resource planner that dedicates 3 rows to each project
in
sheet 1.

As i am doing VLOOKUPS in Sheet 2 i need to reference the project titles,
so
in the second sheet in column A i want to copy each project title 3 times
in
column A on consecutive rows, then move onto the next for a further 3
times
etc...

So i want Sheet 2 to look like this:


Project A
Project A
Project A
Project B
Project B
Project B
etc...

and i need to copy paste this all the way down the spreadsheet for any
newly
added project in Sheet1.

What formula do i need to use so i can copy down easily and pull in the
project names to sheet 2 in the way that i need.

Thanks for any help in advance.

NP

Try this:

=INDEX(Sheet1!C:C,INT((ROWS($1:1)-1)/3)+1)

copy down as far as needed


"Seftyby" wrote:


Hi,

Hopefully someone can help, this seems easy but just can't think how to do
it without macros etc...

I have 2 sheets, on sheet 1 i have a list of project titles in column C, now
in sheet 2 i have a resource planner that dedicates 3 rows to each project in
sheet 1.

As i am doing VLOOKUPS in Sheet 2 i need to reference the project titles, so
in the second sheet in column A i want to copy each project title 3 times in
column A on consecutive rows, then move onto the next for a further 3 times
etc...

So i want Sheet 2 to look like this:


Project A
Project A
Project A
Project B
Project B
Project B
etc...

and i need to copy paste this all the way down the spreadsheet for any newly
added project in Sheet1.

What formula do i need to use so i can copy down easily and pull in the
project names to sheet 2 in the way that i need.

Thanks for any help in advance.

NP

Why not try it and see what you get
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Seftyby" wrote in message
...
Hi Bernard,

There are no blank cells, will this copy the first project name 3 times
and
then move onto the next though?

"Bernard Liengme" wrote:

That would require an event macro
I will assume that the first project name on Sheet1 is in A2
Use a formula such as =IF(ISBLANK(Sheet1!A2),"",Sheet1!A2)
copy this down the column. If you think you may have 100 projects, copy
it
to A200 to be safe!
If A2 on sheet 1 is not blank then the project name appears in the cell
on
Sheet 2, otherwise you see nothing in that cell

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Seftyby" wrote in message
...

Hi,

Hopefully someone can help, this seems easy but just can't think how to
do
it without macros etc...

I have 2 sheets, on sheet 1 i have a list of project titles in column
C,
now
in sheet 2 i have a resource planner that dedicates 3 rows to each
project
in
sheet 1.

As i am doing VLOOKUPS in Sheet 2 i need to reference the project
titles,
so
in the second sheet in column A i want to copy each project title 3
times
in
column A on consecutive rows, then move onto the next for a further 3
times
etc...

So i want Sheet 2 to look like this:


Project A
Project A
Project A
Project B
Project B
Project B
etc...

and i need to copy paste this all the way down the spreadsheet for any
newly
added project in Sheet1.

What formula do i need to use so i can copy down easily and pull in the
project names to sheet 2 in the way that i need.

Thanks for any help in advance.

NP

Perfect!!!

Just what i was looking for, thanks mama!

"Teethless mama" wrote:

Try this:

=INDEX(Sheet1!C:C,INT((ROWS($1:1)-1)/3)+1)

copy down as far as needed


"Seftyby" wrote:


Hi,

Hopefully someone can help, this seems easy but just can't think how to do
it without macros etc...

I have 2 sheets, on sheet 1 i have a list of project titles in column C, now
in sheet 2 i have a resource planner that dedicates 3 rows to each project in
sheet 1.

As i am doing VLOOKUPS in Sheet 2 i need to reference the project titles, so
in the second sheet in column A i want to copy each project title 3 times in
column A on consecutive rows, then move onto the next for a further 3 times
etc...

So i want Sheet 2 to look like this:


Project A
Project A
Project A
Project B
Project B
Project B
etc...

and i need to copy paste this all the way down the spreadsheet for any newly
added project in Sheet1.

What formula do i need to use so i can copy down easily and pull in the
project names to sheet 2 in the way that i need.

Thanks for any help in advance.

NP

Just as a follow up to this, now that i have the project rows in Sheet 2, i
need to group them in 3's so that i can collape and expand to overall project
overviews or detailed view.

It there an easy way to group each project without doing them individually
by highlighting and going to DATA -- GROUP AND OUTLINE -- GROUP, which will
take some time if i have to do it for each project set!

"Seftyby" wrote:

Perfect!!!

Just what i was looking for, thanks mama!

"Teethless mama" wrote:

Try this:

=INDEX(Sheet1!C:C,INT((ROWS($1:1)-1)/3)+1)

copy down as far as needed


"Seftyby" wrote:


Hi,

Hopefully someone can help, this seems easy but just can't think how to do
it without macros etc...

I have 2 sheets, on sheet 1 i have a list of project titles in column C, now
in sheet 2 i have a resource planner that dedicates 3 rows to each project in
sheet 1.

As i am doing VLOOKUPS in Sheet 2 i need to reference the project titles, so
in the second sheet in column A i want to copy each project title 3 times in
column A on consecutive rows, then move onto the next for a further 3 times
etc...

So i want Sheet 2 to look like this:


Project A
Project A
Project A
Project B
Project B
Project B
etc...

and i need to copy paste this all the way down the spreadsheet for any newly
added project in Sheet1.

What formula do i need to use so i can copy down easily and pull in the
project names to sheet 2 in the way that i need.

Thanks for any help in advance.

NP