basically I have a summary sheet which I want to display data of
"current in progress works". On the other worksheet I have the a column that
tells me which company is in progress (100 companies) so what I want to do is;

on worksheet 2 read colum O3 if there is a 1 (meaning in progress) then take
the company name A3 and display it in summary sheet cell B5, the O and A rows
always are the same so if O33 has a 1 A33 is always the company that relates
to that cell, I have seperate IF statements for each row at this point.

Now not all 100 companies will be "in progress" so to avoid a bunch of void
cells under each other I want to put all result in B5 (summary sheet) under
each other so its a clean flowing list

Does that make sense?

Thanks for your help in advance
Neall

--
Neall

You have replies to your previous posting. Please don't repost.

"Neall" wrote:

basically I have a summary sheet which I want to display data of
"current in progress works". On the other worksheet I have the a column that
tells me which company is in progress (100 companies) so what I want to do is;

on worksheet 2 read colum O3 if there is a 1 (meaning in progress) then take
the company name A3 and display it in summary sheet cell B5, the O and A rows
always are the same so if O33 has a 1 A33 is always the company that relates
to that cell, I have seperate IF statements for each row at this point.

Now not all 100 companies will be "in progress" so to avoid a bunch of void
cells under each other I want to put all result in B5 (summary sheet) under
each other so its a clean flowing list

Does that make sense?

Thanks for your help in advance
Neall

--
Neall

I was hoping to get a response to my more detailed explaination that was
requested.
--
Neall


"Toppers" wrote:

You have replies to your previous posting. Please don't repost.

"Neall" wrote:

basically I have a summary sheet which I want to display data of
"current in progress works". On the other worksheet I have the a column that
tells me which company is in progress (100 companies) so what I want to do is;

on worksheet 2 read colum O3 if there is a 1 (meaning in progress) then take
the company name A3 and display it in summary sheet cell B5, the O and A rows
always are the same so if O33 has a 1 A33 is always the company that relates
to that cell, I have seperate IF statements for each row at this point.

Now not all 100 companies will be "in progress" so to avoid a bunch of void
cells under each other I want to put all result in B5 (summary sheet) under
each other so its a clean flowing list

Does that make sense?

Thanks for your help in advance
Neall

--
Neall

Neal,

here is a macro that you can use to do what you want. Make sure you select
B5 in sheet1 (you could use any cell really.)

Public Sub InProgress()
' Adds all companies "In progress", at the selected cell.
Dim companyCol As New Collection
Dim progressRange As Range
Dim curCell As Range

' You might want to name the range O3:O102 and use that instead.
Set progressRange = Worksheets("Sheet2").Range("O3:O5")

' Collect all the companies in progress in a collection
For Each curCell In progressRange
' I'm assuming that column A will always contain the company name
' and column O the progress flag.
If curCell.Value = 1 Then
companyCol.Add (curCell.Offset(columnoffset:=-14).Value)
End If
Next

' Now display the collection in the current cell
Dim i As Integer
Dim targetCell As Range

' Selection is the active cell in sheet1, i.e. B5
Set targetCell = Selection
For i = 1 To companyCol.Count
targetCell.Value = companyCol(i)
Set targetCell = targetCell.Offset(rowoffset:=1)
Next
End Sub

To create the macro do the following: press alt+F11, Select Insert | Module
from the menu and copy and paste code below.

HTH.

Cheers,
Socratis

"Neall" wrote:

basically I have a summary sheet which I want to display data of
"current in progress works". On the other worksheet I have the a column that
tells me which company is in progress (100 companies) so what I want to do is;

on worksheet 2 read colum O3 if there is a 1 (meaning in progress) then take
the company name A3 and display it in summary sheet cell B5, the O and A rows
always are the same so if O33 has a 1 A33 is always the company that relates
to that cell, I have seperate IF statements for each row at this point.

Now not all 100 companies will be "in progress" so to avoid a bunch of void
cells under each other I want to put all result in B5 (summary sheet) under
each other so its a clean flowing list

Does that make sense?

Thanks for your help in advance
Neall

--
Neall