Pasting into a filtered list

  • Thread starter Thread starter Mick
  • Start date Start date
M

Mick

Can anyone tell me if it is possible to paste a list of price updates into a
filtered list. - I have filtered the list to show just the products whose
prices need to be updated, when I try to paste the updated prices, into the
list, the prices are not just pasted into the visible cells but the hidden
cells between. Can I paste into the visible cells only or do I have to
update each line manually. I am using Excel 2002
Any help would be much appreciated
 
Can you sort the list to bring the products that need updating together in
the list? If not, try something like this:

Your list of products and prices:

A B
1 ProdID Price
2 1005 10.00
3 1002 20.00
4 1001 15.00


The list of new prices:

D E
1 ProdID New Price
2 1002 21.00
3 1001 17.00


Copy the list of ProdIDs in column A to column G:

G H
1 ProductID Price
2 1005
3 1002
4 1001

Put this formula in H2 and copy down:

=IF(ISNA(VLOOKUP(G2,D:E,2,FALSE)),VLOOKUP(G2,A:B,2,FALSE),VLOOKUP(G2,D:E,2,F
ALSE))

You can then Copy and Paste Special>Values to remove the formula and retain
the values.

HTH

-Dave
 
Thanks for your suggestions Dave, I hadn't thought of using the "sort"
feature.- I have now filtered the list with the products that need to be
updated using advanced filter, inserted a column to the left of the data,
put a "X" in the column next to the filtered data, unfiltered the list and
sorted the list by the newly added column. Now all the data to be updated is
grouped together at the top of the list making it simple to paste the new
prices in. Thanks a million!!
 
Back
Top