Retrieve rows in Excel 4

[newbiewonkinobie]

On sheet 2 there are 7 columns:
A1 is Job #
B1 is Client Name
C1 is Product Ordered
etc.
On Sheet 1, in cell A1 I want to enter a job #, then it will copy all rows with the matching job #'s from Sheet 2 to Sheet 1. Any suggestions?

Flores

 

[luceze]

Could you just use auto filter or advanced filter on sheet 2?

 

[newbiewonkinobie]

It needs to be as simple as possible for others to use, dumb it down if you will. Just enter a number in a cell without having to go to a menu or anything. Maybe this can be done by a vlookup and match or something... any other suggestions?

Flores

 

[verizonrep]

I do this all of the time. If you do it this way, it will work no matter how long your list gets on sheet 2.

Let's call sheet 1 "Orders" and sheet 2 "Reference". On the "Reference" sheet, you need to define a name.

First, make sure your field names start at cell A1 on the "Reference sheet", and that you only use one row for the field names. Your data needs to start on row A2.

Next, go to:
Insert
Name
Define

In the field located under "Names in Workbook" let's give it a name like "List"

In the field at the bottom (under "refers to, type copy and paste this equation:

=Offset('Reference'!$A$1,0,0,COUNTA('Reference'!$A:$A),7)

This formula makes it so the entire dataset can go on and on (up to excel's row limit). The "7" at the end indicates 7 columns (if you end up with 8 columns, change the 7 to 8).


On the "Orders" sheet (sheet 1), I am going to assume that your field names are on ROW 1, so I'll start putting the data on ROW 2. Have column A2 be the place where you put the job number. In B2, type the following:

=If(ISERROR(Vlookup(A2,List,2,False)),"Not Listed",Vlookup(A2,List,2,False))

That part after the word "List" (in this case the 2) says lookup cell A2, Refer to the "List" on the "Reference" sheet and return column 2. The ISERROR part basically makes it so that if you input a job order that doesn't exist on the "Reference" sheet, it will respond with "Not Listed"; therefore telling you that you need to go to the "Reference" sheet and add it.

Use the same equation for the other columns on the "Orders" Sheet, but for column C you would put a 3 instead of 2 in both part of the equation (the part after the word "List,". The same goes for the others (D=4,E=5,F=6,G=7).

Example - for column C

=If(ISERROR(Vlookup(A2,List,3,False)),"Not Listed",Vlookup(A2,List,3,False))

Once you have them all in place,highlight B2 through G2 (if you are using 7 columns), and drag all the way down the spreadsheet (as many rows as you need). Of course, don't drag column A since it will be the area you put the job number.

I know this seems detailed, and a bit long, but it is something that has worked for me very well. The best part is that you don't have to do any adjusting of your "Reference" sheet or other formulas. Once you set it up, you can go all the way to the 65,000+ lines that Excel has to offer.

Let me know how it worked.

 

[verizonrep]

I just saw your statement about others using this and not you exclusively. If you are not going to do a drag and drop for each one, you can pre-format all 65,000+ lines so that no information shows unless column A has something in it. Just add this before it:

=If(A2="","",equation)

So in the example I set, the equation would look something like:

=If(A2="","",If(ISERROR(Vlookup(A2,List,2,False)),"Not Listed",Vlookup(A2,List,2,False)))

 

[newbiewonkinobie]

Thanks for the detailed help Verizonrep. Your second version was much cleaner because the page wasn't filled up with "Not Listed." It worked as requested, except that I neglected to say that there sometimes are multiple entries on the same job #. As it works right now, it brings in the first entry with that job #. Is there any way to modify it to search "List" from the "Reference" page and retrieve all entries with that job # instead of retrieving just the first entry?

Flores

 

[verizonrep]

So let me see if I have this correct. On the first sheet (Orders), you want to put a job number in cell A2, and say that has 4 listings of name, product, etc., you want it to populate rows 2,3,4 and 5. Then, the user puts another job in cell A6, and there are 3 entries, so you want it to populate row 6,7,and 8.

Is this correct?

 

[newbiewonkinobie]

Yes, that is correct. Let's say that on the reference page, in cells A3, A44, and A78 there is job # 1426 (between these rows, there are different jobs). On the first sheet, when someone enters "1426", rows 2, 3, and 4 are populated with the A3, A44, and A78 from the 2nd sheet.

Flores

 

[verizonrep]

Are you using the "Orders" sheet (sheet 1) as a one time reference. In other words, are you simply putting in a number in cell A2 and seeing what the orders are and then the next time you need to look up all the job numbers, you input it to the same A2?

 

[DaleWatson123321]

Flores,

It seems your task is still unresolved.

I've therefore created an example file for you. It uses Excel's Advanced Filter and VBA to extract the data from Sheet2 to Sheet1.

After entering the Job # into cell A1 on Sheet1, all the user has to do is click a button. All the records for that Job # are then shown immediately below.

If you'd like the file, email me and I'll send the file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[newbiewonkinobie]

I would like to be able to check it whenever I want, but basically what I want it for is to put all of a jobs expenses on 1 sheet. A job may be open for 4 to 6 months, and whenever the job is closed, I would like to print only expenses from that job on the "order sheet" rather than the whole list of jobs that is on "reference".
The way that I have been doing it in the past is on the "reference" page, cell H1 I put in a job # to search and then all of the rows with that job # are highlighted. This is done by conditional formatting: Formula is =($A2=$H$1) I highlighted row A2 and put that condition in there, then I copied the rows down. This makes it easier to find costs relating to a job, but printing is a pain because there is a lot of copy-and-pasting to get it all on one sheet.

Flores

 

[DaleWatson123321]

Flores,

You'll be surprised by the POWER of the Advanced Filter component of Excel, but ONLY if you allow me to email you the file I've created and offered.

Perhaps you missed my posting (above). If you'd like the file, all you need to do is email me, and I'll then send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[verizonrep]

So in other words, you will basically put one job in a cell, get a list, and then print?

I want to make sure that you are only looking at doing one at a time.

Technically, this would be better suited for a database like Access, but there is a way to do it in Excel as long as you are looking up one job at a time. To do multiple jobs is a lot more challenging.

 

[KenWright]

What am I missing? From all the text that you have given so far, there is nothing I can see that would preclude the use of Autofilter (As suggested by Luceze earlier). This will list all rows with the Job Number that you choose from the dropdown. You can then print just what's left.

The only limitation that I can see with this option is that Autofilter is limited to 1000 unique entries in the dropdown, but that depends on how many job numbers you are likely to have.

The hardest part about this is having to click on an arrow and choose a number.

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[verizonrep]

Normally, yes; however, Flores gave the original project as requiring a two sheet solution. If that is the case, let's see what we can do.

 

[DaleWatson123321]

Ken,

With the "less than adequate" documentation by Microsoft on the use of Advanced Filter, perhaps we shouldn't be surprised at the response ;-)

For the benefit of others... The Advanced Filter is EXTREMELY capable of extracting data from one sheet to another. While the needs described here are VERY simple, the Advanced Filter capabilities are almost "endless" in terms of being able to use MULTIPLE fields and COMPLEX criteria (if requried) to extract to a SEPARATE sheet, a copy of the precise data required.

The separate sheet can be either pre-set for printing, or the VBA routine can also format the data and set a print range specific to the extracted data.

I SURE hope someone notices this.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[KenWright]

LOL - Preaching to the converted Dale!!! Agree 100%

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[newbiewonkinobie]

Thanks for the responses everyone. I was out for most of the day and didn't have a chance to respond. Dale, I sent you an email so you can send me the file. I tried the autofilter mentioned on a few earlier posts, but for whatever reason, it returned everything on the second page, rather than just the requested job #. I know it was more than likely user-error. I did mention MS Access because we are using a spreadsheet application to do a database function. Supposedly, it would be too expensive for several seats, and the learning curve would be too great for most users.

Flores

 

[KenWright]

Newbie, to put the data on another sheet, then use Advanced Filter as suggested by Dale, but to filter in place on the same sheet, thereby eliminating the need for a second sheet at all, simply use Autofilter on the sheet with all the data.

You must have headings on your columns, and then simply select all the data (Go way past it if you want a bigger range included), headings included, and do data / Filter / Autofilter. Then click on the dropdown arrow that will appear on the word Job_Number, and then click on the job number from the list provided. This should filter out every row except the ones with that job number.

For advanced filter, you might want to look at the following website, which will give you a nice intro:-

http://www.contextures.com/xladvfilter01.html

Depending on how sensitive your data is, by all means send me a copy if you like (or a desensitised copy), and I will put the filter on for you and make sure it works OK before returning it. ken.wright at ntlworld.com

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[newbiewonkinobie]

Thanks once again for all of the helpful replys that I have recieved and thanks for not making me feel like an idiot for being "Excel Challenged" to be politically correct Dale has sent me a file with VBA and advanced-filtering that seems to be exactly what I need. I can now enter a number into a cell, and on one sheet I will have all of the costs related to that job ready for printing. It's pretty much idiot-proof.

Flores