Copying from one sheet to another

  • Thread starter Thread starter Seftyby
  • Start date Start date
S

Seftyby

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
 
Hi Bernard,

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

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

copy down as far as needed
 
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!
 
Back
Top