INDIRECT Question I think

[s boak]

Hi Folks:

I have a table that's 3 columns something like this

Model Qty Price
AB123 1 $ 10.00
AB123 100 $ 9.10
AB123 1000 $ 8.00
AB125 2500 $ 7.50
BA222 1 $ 6.00
BA222 100 $ 5.50


In most cases, this format is followed . . .
Sometimes however, there is only pricing for 1 pc, or 1 and 100, or
sometimes, 1000 and 2500

What I'd like to have is:

AB123 $10.00 $9.10 $8.00
AB125 $7.50
BA222 $ 6.00 $ 5.50

I'm guessing this might be an INDIRECT function, but I need help on
constructing the formula.

Many thanks.
Steve

 

[Dave O]

I got a result by setting up like this: headers Model, Qty, Price in
A1:C1; relevant data (the sample from your post) in B2:C7. I entered
numeric headers 1, 100, 1000, and 2500 in B10:E10 and representative
entries AB123, AB125, and BA222 in A11:A13. I entered this formula in
B11:
=SUMPRODUCT(--($A11=$A$2:$A$7),--(B$10=$B$2:$B$7),$C$2:$C$7)
.... which you can copy and paste to the other cells in the grid. You
can suppress the cells that return 0 with conditional formatting, if
desired.

 

[s boak]

Dave:
That is just way too cool . . . .
The sheet in 4,300 rows so I need to get all my qtys and model numbers, but
wow - - -
Many thanks
Steve

 

[Dave O]

Glad to help!

 

[Dave O]

Glad to help!