Auto Filter Like Results

[Joe]

I have two worksheets (W/S1 & W/S2) that work together
for different information. What I'm trying to accomplish
is create a formula that will take the value from cell H4
on W/S1 and evaluate the cells in column "X" on W/S2 and
if there's a MATCH (?) take the corresponding information
in the Part coulmn (column D) and enter it onto
worksheet1 into column I. I can use autofilter to get the
results I want by selecting 16 but then I have to copy
and paste the results back onto worksheet1. I'm trying to
automate this spreadsheet so others can use it and they
don't have to perform many manual steps. A sample of the
information I use is below.

Worksheet1 information
cell H4 = 16

Worksheet2 information
Column D X
Part Delivery
123 16
456 10
789 16
123 8
321 9
223 16
123 stk

Results to go back to Worksheet1 column I
I
Longet Delivery
123
789
223

I have tried =LOOKUP(LEFT(TRIM(H4),2),'Priced BOM Summary
Page'!S3:S82,'Priced BOM'!D382) and =MATCH(LEFT(TRIM
(H4),2),'Priced BOM'!S3:S82,0) but have errors. Can this
be done.

TIA
Joe

 

[Jim]

Whew! Another example of a great spreadsheet design. I've already had 2 cups
of coffee and I still can't follow along!

 

[Joe]

Too confusing huh.

All I'm trying to do is take the value in one cell on the
first spreadsheet and find the matching rows on the other
spreadsheet and then write the part number back onto
sheet 1. I still don't know if this helps.

Joe

 

[Peo Sjoblom]

One way

=INDEX($D$1:$D$8,SMALL(IF($X$2:$X$8=$H$4,ROW($X$2:$X$8)),ROW(1:1)))

entered with ctrl + shift & enter, you have to add the worksheet names
yourself

copy down as many rows as you want, there will be many #NUM errors but you
can
use conditional formatting (assume you have this in L1:L20, select that
range with L1 as the active cell,
do format>conditional formatting and formula is, =ISERROR(L1), click format
button and select
white fonts, click OK twice) Or you can replace the error

=IF(ISERR(SMALL(IF($X$2:$X$8=$H$4,ROW($X$2:$X$8)),ROW(1:1))),"",INDEX($D$1:$
D$8,SMALL(IF($X$2:$X$8=$H$4,ROW($X$2:$X$8)),ROW(1:1))))

Finally to make the formula withstand an insertion of rows at the top

=IF(ISERR(SMALL(IF($X$3:$X$9=$H$5,ROW($X$3:$X$9)),ROW()-ROW($L$3)+1)),"",IND
EX(INDIRECT("$D$1:$D$8"),SMALL(IF($X$3:$X$9=$H$5,ROW($X$3:$X$9)),ROW()-ROW($
L$3)+1)))

 

[Peo Sjoblom]

Sorry the last formula should be

=IF(ISERR(SMALL(IF($X$2:$X$8=$H$4,ROW($X$2:$X$8)),ROW()-ROW($L$2)+1)),"",IND
EX(INDIRECT("$D$1:$D$8"),SMALL(IF($X$2:$X$8=$H$4,ROW($X$2:$X$8)),ROW()-ROW($
L$2)+1)))

I tested by inserting a row at the top and then I copied the formula so be
consistent with the earlier formulas
the above should be it.. You might considering using autofilter and a macro
to do this..

 

[Peo Sjoblom]

Sorry, another change, change the index part to a large number

=IF(ISERR(SMALL(IF($X$2:$X$8=$H$4,ROW($X$2:$X$8)),ROW()-ROW($L$2)+1)),"",IND
EX(INDIRECT("$D$1:$D$100"),SMALL(IF($X$2:$X$8=$H$4,ROW($X$2:$X$8)),ROW()-ROW
($L$2)+1)))

that way you should be ok with quite a lot of inserted rows at the topp. If
you never ever going to insert rows you can go for the first formula

=IF(ISERR(SMALL(IF($X$2:$X$8=$H$4,ROW($X$2:$X$8)),ROW(1:1))),"",INDEX($D$1:$
D$8,SMALL(IF($X$2:$X$8=$H$4,ROW($X$2:$X$8)),ROW(1:1))))

 

[Joe]

I must be doing something wrong. I copied the formulas
and I get different answers. I added the worksheet names
and changed the cell references to the ones on the
spreadsheets and this is what I have:

=INDEX('Priced BOM'!$D$3:$D$100,SMALL(IF('Priced BOM'!
$U$3:$U$100=$H$4,ROW('Priced BOM'!$U$3:$U$100)),ROW
('Priced BOM'!1:1)))

=IF(ISERR(SMALL(IF('Priced BOM'!$U$3:$U$100=$H$4,ROW
('Priced BOM'!$U$3:$U$100)),ROW(1:1))),"",INDEX('Priced
BOM'!$D$3:$D$100,SMALL(IF('Priced BOM'!
$U$3:$U$100=$H$4,ROW('Priced BOM'!$U$3:$U$100)),ROW
(1:1))))

=IF(ISERR(SMALL(IF('Priced BOM'!$U$3:$U$100=$H$4,ROW
('Priced BOM'!$U$3:$U$100)),ROW()-ROW($L$3)+1)),"",INDEX
(INDIRECT("'Priced BOM'!$D$3:$D$100"),SMALL(IF('Priced
BOM'!$U$3:$U$100=$H$4,ROW('Priced BOM'!$U$3:$U$100)),ROW
()-ROW($L$3)+1)))

In the last formula what is L3 for? Is this where the
formula writes the answer?

I guess I'm still not understanding Excel enough.

Thank you for your help with this problem and all the
others

Joe

 

[Peo Sjoblom]

I can email you a sample workbook using your sheet names. The L3 is the cell
where the formula is in..
Do you want me to email you a workbook?

 

[Peo Sjoblom]

Assuming you put this formula in I4 and that the lookup value is in H4, the
delivery column is in U
and the part numbers in D

=IF(ISERR(SMALL(IF('Priced BOM'!$U$3:$U$100=$H$4,ROW('Priced
BOM'!$U$3:$U$9)),ROW()-ROW($I$4)+1)),"",INDEX(INDIRECT("'Priced
BOM'!$D$1:$D$100"),SMALL(IF('Priced BOM'!$U$3:$U$100=$H$4,ROW('Priced
BOM'!$U$3:$U$100)),ROW()-ROW($I$4)+1)))

entered with ctrl + shift & enter

I made a little sample that I can email you upon request

 

[Joe]

An email would be great, my email is (e-mail address removed).
I could also email my spreadsheet to you if that would
help.
Thank you again for all your help with this one.

Joe

 

[Peo Sjoblom]

On its way

--

Regards,

Peo Sjoblom

An email would be great, my email is (e-mail address removed).
I could also email my spreadsheet to you if that would
help.
Thank you again for all your help with this one.

Joe