Vlookup - Need Help
 

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

Vlookup - Need Help
 

=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

Vlookup - Need Help
 

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

Vlookup - Need Help
 

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

Vlookup - Need Help
 

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

Vlookup - Need Help
 

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

Vlookup - Need Help
 

streetboarder Wrote:
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!!



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 "Not Started". 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-"Not Started" and
19 rows of "Not Started". 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 "+1" 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 "-1" at the end to return the proper row info.


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

Vlookup - Need Help
 

Got the summary working right!!!

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

Vlookup - Need Help
 

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 = "Not Started" (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: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=534553

Vlookup - Need Help
 

Maybe it would be better if you tried experimenting with
criteria/extract
just a suggestion

streetboarder Wrote:
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 = "Not Started" (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!


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