Gathering information from one worksheet to another

**jendew** · 06-19-2007, 04:10 PM

I have one worksheet that has various columns, one of them being a drop down field where you can pick Quoting;Ordered;Dropped;On-hold;Lost.
In the next worksheet I would like to grab all the jobs that have a Quoting status. I have tried a VLOOKUP but, it is only grabbing the word "Quoting" and not the rest of the information in that row. Is there a way to grab the entire row using a lookup for the word Quoting.
I hope someone can help. I am not sure if I explained this very well.

Thank you!!!!!

Jen

**NBVC** · 06-19-2007, 04:36 PM

Vlookup() is probably the correct function...did you check Excel Help for how it works?

You need to tell the function which column in the lookup table needs to be retrieved.

If you want us to try to put a formula together for you, please indicate exact location of your lookup table and which column contains the lookup value and where is your drop down list.

**jendew** · 06-20-2007, 09:05 AM

Thank you for your help!!!!!!!!!!!!!!! The lookup table I am needing information from is (sheet 2) Bookings Forecast, there is a range from A5:U147 that I need to pull the Quoting jobs from. The "Quoting" comes from the drop down list in Column B5:B147. I need to grab the rows that have Quoting in column B and have them show up on (sheet 3) Quote Backlog Report. I sure hope this makes sense, its easier to show someone than to tell someone! I appreciate your help immensly!

Jen

**NBVC** · 06-20-2007, 10:37 AM

Hi,

Actually if your trying to pull all rows that have a "Quoting" string in Column B, then Vlookup() is not really the way to go... instead try these steps.

In any available cell in Quote Backlog Report sheet, enter this formula:

=COUNTIF('Booking Report'!$B$5:$B$147,"Quoting")

Then in the first cell where you want to start retrieving info enter this formula:

=IF(ROWS($A$1:$A1)>$A$2,"",INDEX('Booking Report'!C$5:C$147,SMALL(IF('Booking Report'!$B$5:$B$147="Quoting",ROW('Booking Report'!C$5:C$147)-ROW($A$5)+1),ROWS($A$1:$A1))))

where $A$2 is the cell you put the Counti() formula in.

Note: After you enter the formula in the cell you must confirm it with CTRL+SHIFT+ENTER not only ENTER...You'll see {} brackets appear around the formula.

This will start retrieving info from column C of your Booking Report. You can now drag the formula as far right as needed to get all info and down as far as necessary to get all relevant rows. When you start seeing blanks, you have retrieved all relevant info.

If your ranges are different or your Sheet3 is named differently you will have to adjust those first and re-confirm with CSE key combo before copying over.

Let me know how it goes....if you can't get it to work, post your workbook here (zipped) and I will look at it.

Good Luck.

EDIT:

Added clarification of what $A$2 is in the formula.

**jendew** · 06-20-2007, 11:06 AM

Ok... I am officially stumped!!!!! I attached the document for you to reveiw, because, I just cannot figure it out! I THANK YOU SO MUCH FOR YOUR HELP!!! You are such a life saver!

**NBVC** · 06-20-2007, 11:37 AM

You almost had it...except that since you put the Countif() formula in A1, the other formula needed to reference Cell A1 in the first argument of the If() formula...

So now I fixed it.... See attached.

You only have 1 record currently showing Quoting...

If you change any other record to Quoting, you will see it appear in the backlog report sheet.