sorry if this is a double post but I didn't get a response from my
previous post and I need to finish this workbook up by the end of the
week.
----

Ok. Still not getting it so hang in there with me. Let's try the easier
one first.

I am trying to match the following text in cells (RawData!AD14:AD100)
text = "Not Started" If there is a match, I need to pull the "project
managers name" located on the same sheet in cells (RawData!D14:D100)

This information needs to go to (Overview!A5:A100). I need to be able
to copy this formula down so it pulls all of the projects manager name
by "not started".

The next cell to the right (Overview!B5:B100) needs to pull the
information for priority number for the same projects "not started"
(RawData!AD14:AD100) pull the priority number from (RawData!A14:A100).

So in the end I will have "Projects Not Started by Manager".

Thanks for your help


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=534553

=IF(RawData!AD14="Not Started",RawData!D14,"") <====== in Overview cell A1
=IF(RawData!AD14="Not Started",RawData!A14,"") <====== in Overview cell B1

And copy down
HTH



"streetboarder" wrote:


sorry if this is a double post but I didn't get a response from my
previous post and I need to finish this workbook up by the end of the
week.
----

Ok. Still not getting it so hang in there with me. Let's try the easier
one first.

I am trying to match the following text in cells (RawData!AD14:AD100)
text = "Not Started" If there is a match, I need to pull the "project
managers name" located on the same sheet in cells (RawData!D14:D100)

This information needs to go to (Overview!A5:A100). I need to be able
to copy this formula down so it pulls all of the projects manager name
by "not started".

The next cell to the right (Overview!B5:B100) needs to pull the
information for priority number for the same projects "not started"
(RawData!AD14:AD100) pull the priority number from (RawData!A14:A100).

So in the end I will have "Projects Not Started by Manager".

Thanks for your help


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=534553

It works but needs help...

It is leaving blank rows were their is no reference to "not started"
for that specific row. For example I have blanks showing in Rows
5-8,12-15, etc.

What I need to do is search the column (AD14:AD100) for the first cell
containing "not started" and pull the reference of "project manager" to
Overview!A5, then search the same column (AD14:AD100) find the next cell
containing "not started" and pull the next reference to Overview!A6,
etc

I think this can be accomplished a few ways...one way I know would be
to write a macro to delete the blank rows or via a formula. Is this
possible via a formula?

So what I have now is this:
Row 5 - Blank
Row 6 - Blank
Row 7 - Blank
Row 8 - Manager
Row 9 - Blank

What I need would be -
Row 5 - Manager (not started project)
Row 6 - Manager (next not started project)
Row 7 - Manager (next not started project, ect)


Thanks!


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=534553

Here is one to delete empty rows.
Below it is one that HIDES empty rows ( formula will stay in place that way)

Sub DelEmptyRows()
Dim j As Long
Dim i As Long
Dim r As Range

j = 65536
For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
If r Is Nothing Then
Set r = Rows(i)
Else
Set r = Union(r, Rows(i))
End If
End If
Next i

If Not r Is Nothing Then
r.Delete
End If
End Sub
_____________________________
Sub HideEmptyRows()
Dim cell As Range
For Each cell In Range( _
Cells(1, "A"), _
Cells(Rows.Count, "A").End(xlUp) _
)
If cell.Value = "" Then _
cell.EntireRow.Hidden = True
Next cell
End Sub


"streetboarder" wrote:


It works but needs help...

It is leaving blank rows were their is no reference to "not started"
for that specific row. For example I have blanks showing in Rows
5-8,12-15, etc.

What I need to do is search the column (AD14:AD100) for the first cell
containing "not started" and pull the reference of "project manager" to
Overview!A5, then search the same column (AD14:AD100) find the next cell
containing "not started" and pull the next reference to Overview!A6,
etc

I think this can be accomplished a few ways...one way I know would be
to write a macro to delete the blank rows or via a formula. Is this
possible via a formula?

So what I have now is this:
Row 5 - Blank
Row 6 - Blank
Row 7 - Blank
Row 8 - Manager
Row 9 - Blank

What I need would be -
Row 5 - Manager (not started project)
Row 6 - Manager (next not started project)
Row 7 - Manager (next not started project, ect)


Thanks!


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=534553

See attachment, hope it helps.


+-------------------------------------------------------------------+
|Filename: Vlookup.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4664 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=534553

Thank you both!!!!

Morrigan that formula is awesome. Thank you for taking the time to
create a file for me so I can see exactly what is going on.

I have the overview sheet working perfectly with your formula. I have
three different sections all with information now that need to be
sorted. 1st by "Manager", 2nd by "Project # and 3rd by "Project name".


I don't believe you can do the alpha sort through a formula. Using
UFO's VBA code I can delete rows referencing (#NUM!), sort and total as
needed. PERFECT!

I know am moving on to the second phase of this which is the "Summary"
page of "Active" projects. This time the data needs to start on
(Summary!A15, A16, etc.

I have looked at your formula changed "Not Started" to "Active" of
course and everything is working correctly except i am not getting a
full list of my active projects.

I have taken a look at your formula to see if "Row 5" on the overview
sheet was referenced and the only part I can find is the +1))-1 which,
when changed seems to effect my results but I can't get it to work
correctly.

Maybe I am missing something...

Project Status (RawData!AD15:AD100)
Project Number (RawData!A15:A100)
Summary Page (Summary!A15) - Results in "Project number"

Thanks again!!


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=534553