vlookup subtotals value
 

Here is my problem:

I have sheet #1 contains part no. A with on hand qty 10 pcs= book qty.
col 1 col. 2
part no. book qty
A 10


Now we are doing physical count - I have a new sheet #2 = it subtotal part
no. A (it has 3 count tags on this part) with subtotal qty 25pcs
The result subtotal shown on sheet#2:
part no. tag qty
A 1 20
A 2 3
A 3 2
A Total 25 = this is a subtotal line



I would like to use sheet #1 and vlookup phy qty associate part no. A data
from sheet #2:

col. 1 col. 2 col. 3
part no. book qty physical qty
A 10 25 - this data from vlookup on
sheet #2 subtoal value.

However, it always shown col. 3 - #N/A, it won't lookup subtotal qty from
sheet #2
part no. A Total.

Please reply.

Thank you,
BB

vlookup subtotals value
 

Hi!

Are you using DataSubtotals ?

Then lookup "A Total" :

=VLOOKUP("A Total",A1:B16,3,0)

Or, even better:

=SUMIF(A2:A16,"A",C2:C16)

Biff

"BB" wrote in message
...
Here is my problem:

I have sheet #1 contains part no. A with on hand qty 10 pcs= book qty.
col 1 col. 2
part no. book qty
A 10


Now we are doing physical count - I have a new sheet #2 = it subtotal
part
no. A (it has 3 count tags on this part) with subtotal qty 25pcs
The result subtotal shown on sheet#2:
part no. tag qty
A 1 20
A 2 3
A 3 2
A Total 25 = this is a subtotal line



I would like to use sheet #1 and vlookup phy qty associate part no. A data
from sheet #2:

col. 1 col. 2 col. 3
part no. book qty physical qty
A 10 25 - this data from vlookup on
sheet #2 subtoal value.

However, it always shown col. 3 - #N/A, it won't lookup subtotal qty from
sheet #2
part no. A Total.

Please reply.

Thank you,
BB

vlookup subtotals value
 

Typo:

=VLOOKUP("A Total",A1:B16,3,0)

Should be:

=VLOOKUP("A Total",A1:C16,3,0)

Biff

"Biff" wrote in message
...
Hi!

Are you using DataSubtotals ?

Then lookup "A Total" :

=VLOOKUP("A Total",A1:B16,3,0)

Or, even better:

=SUMIF(A2:A16,"A",C2:C16)

Biff

"BB" wrote in message
...
Here is my problem:

I have sheet #1 contains part no. A with on hand qty 10 pcs= book qty.
col 1 col. 2
part no. book qty
A 10


Now we are doing physical count - I have a new sheet #2 = it subtotal
part
no. A (it has 3 count tags on this part) with subtotal qty 25pcs
The result subtotal shown on sheet#2:
part no. tag qty
A 1 20
A 2 3
A 3 2
A Total 25 = this is a subtotal line



I would like to use sheet #1 and vlookup phy qty associate part no. A
data
from sheet #2:

col. 1 col. 2 col. 3
part no. book qty physical qty
A 10 25 - this data from vlookup on
sheet #2 subtoal value.

However, it always shown col. 3 - #N/A, it won't lookup subtotal qty from
sheet #2
part no. A Total.

Please reply.

Thank you,
BB