software

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 = quot;Not Startedquot; If there is a match, I need to pull the quot;project
managers namequot; located on the same sheet in cells (RawData!D14100)

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 quot;not startedquot;.

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

So in the end I will have quot;Projects Not Started by Managerquot;.

Thanks for your help--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
View this thread: www.excelforum.com/showthread...hreadid=534553=IF(RawData!AD14=quot;Not Startedquot;,RawData!D14,quot;quot;) lt;====== in Overview cell A1
=IF(RawData!AD14=quot;Not Startedquot;,RawData!A14,quot;quot;) lt;====== in Overview cell B1

And copy down
HTH
quot;streetboarderquot; wrote:

gt;
gt; sorry if this is a double post but I didn't get a response from my
gt; previous post and I need to finish this workbook up by the end of the
gt; week.
gt; ----
gt;
gt; Ok. Still not getting it so hang in there with me. Let's try the easier
gt; one first.
gt;
gt; I am trying to match the following text in cells (RawData!AD14:AD100)
gt; text = quot;Not Startedquot; If there is a match, I need to pull the quot;project
gt; managers namequot; located on the same sheet in cells (RawData!D14100)
gt;
gt; This information needs to go to (Overview!A5:A100). I need to be able
gt; to copy this formula down so it pulls all of the projects manager name
gt; by quot;not startedquot;.
gt;
gt; The next cell to the right (Overview!B5:B100) needs to pull the
gt; information for priority number for the same projects quot;not startedquot;
gt; (RawData!AD14:AD100) pull the priority number from (RawData!A14:A100).
gt;
gt; So in the end I will have quot;Projects Not Started by Managerquot;.
gt;
gt; Thanks for your help
gt;
gt;
gt; --
gt; streetboarder
gt; ------------------------------------------------------------------------
gt; streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
gt; View this thread: www.excelforum.com/showthread...hreadid=534553
gt;
gt;


It works but needs help...

It is leaving blank rows were their is no reference to quot;not startedquot;
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 quot;not startedquot; and pull the reference of quot;project managerquot; to
Overview!A5, then search the same column (AD14:AD100) find the next cell
containing quot;not startedquot; 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: www.excelforum.com/member.php...oamp;userid=30707
View this thread: www.excelforum.com/showthread...hreadid=534553Here 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, quot;Aquot;), _
Cells(Rows.Count, quot;Aquot;).End(xlUp) _
)
If cell.Value = quot;quot; Then _
cell.EntireRow.Hidden = True
Next cell
End Subquot;streetboarderquot; wrote:

gt;
gt; It works but needs help...
gt;
gt; It is leaving blank rows were their is no reference to quot;not startedquot;
gt; for that specific row. For example I have blanks showing in Rows
gt; 5-8,12-15, etc.
gt;
gt; What I need to do is search the column (AD14:AD100) for the first cell
gt; containing quot;not startedquot; and pull the reference of quot;project managerquot; to
gt; Overview!A5, then search the same column (AD14:AD100) find the next cell
gt; containing quot;not startedquot; and pull the next reference to Overview!A6,
gt; etc
gt;
gt; I think this can be accomplished a few ways...one way I know would be
gt; to write a macro to delete the blank rows or via a formula. Is this
gt; possible via a formula?
gt;
gt; So what I have now is this:
gt; Row 5 - Blank
gt; Row 6 - Blank
gt; Row 7 - Blank
gt; Row 8 - Manager
gt; Row 9 - Blank
gt;
gt; What I need would be -
gt; Row 5 - Manager (not started project)
gt; Row 6 - Manager (next not started project)
gt; Row 7 - Manager (next not started project, ect)
gt;
gt;
gt; Thanks!
gt;
gt;
gt; --
gt; streetboarder
gt; ------------------------------------------------------------------------
gt; streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
gt; View this thread: www.excelforum.com/showthread...hreadid=534553
gt;
gt;


See attachment, hope it helps. -------------------------------------------------------------------
|Filename: Vlookup.zip |
|Download: www.excelforum.com/attachment.php?postid=4664 |
-------------------------------------------------------------------

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: www.excelforum.com/member.php...foamp;userid=7094
View this thread: 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 quot;Managerquot;, 2nd by quot;Project # and 3rd by quot;Project namequot;.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 quot;Summaryquot;
page of quot;Activequot; projects. This time the data needs to start on
(Summary!A15, A16, etc.

I have looked at your formula changed quot;Not Startedquot; to quot;Activequot; 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 quot;Row 5quot; 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 quot;Project numberquot;

Thanks again!!--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
View this thread: www.excelforum.com/showthread...hreadid=534553
streetboarder Wrote:
gt; Thank you both!!!!
gt;
gt; Morrigan that formula is awesome. Thank you for taking the time to
gt; create a file for me so I can see exactly what is going on.
gt;
gt; I have the overview sheet working perfectly with your formula. I have
gt; three different sections all with information now that need to be
gt; sorted. 1st by quot;Managerquot;, 2nd by quot;Project # and 3rd by quot;Project namequot;.
gt;
gt;
gt; I don't believe you can do the alpha sort through a formula. Using
gt; UFO's VBA code I can delete rows referencing (#NUM!), sort and total as
gt; needed. PERFECT!
gt;
gt; I know am moving on to the second phase of this which is the quot;Summaryquot;
gt; page of quot;Activequot; projects. This time the data needs to start on
gt; (Summary!A15, A16, etc.
gt;
gt; I have looked at your formula changed quot;Not Startedquot; to quot;Activequot; of
gt; course and everything is working correctly except i am not getting a
gt; full list of my active projects.
gt;
gt; I have taken a look at your formula to see if quot;Row 5quot; on the overview
gt; sheet was referenced and the only part I can find is the 1))-1 which,
gt; when changed seems to effect my results but I can't get it to work
gt; correctly.
gt;
gt; Maybe I am missing something...
gt;
gt; Project Status (RawData!AD15:AD100)
gt; Project Number (RawData!A15:A100)
gt; Summary Page (Summary!A15) - Results in quot;Project numberquot;
gt;
gt; Thanks again!!
Not sure what to tell you without looking at your sheet. Anyway, the
idea is to use SUMPRODUCT(SMALL(ROW())) to sort and to return the row
number where column AD contains quot;Not Startedquot;. The following is what
the row-number-array looks like:

{0, 0, 0,...(68 of them)..0, 0, 14, 15, 16, 20, 21, 22, 25, 27, 36, 38,
39, 50, 51, 52, 53, 54, 55, 61, 100}

From the example I attached, there are 68 rows of non-quot;Not Startedquot; and
19 rows of quot;Not Startedquot;. In row 5 of the sheet Overview, SUMPRODUCT()
returns the 68th element in the array which is the last 0 in the array.
However, what you want is the 69th element. Thus, you add quot; 1quot; at the
end. Now as you drag the formula down, row 6 will return the 70th
element, row 7 will return the 71th element and so on.

Since I used OFFSET() not INDEX(), and SUMPRODUCT() returns the row
number, you need to put quot;-1quot; at the end to return the proper row info.--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: www.excelforum.com/member.php...foamp;userid=7094
View this thread: www.excelforum.com/showthread...hreadid=534553
Got the summary working right!!!

Thanks again!--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
View this thread: www.excelforum.com/showthread...hreadid=534553
Morrigan,
using the formula you provided would it be possible to change it a bit
to find the top 20 projects that are not started and pull this
information to the summary page?

Project Numbers 1-20 (z_data!$A15:A100)
Project Status = quot;Not Startedquot; (z_data!$AD15:$AD100)

Summary Page = (Summary!A35)

I have been making a lot of progress with this report and can't thank
you enough.

Thanks!--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: www.excelforum.com/member.php...oamp;userid=30707
View this thread: www.excelforum.com/showthread...hreadid=534553
Maybe it would be better if you tried experimenting with
criteria/extract
just a suggestion

streetboarder Wrote:
gt; Morrigan,
gt; using the formula you provided would it be possible to change it a bit
gt; to find the top 20 projects that are not started and pull this
gt; information to the summary page?
gt;
gt; Project Numbers 1-20 (z_data!$A15:A100)
gt; Project Status = quot;Not Startedquot; (z_data!$AD15:$AD100)
gt;
gt; Summary Page = (Summary!A35)
gt;
gt; I have been making a lot of progress with this report and can't thank
gt; you enough.
gt;
gt; Thanks!--
FireWater
------------------------------------------------------------------------
FireWater's Profile: www.excelforum.com/member.php...oamp;userid=33846
View this thread: www.excelforum.com/showthread...hreadid=534553