Lookup and populate question:
 

Have numerous assemblies and wish to pull some labor data from another
sheet.

The source data sheet contains the assembly number, and several
"operation numbers" which contain a labor value (hours).

The operation number can be anywhere from 1 to 18.

Some assemblies can only have 1 operation, and others may have 12

The assembly line which is to be populated will look like this after
data is found:

Assembly: op 1 op 2 op 3 op 4 op 5 op 6 (etc. to
18)
12587-A 2 2.8 8 65


The source data which contains many assemblies would look like:

Assembly Operation Hours
12587-A 1 2
12587-A 3 2.8
12587-A 4 8
12587-A 6 65

All assemblies on the source data list might have completely different
hours and operation numbers, but only one "block" of data.

If a value on the populated data page is not found in the lookup, a
blank is returned

I thought of an array formula to find the grid in question on the
source data page, but the formulas construction is a head scratcher.

TIA for any thougths.
Pierre

Lookup and populate question:
 

Hi!

Try this:

This data in the range A2:C5

12587-A 1 2
12587-A 3 2.8
12587-A 4 8
12587-A 6 65


A10 = 12587-A
B10:S10 = 1,2,3,4,5....18

Formula in B11 copied across:

=IF(SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5)=0,"",SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5))

This assumes that there will be no duplicates. Such as:

12587-A 1 2
12587-A 1 2.8
12587-A 1 8
12587-A 6 65

Biff

"Pierre" wrote in message
oups.com...
Have numerous assemblies and wish to pull some labor data from another
sheet.

The source data sheet contains the assembly number, and several
"operation numbers" which contain a labor value (hours).

The operation number can be anywhere from 1 to 18.

Some assemblies can only have 1 operation, and others may have 12

The assembly line which is to be populated will look like this after
data is found:

Assembly: op 1 op 2 op 3 op 4 op 5 op 6 (etc. to
18)
12587-A 2 2.8 8 65


The source data which contains many assemblies would look like:

Assembly Operation Hours
12587-A 1 2
12587-A 3 2.8
12587-A 4 8
12587-A 6 65

All assemblies on the source data list might have completely different
hours and operation numbers, but only one "block" of data.

If a value on the populated data page is not found in the lookup, a
blank is returned

I thought of an array formula to find the grid in question on the
source data page, but the formulas construction is a head scratcher.

TIA for any thougths.
Pierre

Lookup and populate question:
 

Biff wrote:
Hi!

Try this:

This data in the range A2:C5

12587-A 1 2
12587-A 3 2.8
12587-A 4 8
12587-A 6 65


A10 = 12587-A
B10:S10 = 1,2,3,4,5....18

Formula in B11 copied across:

=IF(SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5)=0,"",SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5))

This assumes that there will be no duplicates. Such as:

12587-A 1 2
12587-A 1 2.8
12587-A 1 8
12587-A 6 65

Biff


Biff, you're a genius. Thank you!

Pierre

Lookup and populate question:
 

You're welcome. Thanks for the feedback!

Biff

"Pierre" wrote in message
ups.com...

Biff wrote:
Hi!

Try this:

This data in the range A2:C5

12587-A 1 2
12587-A 3 2.8
12587-A 4 8
12587-A 6 65


A10 = 12587-A
B10:S10 = 1,2,3,4,5....18

Formula in B11 copied across:

=IF(SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5)=0,"",SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5))

This assumes that there will be no duplicates. Such as:

12587-A 1 2
12587-A 1 2.8
12587-A 1 8
12587-A 6 65

Biff


Biff, you're a genius. Thank you!

Pierre