copy subtotal value only, subtotal value can be vlookup by others

[Guest]

I did copy subtotal value only by edit/go to/ special/visiable cell to the
sheet A. It shown part no. 31611 Total - qty 114. Then I did "find" replace
"Total" with blank field, so I can get part no. only. => part no. 31611 , qty
114 - they both on 2 different column.

Now I can't Vlookup these 2 column data. I need vlookup qty column. It
shown "#N/A" when I loopup qty column. Is this text / number issue? How can
loopup value from subtotal result?

 

[Dave Peterson]

Maybe you should change " total" instead (with leading spaces).

And if you enter your part numbers as text (preformat the cell as Text or enter
with a leading apostrophe), then the edit|replace will change the Text values to
numbers.

(You'll see that the leading 0's are lost after you do that edit|Replace.)

You can change your =vlookup() to something like:

=vlookup(--a2,sheet2!a:b,2,false)

The -- coerces text numbers to number numbers so that it'll match the values in
the first column of the lookup table.

But you may want to invest a bit of time with data|pivottable. You'll find that
you'll be able to build that table a bit easier (after the learning curve) and
the text numbers will be kept as text numbers (no need to get rid of the "total
" stuff.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx