Vlookup - Need Help

  • Thread starter Thread starter streetboarder
  • Start date Start date
S

streetboarder

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

Ok. Still not getting it so hang in there with me. Let's try the easie
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 "projec
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 abl
to copy this formula down so it pulls all of the projects manager nam
by "not started".

The next cell to the right (Overview!B5:B100) needs to pull th
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 hel
 
=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
 
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!
 
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
 
Thank you both!!!!

Morrigan that formula is awesome. Thank you for taking the time t
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 hav
three different sections all with information now that need to b
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. Usin
UFO's VBA code I can delete rows referencing (#NUM!), sort and total a
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 o
(Summary!A15, A16, etc.

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

I have taken a look at your formula to see if "Row 5" on the overvie
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 wor
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 said:
Thank you both!!!!

Morrigan that formula is awesome. Thank you for taking the time t
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 hav
three different sections all with information now that need to b
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. Usin
UFO's VBA code I can delete rows referencing (#NUM!), sort and total a
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 o
(Summary!A15, A16, etc.

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

I have taken a look at your formula to see if "Row 5" on the overvie
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 wor
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!!
Click to expand...



Not sure what to tell you without looking at your sheet. Anyway, th
idea is to use SUMPRODUCT(SMALL(ROW())) to sort and to return the ro
number where column AD contains "Not Started". The following is wha
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" an
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 th
end. Now as you drag the formula down, row 6 will return the 70t
element, row 7 will return the 71th element and so on.

Since I used OFFSET() not INDEX(), and SUMPRODUCT() returns the ro
number, you need to put "-1" at the end to return the proper row info
 
Morrigan,
using the formula you provided would it be possible to change it a bi
to find the top 20 projects that are not started and pull thi
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 than
you enough.

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