Grabbing data from one sheet and displaying on another?

Hi all,

I am seeking a method of collecting data from one sheet in a workbook and displaying it on another, based on a search.

e.g.

Sheet1
Garcia Joe 11111
Garcia Matt 22222
Hernandez Efrain 33333
Matias Jorge 44444

What I want to do is to have Sheet2 be a query for Sheet1--that is, in a cell in Sheet2, the user can type 'Garcia' into a cell and thus display the matching entries from Sheet1, e.g.

Sheet2
'Garcia' displays

Garcia Joe 11111
Garcia Matt 22222

I don't know if this can be done purely within Excel or if I'll need a VB macro to do it, any help would be greatly appreciated.

You can do it with a formula - here is one approach. If your data are in A1:A4 on sheet1 and search item in A1 in sheet2, then results are in A2 downwards of sheet2:

A2 down, entered with Ctrl+Shift+Enter
=IF(COUNTIF(Sheet1!$A$1:$A$4,Sheet2!$A$1&"*")<ROWS(A$2:A2),"",INDEX(Sheet1!$A$1:$A$4,SMALL(IF(ISNUMBER(FIND($A$1,Sheet1!A$1:A$4)),ROW(Sheet1!A$1:A$4)-ROW(Sheet1!A$1)+1),ROWS(A$2:A2))))

It will need slight tweaking if you wanted to search for strings contained within cells rather than at the beginning.

You could do it with VBA too.

Originally Posted by StephenR

You can do it with a formula - here is one approach. If your data are in A1:A4 on sheet1 and search item in A1 in sheet2, then results are in A2 downwards of sheet2:

A2 down, entered with Ctrl+Shift+Enter
=IF(COUNTIF(Sheet1!$A$1:$A$4,Sheet2!$A$1&"*")<ROWS(A$2:A2),"",INDEX(Sheet1!$A$1:$A$4,SMALL(IF(ISNUMBER(FIND($A$1,Sheet1!A$1:A$4)),ROW(Sheet1!A$1:A$4)-ROW(Sheet1!A$1)+1),ROWS(A$2:A2))))

It will need slight tweaking if you wanted to search for strings contained within cells rather than at the beginning.

You could do it with VBA too.

Thanks StephenR,

unfortunately I can't get this to work, even on my example data set above; I'm not sure what's going wrong.

In VBA, how could I do this? I think, with my limited programming knowledge, it might be as simple as defining an array, calling the Index worksheet function to populate the array, and then transposing the array into the cells I want the data to appear in.

But I am very unsure of methodology, if my logic is sound, and all that.

See attachment for an example of the formula. Note the curly brackets round the formula -they don't appear if you don't enter it correctly and that may be the problem.

Have also added a VBA approach, which uses the Find method - press the button.

Originally Posted by StephenR

See attachment for an example of the formula. Note the curly brackets round the formula -they don't appear if you don't enter it correctly and that may be the problem.

Have also added a VBA approach, which uses the Find method - press the button.

Hi, StephenR,

Thanks; the VBA approach looks like what I want. Here's another question for you--how can I alter the ranges and such to point towards the area of data I actually want it to reference?

In this case, the sheet with the data is Sheet4 (named within Excel as 'master_list_by_name') and the query sheet is Sheet2 (named Query), and the range of cells we want to search is A1 to A5000.

I tried making outright substitutions but it doesn't seem to be working---and my apologies, it's been busy as heck around the office and this is actually something of a side project.

Probably easiest if you attach a small sample of your data, but in the meantime try this:

Please Login or Register  to view this content.

Here's a small sample, including the query page--ideally the data should display from A12 downwards.

I'd recommend a pivot table.

im trying to do something similar.. i found this thread to be helpful


http://www.excelforum.com/excel-prog...directory.html

the only thing is it doesnt do partial searches..

Originally Posted by StephenR

Probably easiest if you attach a small sample of your data, but in the meantime try this:

Please Login or Register  to view this content.

Hi StephenR,

Hoping you're still willing to help me out here. I tinkered with the code as much as I was able and have managed a /few/ small successes but nothing remotely resembling what I want. (Given the difficulty I've had in accomplishing what sounds, on the surface, to be simple, I'm hardly surprised.)

Using my sheet, with the input in B9 and display to show from A12 downwards, I have managed to have the VBA script lay out from A12 down--however, it pastes only the last name, not the entire row, and it pasted it for approximately 2000 rows, the last time I tried it, rather than the number of rows corresponding to the number of entries for the name.

Here is the code as it looks now:

Please Login or Register  to view this content.

Originally Posted by StephenR

Yes, I'll have a look. I don't have Excel 2007 so wasn't able to look at your attachment. I can access it tomorrow, or post an Xl2003 file.

EDIT: does this work for you? (I'm not sure which is the barcode column.)

Please Login or Register  to view this content.

Wow! That works perfectly. Thank you very much, Stephen. I was just about going insane trying to figure this out.