Pasting into a filtered list

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


0
Mick
9/18/2003 12:43:41 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
973 Views

Similar Articles

[PageSpeed] 21

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


"Mick" <mick @msn.com> wrote in message
news:eca%23%23JefDHA.3700@TK2MSFTNGP11.phx.gbl...
> 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
>
>


0
9/18/2003 2:25:07 PM
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!!

"Mick" <mick @msn.com> wrote in message
news:eca%23%23JefDHA.3700@TK2MSFTNGP11.phx.gbl...
> 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
>
>


0
Mick
9/18/2003 4:04:36 PM
Reply:

Similar Artilces:

need to create a list box based on the selection of another list
I've done this in excel using =indirect in the list box. need to repeat in acces.. did have a response about a week ago, but cannot trace the link someone gave me. sorry to have to ask again, but it looked exatly what i wanted - had a new computer at work and they lost all my stored data... thanks what's your question ? -- Wayne Manchester, England. "Gover" wrote: > I've done this in excel using =indirect in the list box. need to repeat in > acces.. did have a response about a week ago, but cannot trace the link > someone gave me. sorry to have t...

Paste special treats cells as a picture
I just got a new computer, and a newer version of excel, which is great, but whenever I try to copy from one file to another, my paste special function treats the copied cells as a picture. Instead of the normal paste special popup, where it asks me how I want the data to come out, I get a different popup that shows the source as a "Microsoft Office Excel Worksheet Object." It asks me what format I want the picture to come out as, and gives me a list of different file formats. If I try to just paste a link, it puts a picture of the other file on the new one. I talked to so...

viewing lists vs. thumbnails
re mspub 2002. when pasting images in -- via click on insert pix, right click on insert -- how to not view resulting dialogue box as default thumbnails? conversion to list at toolbar at dialogue top seems to work only once, and then each time it's back to thumbnails. fooled with ms explorer option "remember view of previous folder view," but this had no effect on pub problem. Can you be a bit clearer? Are you trying to insert from the Organizer? The Organizer will always show thumbnails. You don't have to right-click to insert, simply drag and drop. -- Mary Saue...

How to hide a,list from the GAL
Hi I have a list setup in AD but do not what to show it in GAL. Where do i set this. Exch2003 and out2003 thanks You need to go into the properties of the group (the distribution list) in Active Directory Users and Computers. On the Exchange Advanced tab you will see a check box for "Hide users from Exchange address lists". Check that box and it will remove the DL from the GAL! -- Lee Owens MCSE, A+ Check out my blog: http://leeowens337.blogspot.com "MSExchange2003Student" wrote: > Hi > > I have a list setup in AD but do not what to show it in GAL....

Copy and paste formula; Value pastes instead
Excel 2002 I've tried starting several worksheets, checking formats, pasting special (formula only). For some reason the formula actually copies OK by looking at the formula bar, but the value in the cell is identical to the value of the copied cell.Incorrect. Tried even the simplest formulas with no joy. BK ...

[Help] Quick campaign for inactive member on a marketing list
Hi all, When I create quick campaign via mail merge to a marketing list, why do CRM also send email for inactive member of the marketing list. The email should sent only for active member, doesn't it? The marketing list also contain member without email address. Of course CRM didn't create email activity (quick campaign) for this member. But why these members didn't appear on the excluded customer? Make us hard to tracking and following up. Any body know what it might be wrong? Any help will be very apreciated Thank you It's been one week now? Any body know about thi...

Junk E-mail filter problems in Outlook 2003
The Junk E-mail Options - Safe Senders tab states "E-mail from addresses or domain names on your Safe Senders List will never be treated as junk e-mail. In that list I have (for example) nytdirect@nytimes.com which is the from address for a daily DealBook summary to which I have subscribed. Yet, in an unpredictable pattern, email from this address lands in my Junk E-mail folder. Each time, I click on the "Not Junk" toolbar button and am then informed in a "Mark as Not Junk" dialog box that "This message will be moved back into the Inbox Folder." I then...

Trouble filtering a report on a custom field
We have added some custom fields via the deployment manager and when we try to filter a case report (like case list) by them, we get this error: CrystalReportViewer Error in formula This field name is not known.. File a4c34756b9b331b8.rpt. I'm figuring its a straightforward configuration step we must have missed. Any ideas? Thanks, Justin jfischer[nospam]@coretrac.com Hi Justin, have you sync'd your crystal db with crm? Mike greaterthantwo media Printable Quotes, Orders and Invoices, with line-item details, now available for MSCRM. http://www.greaterthantwo.com "...

"Paste Special" with the right click mouse
I would like to have the option of "Paste Special" with the right click mouse in all microsoft office suite. Thanks, ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=de847738-df56-4...

Clipboard pasting
I copy something to the clipboard using Publisher 2007 and the computer tells me it is in the clipboard; however, when I go to paste that information into another document, Publisher acts like it is not there. I then need to close the document I wish to paste it into, reopen it, and then, like magic, there is the information in the clipboard and I am able to paste it. UGH!!!!!! -- Ginger Christenson Open the Office Clipboard in Publisher. Edit, Office Clipboard. What program are you pasting to? If it is an Office program, open the Office Clipboard in that program too. -- Mary Sauer ...

Outlook Junk Mail Filter not blocking blocked addresses
What are the possible causes for an Outlook 2003 installation failing to stop messages from addresses on its blocked addresses list? No messages on this installation are being stopped, including those from addresses blacklisted in Outlook junk mail options. Is there a setting somewhere that I don't know about that disables junk mail filtering? Are you using it as an Exchange server client in online (not cached) mode? -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Micro...

cascading pick lists?
Hi, Anyone know if it's possible to create a pick list attribute that has it's options limited by what is chosen in another attibute? e.g. Attribute Area, which has values A, B and C, and attribute Location, which has values 1, 2, 3, 4, 5, 6. Area A has location 1 & 2 Area B has locations 2 & 3 Area C has locations 4 & 5 In a form I want to be able to select Area A, then restrict the options available in Location to only 1 & 2. I'd be grateful for any ideas as to how this can be achieved, if possible. Many Thanks Answered in the developer newsgroup. -- ...

drop list dependant on other drop list selection
How do I make a drop list that is used with the cell-validation feature depend on the selection from the drop list/cell validation of a previously filled cell? Example: Cell A2 can be Hot, Warm, or Cold. If Hot is selected, cell A3 can be red, yellow or orange. If Warm is selected, cell A3 can be black, gray or white. If Cold is selected, cell A3 can be blue, green or purple. Individual lists may be up to 25 items long. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi for an examp...

Lookup Filter
I've seen similar questions posted here, but so far no answers - but hey, I'm hopeful someone will be able to assist! On the opportunity form, there is a field where you can do a lookup to either contact or account - in our company, however, we always want to populate the account in this field. Then, after users selected the account, we want them to be able to select a contact *associated with the chosen account*. So far we have been able to create a 'fake' lookup field that will allow them to select a contact - but we haven't been able to restrict it to only the ...

Pasting formulas advances cell reference
Hi When i paste special from one cell into another, it advances the cell reference and i just want an exact match i.e. A1 formula ='Feb 2010'!C18 but when i paste it into another cell it pastes it as ='Feb 2010'!D18 The C changes to D, how do i stop this happening? Thanks for any help in advance Derek Use absolute referencing. Below are the different reference styles. A1 Relative referencing. Both column and row will change if you copy or drag the formula. $A1 The column reference is fixed and will not change A$1 The row reference is fixed and wil...

Copy/Paste loop
I have a form on one sheet in excel, on the next sheet i have mad different coloums with different headings. I want the data from th form to be copyed to each heading e.g. name in form is copyed to unde the coloum with title name. So each time a user fills in there details he/she clicks submit an there data is copyed to the next page, like a small database. Is there a macro that can do this as i keep getting different error each time. I want this to continue in a loop Please help!!:confused -- Message posted from http://www.ExcelForum.com This might get you started: Option Explicit Opti...

Filtered Rows using Mode Function?
Hi Excel Forum, I am using numeric "filtered" data and I need to find the most frequen / re-occurring values (1st, 2nd, 3rd, 4th, 5th etc.) from the visibl filtered rows. The Mode function seemed likely, but I cannot get it t work with filtered rows. Can you assist with working examples, please: Formula based input direct on worksheet. VBA Macro using Formula. VBA UserDefined Function. Thank you QT -- Message posted from http://www.ExcelForum.com I would use one of 2 methods :- 1. Formula in another column and sort descending :- =COUNTIF($A$1:$A$20,A16) Need to sort out dupl...

Drop Down list in duplicate
How do I collapse duplicate records and place a drop down list with the values in the fields causing duplicate records in my spreadsheet? Huh?? Try that again with some examples of what you have and what you want to have. Don't attach a file to your post. Just explain it. HTH Otto "savior1" <savior1@discussions.microsoft.com> wrote in message news:B744A2FE-43FC-4F8A-85F7-B209BF1177DF@microsoft.com... > How do I collapse duplicate records and place a drop down list with the > values in the fields causing duplicate records in my spreadsheet? Row 1: Column ...

change font size in the Folder List in MS Outlook
change list of list in All Outlook Items in Folder List Pane Please see http://www.officeforlawyers.com/howask.htm -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "sjpollak" <sjpollak@discussions.microsoft.com> wrote in message news:676FF719-F5D4-4C61-B901-5138D042E535@microsoft.com... > change list of list in All Outlook Items in Folder List Pane ...

Filtering
Out of the box, is there a way to do message filtering based on text in the subject line with 2003 without using a third-party gateway or AV product? Thanks Not out of the box. >-----Original Message----- >Out of the box, is there a way to do message filtering >based on text in the subject line with 2003 without using >a third-party gateway or AV product? > >Thanks >. > ...

Auto-Populate based on Drop-down lists????
I have a table of data and have created a drop down list based on the first column. I would like to be able to choose an item from the list and then have the additional info I choose fill the next two columns. Is this possible? Here is a sample: Product Spec1 Spec2 Spec3 Price A 0.5 6 RED $2.00 B 0.5 4 BLUE $4.00 C 0.6 8 GREEN $6.00 D 0.7 3 BLACK $8.00 When I choose Product A from the drop down on the &q...

text box filter by form
For some reason that I just can't explain, a text box on a form associated with a single primary key field behaves unexpectedly when I do a filter-by-form. Instead of listing all of the records from the source table as choices in combo box mode, I only see "Is Null" and "Is Not Null" as choices. I have an older form from another database that behaves as I hoped this one would, yet there seems to be no property differences between the two text boxes. Can anybody explain what I'm missing please? Having "Is Null" and "Is Not Null" as filt...

When I copy a picture and paste it into Publisher it disappears
I have tried to copy a picture from my documents and paste it into Publisher and the picture disappears. What version of Publisher? What happens if you go to... Insert / Picture / From File? -- John Inzer "Cathy" <Cathy@discussions.microsoft.com> wrote in message news:EF2CA50C-78D9-47DB-8799-E9C3C9171038@microsoft.com... >I have tried to copy a picture from my documents and paste it into >Publisher > and the picture disappears. I am using Office Profession Edition 2003. I tried another picture and so far it is still there. Thanks. "John Inzer"...

Paste and Paste Special
What are the differences between paste and paste special and are these two functions the same for XP software from different sources? e.g Word, Publisher, Ventura and Word Perfect? -- _ _________________________________________ / \._._ |_ _ _ /' Orpheus Internet Services \_/| |_)| |(/_|_|_> / 'Internet for Everyone' _______ | ___________./ http://www.orpheusinternet.co.uk ...

Adding members to outlook distribution lists
I have to add members to distribution lists of outlook every one week programatically. I checked AddressEntry,AddressEntries,AddressList objects but no where I am finding methods or properties to add members to distribution lists. Only I can traverse through the members and display them. Is there any possibilty to add members to distribution list if it is possible tell me how to do it?? See the DistributionList.AddMember method. Very helpful is the object browser: press f2, then switch from <All Libraries> to Outlook. -- Best regards Michael Bauer - MVP Outlook ...