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



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

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

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


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:
And Debra's own site:

John Walkenbach also has some at:
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:

MS has some at (xl2000 and xl2002):

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question