Exclude matches in a list

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - problem importing xmlss - Worsheet Setting problem during load trying to open an xmlss and I get a message problem during load and in the text box it says: Worksheet Setting I sess the errors are listed in a file c:\documents and settings\xxx\local settings\temporary internet files\content.MSO\580887f9/log but I can't find that file. How can I determine what's wrong with my xml file? Thanks in advance Tag: Exclude matches in a list Tag: 138990
  - 2
    - Date Comparison Formula Looks like I need some help, please: I have a column (A1:A12) of dates representing the twelve months of a company year (e.g. A1 = April 2007 to A12 = March 2008). In the second column (B1:B12) is a 'cumulative amount due' currency figure representing the sum to date of the monthly expenses claimed by an employee over the company year. The third column (C1:C20) contains the various dates that expenses were reimbursed to the employee. The fourth column (D1-D20) contains a record of the expense amounts reimbursed to the employee over the year. These reimbursements are random and not necessarily on a monthly basis. There might be more than one reimbursement in a month, or there might not be any. The number of reimbursements could exceed twelve in the year, hence using D1:D20 for this column. In the fifth column (E1:E20) I want to show the 'balance of expenses due' (or overpaid) to the employee at the date the reimbursement was made. What I would like to do is calculate the 'balance of expenses due' based on the date that the reimbursement was made (C1:C20), using the 'cumulative amount due' figure (B1:B12) for the same year & month. Example: If a reimbursement was made on 18 June 2007 (assume reimbursement date entered in cell C4 and reimbursement amount entered in cell D4) , then the amount reimbursed to date (sum cells D1:D4) should be subtracted from the 'cumulative amount due' figure in column B1:B12 that corresponds to June 2007 (June 2007 would be in cell A3 and the corresponding 'cumulative amount due' would be in cell B3). Therefore the sum would be (B3-SUM(D1:D4)). The answer should appear in cell E4 How easy is it to compare the year and month of the reimbursement date with the dates in column A1:A12, find the year/month match and use the corresponding 'cumulative amount due' in the calculation? Is this a task for VLOOKUP? I've tried VLOOPUP, but keep getting a #REF! error. Thanks -- Mike -Please remove 'safetycatch' from email address before firing off your reply- Tag: Exclude matches in a list Tag: 138983
  - 3
    - VBA code needed to AUTOMATE SELECTION of names within VALIDATION I have searched the blogs and cannot seem to locate anything related to my challenge. I know you guys are gurus when It comes to things like this. I have a worksheet that tracks employee productivity for each cost center with a region. My excel worksheet contains three validation dropdowns that use the "indirect" function with various named ranges that link these dropdowns . So for instance lets say that the first dropdown relates to a region, the second relates to a cost center (there can be many cost centers in a region), and the third is a worker in that cost center (there can be many workers in a cost center). So, lets say that I select Northwest Region =E0 10001 (Cost Center) =E0 and Jim Stu, John Doe, Bob Doe, and Jane Done can all be selected from this cost center individually which changes the productivity data for each worker. Drop Down 1 (region) --> Drop Down 2 (cost center) -> Drop Down 3 (Worker) Range1 =3D Region Range2 =3D (indirect function from cell in Range 1) Range3 =3D (indirect function from cell in Range 2) What I would like to do is automate the printing process of the workers within a selected cost center using these already defined ranges. So for example in the example above I want to print all four names dynamically if some one decides that they would like to print all of the workers in ANY cost center. I am looking for the VBA code for this endeavor. Is this feat possible without having to select each and every worker within that cost center? Also, to take it a step further, is it possible to select all workers from every cost center and print all cost centers at once? Tag: Exclude matches in a list Tag: 138981
  - 4
    - Excel 2007 Problem I just upgraded from Excel 2002 --> 2007 A particular file behaves strangely when saved in the original .xls format. EOMONTH function gets replaced with a currently unused NAME that refers to a formula upon Save. (This screws everything up, of course). Enabling the ATP add-in in the Name manager doesn't seem to make any difference. Saving in .xlsm format does seem to result in a proper file. What is going on? How to prevent it? Do I need to enable the either the regular, or the VBA ATP add-ins? Thanks. --ron Tag: Exclude matches in a list Tag: 138979
  - 5
    - Combo Box I am trying to duplicate what another person did for consistency but can't figure it out. A Combo Box appears with the black upside-down triangle at the right side of the object. This triangle appears on a printed page just as it appears onscreen. There is another option for a "drop-down box", however, that I can not figure out how to duplicate. A cell appears to just have numbers or text in it, but when the cell is selected the black triangle appears, giving options to select. I can not figure out how to create this in other cells, or even edit the referenced cells which provide the options. The cell prints just like a normal cell - without showing the black triangle drop-down box. Please help! Our company is cheap and out-dated, so we have excel 2000. Tag: Exclude matches in a list Tag: 138977
  - 6
    - Excel Data Not Visible in Cells 2007 I have two users in my office that use 2007 - myself and my manager. This morning something strange happened to a spreadsheet he was working on. There was data in the first 200 lines of the spreadsheet. somewhere around line 35 and up to line 60 the data - dissapeared. You couldn't see the text in the cells. BUT if you clicked in the cells you could see the info in the formula bar. He clicked save, and then it showed all the information again. This has happend several times over several spreadsheets. Can anyone help explain this?? Thanks Tag: Exclude matches in a list Tag: 138976
  - 7
    - Problem keeping data synchronised with database query. Hi all, I'm using Excel 2003. I have a spreadsheet containing a database query (inserted using Data->Import External Data->New Database Query) with 5 columns. The 6th column is a notes column, in which the user can type little comments to help them. When clicking "Refresh Data" on the data, I have a problem if a row is deleted, because the Notes cell that was on that line is now inline with the next row. I would prefer it if the notes cell was deleted entirely (or, more specifically, if the entire row was deleted). Is this possible in Excel 2003? I've been through the 3 options in "Data Range Properties" for "If the number of rows in the data range changes upon refresh", but none of them will delete entire rows - only the cells in the data set. I currently have it set to "Insert entire rows for new data, clear unused cells.", and am using a Dictionary object in VBA to read in the "Notes" values, then write them back out after the refresh is completed - is there a better way?! Cheers, RB. Tag: Exclude matches in a list Tag: 138975
  - 8
    - Strange behavior This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C88562.E7DE8BB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable A B C D as tr cf x er wr =20 sg rt yu x =20 sd ff x df qw cv x In the above array, columns A,B, & C are formulas. Theey represent a = first name, last name and email address, respectively. The formula for = column D is =3Dif(C2<>"","x",""). I've replicated it down the column.=20 When note that line 3 works correctly, but line 5 does not. Nor does the = formula work in any following rows.=20 I tried the same thing, programming in VB, with similar results. This = must be a setup problem, but I can't figure it out. I'm using Office 2007 SP1, and Vista Home Professional. Any suggestions would be appreciated... Jim ------=_NextPart_000_0006_01C88562.E7DE8BB0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.6000.16609" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>A    B    = C    D</FONT></DIV> <DIV><FONT face=3DArial size=3D2>as    = tr   =20 cf   x</FONT></DIV> <DIV><FONT face=3DArial size=3D2>er   =20 wr           = </FONT></DIV> <DIV><FONT face=3DArial size=3D2>sg   =20 rt   yu   x    = </FONT></DIV> <DIV><FONT face=3DArial size=3D2>sd   =20 ff          x</FONT></DIV> <DIV><FONT face=3DArial size=3D2>df    qw   = cv  =20 x</FONT></DIV> <DIV><FONT face=3DArial size=3D2>In the above array, columns A,B, & = C are=20 formulas. Theey represent a first name, last name and email address,=20 respectively. The formula for column D is =3Dif(C2<>"","x",""). = I've=20 replicated it down the column. </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>When note that line 3 works correctly, = but line 5=20 does not. Nor does the formula work in any following=20 rows. </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I tried the same thing, programming in = VB, with=20 similar results. This must be a setup problem, but I can't figure it=20 out.</FONT></DIV> <DIV><FONT face=3DArial size=3D2>I'm using Office 2007 SP1,  and = Vista Home=20 Professional.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Any suggestions would be=20 appreciated...</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Jim</FONT></DIV></BODY></HTML> ------=_NextPart_000_0006_01C88562.E7DE8BB0-- Tag: Exclude matches in a list Tag: 138972
  - 9
    - Excel 2003 / Windows Explorer (XP) sort order differs - How to fix? In Excel 2003, mpeg2_MP@H1440_1280x720p_29.97fps_CBR_40Mbs_openGOP_zzscan.mpv comes before mpeg2_MP@H1440_720x576i_25fps_CBR_45Mbs_closedGOP_alternatescan_LFF.mpv In XP's Windows Explorer and also using DIR/ON in a command prompt shell window, the sort order is reversed. How do I reconcile the two so they sort in the same order? Thank you kindly. Tag: Exclude matches in a list Tag: 138961
  - 10
    - ActiveCell I am trying to achieve If ActiveCell In Range ("C22:K22,C24:K24") Then but I require assistance with the correct syntax. Thanks Sandy Tag: Exclude matches in a list Tag: 138956
  - 11
    - Excel available resources I am having an error that happens frequently when opening a workbook. It says "Excel cannot complete this task with available resources". It also happens at other times. I have seen this happen at other times as well, but it happens most frequently on workbook open. It happens most when the computer has just come back from the screen saver or having been locked (or both). I am working in Excel 2007 on a machine with 2 GB of RAM and 4096MB of virtual memory. There are plenty of available resources when I check the mem usage in task manager. The information I've read about this error doesn't seem to relate to it. I am not using named ranges, nested names, or an older version of Excel. After closing the error message and a secondary one which has code 101648 after I hit "CTRL+SHIFT+I", the screen becomes messed up. The application toolbar and parts of the screen "ghost" onto other screens. I then have to drag another window across them to get them to redraw. Excel has to be restarted. It doesn't appear to be related to how long my computer's been on. Remote Desktop also seems to impact my base machine sometimes causing this. Any help is appreciated. Tag: Exclude matches in a list Tag: 138951
  - 12
    - Sumproduct error Hi! I have a formula that work fine on a computer, but on other computer (same software installed) it return #VALUE. Formula is: =SUMPRODUCT(--(Export!$G$2:$G$100>C9);--(Export!$G$2:$G$100<D9);--(Export!$D$2:$D$100)) Can anybody help? Thanks Tag: Exclude matches in a list Tag: 138939
  - 13
    - Number format for basis points Hello I am certainly not the first to ask but I didn't find an answer yet. Is there anyway to create a custom number format that allows to display basis points in Excel? For instance if I have a cell with the formula "=2.5%%", I would like to see "2.5bps" appearing. "0.0%%" allows to get "2.5%%" appearing. Any trick to get beyond that step? thanks in advance Charles Tag: Exclude matches in a list Tag: 138938
  - 14
    - PIVOT TABLE ALL option Is there any way to remove the "All" option that comes in the page fields in a pivot table?? Tag: Exclude matches in a list Tag: 138936
  - 15
    - Excel Help for Immediate Window I cannot display VBA Help for the Immediate Window using Excel 2003 under XP home. When I open Help and search for Immediate Window I get several topics. The top two, Use the Immediate Window and Immediate Window Keyboard Shortcuts do not display any information. When I click on them the machine just sits there and does nothing. All the other topics bring up a window with the topic information. Do I need to do something other than just click on either of the top two to get information displayed? Thank you. Tag: Exclude matches in a list Tag: 138935
  - 16
    - Cell colour according to date Hi, Is there a formula that would change the cell colour if a date in a cell was out of date afetr a set number of years. Example: Cell B9 contained date 140506 I then want the cell B9 to turn red when the date reaches 2 years later 140508. Thanks in advance Jason Tag: Exclude matches in a list Tag: 138929
  - 17
    - missing menus when I open excel the only thing I can see is the title bar and the cells. all the icons and menus are missing eg file, edit, insert etc how do I get the menus back thanks steve Tag: Exclude matches in a list Tag: 138928
  - 18
    - Footer i have following contents need to display in excel every page. anyway that can fullfill the request. I am using excel 2003 e.g contents approval by hod approval owner approval _________ ____________ ____________ audit aprroval implemented by reviewed by ___________ ______________ ____________ Tag: Exclude matches in a list Tag: 138926
  - 19
    - Deafult / Forced view arrangement when opening Is it possible to set a default or forced view arrangement to e.g. cascade so that and existing excel workbook automatically opens and rearranges / resizes to this mode. Most sheets I receive have been created and saved with Excel maximised and so the sheets invariably open with scroll bars running off the right and lower edges and hence have to be re-arranged Thanks Sanjay Jain Tag: Exclude matches in a list Tag: 138925
  - 20
    - Possible to indent the height of a row? Is it possible to apply a standard size across all rows, so that all tops and bottoms of the rows will be a specified distance from the text (similar to an indent, but from top to bottom) Thanks. Tag: Exclude matches in a list Tag: 138923
  - 21
    - FYI - MS Plugs 7 XL Flaws Just came across this article. http://blogs.zdnet.com/security/?p=943&tag=nl.e550 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- Tag: Exclude matches in a list Tag: 138922
  - 22
    - how to stop the Web and Reviewing Toolbars from randomly being Hello - It seems that excel is randomly displaying the Web Toolbar and Reviewing Toolbar---even though I never need to use either one of them. Does anyone happen to know how I can stop this from happening? Thanks. Tag: Exclude matches in a list Tag: 138921
  - 23
    - Converting pdf tables into Excel files Converting pdf tables into Excel files http://www.library.mcgill.ca/edrs/services/publications/howto/PDFtoXLS/PDFtoExcel.html Tag: Exclude matches in a list Tag: 138911
  - 24
    - How to transform a cell value that as no comma like 43534 into 435,34 Hello to ALL. I would like to know what should i use as a command or formula on Excel in order to transform a cell value that as no comma like 43534 into 435,34. Is it possible to do this in Excel? How can i do it? I use Excel 2003 SP3. Thank you in advance for any help. Best regards, Hugo Rosa Tag: Exclude matches in a list Tag: 138906
  - 25
    - Countif using less than or greater than criteria I have a list of data in cells d11:d15. I want to be able to count how many of the data points fall within a certain numeric range (ie less than 100 but greater than 50) but I want to be able to reference a specific cell containing the criteria rather than using '100' or '50' in the formula. In my worksheet 50 is in cell I2 and 100 is in cell K2. Tag: Exclude matches in a list Tag: 138902
- Next
  - 1
    - Simple countif syntax request Hi, I used to use Excel a lot but haven't for a few years. What is the syntax for using a countif function these days? "=countif(a1:a10,>b2)" used to do the trick I have tried using help but for some reason "=COUNTIF(B29:B40,">B23")" is not giving me the correct result, ie if I put 6.5 into cell b23 it is returning 0 not the number of cells in the range B29:B40 which are greater than 6.5 which is incredibly frustrating! Thanks, Will Tag: Exclude matches in a list Tag: 138898
  - 2
    - Data hidden/lost in Excel 2003 spreadsheet? I have an odd situation I was hoping some of you excel experts might be able to help me with. I was sent a spreadsheet (task schedule list) that was about 25kb (only 40 rows by 8 columns). After I did some editing (adding 2 new columns and adding some information to them) and saved the file, I noticed the file size has ballooned up to 2MB! I went hunting through the file to see if had accidentally pasted a something huge from my clipboard but could find nothing. I know it is the column I added as when I delete the entire column and save the file it goes back to the original 25kb. But when I delete the rows from this column where I added data (knowingly!) the file size does not change....it remains 2MB. I manually scrolled down from row 41 to 65,536 and could see nothing but white cells. I figure there's some data hidden in there but I can't seem to figure out how to find it. Can anyone recommend a good way to hunt this down? Much appreciated. Tag: Exclude matches in a list Tag: 138895
  - 3
    - Excel 2007 acting weird So I go into My Documents and double click an existing Excel file to open. Seems quite normal, no? This document opens, as well as a separate file "Book1". It gets rather annoying, that I've already opened the file I want. I didn't open Excel (start w/ a blank workbook) then go File>Open. Even more annoying is that I didn't make any changes to Book1 (at least that I can see), but it asks when I try to close Excel "Do you want to save?" I did have Solver installed and enabled. I've since tried disabling, but that doesn't seem to do anything. Is this normal behavior? If not, how can I stop it? TIA KenB Tag: Exclude matches in a list Tag: 138894
  - 4
    - Is there a way to copy a cell value into a cell comment? Is there a way to copy a cell value into a cell comment? thanks! Tag: Exclude matches in a list Tag: 138877
  - 5
    - When any cell in row 19 (eg K19) equals X I want the cell in row 25 3 Any tips will be greatly appreciated. Cheers! Tag: Exclude matches in a list Tag: 138875
  - 6
    - Can I move comment indicator to another corner of cell? In Excel 2002, those little triangular comment indicators display in the upper right corner of their cell. I want them in the U.L. corner instead. Can I tell Excel to do that? (There is no such option in Tools / Options / View's Comments panel.) Thanks. *** Tag: Exclude matches in a list Tag: 138870
  - 7
    - Protection from cut and paste Cell C1 is protected and has the formula A1 + B1. The users in my office sometimes cut and paste A1 and B1 to A2 and A2. After the cut and paste the formula in C1 changes to A2 + B2. What I want is for the formula to remain A1 + B1. Is there a way to protect or prevent cuts and pastes? Tag: Exclude matches in a list Tag: 138867
  - 8
    - send mail recipient gray (inactive) Hi I am working with excel 2007 and I added "send mail recipienton" on Quick access toolbar. and now it is gray(inactive) and I can't select it. I am using Outlook 2003. I tried this kb but it didn't work for me. My registry keys same. http://support.microsoft.com/kb/918792 What can I do for this problem Thanks... Tag: Exclude matches in a list Tag: 138860
  - 9
    - Largest non-blank sequence in row or column I'm trying to create a formula which will count the largest number of sequentially non-blank cells in a column or row. e.g. if the row had the following columns: |y|y|y|y| | |y|y|y|y|y|y| |y|y|y|y|y| the value would be 6 because it is the largest number of "y" values next to each other. Any Ideas please let me know. I'm using the formula to work out the longest number of days I do a particular task in a row. I update the spreadsheet daily and put a "y" in the row if I do that task. Tag: Exclude matches in a list Tag: 138859
  - 10
    - Heading styles in a TOC I have created some new Heading Styles for use in a document. How would I get them to be recognized in a TOC? Are there specified Styles that ONLY will work or is there a setting to get them to be recognized? Dave French Tag: Exclude matches in a list Tag: 138856
  - 11
    - Is this possible please Is this possible or am I asking too much of excel? If I have 254 working days in the year from D4 to D257 can it be done by a formula to work out the percentage automatically when a person is absent? It needs to know how many working days has passed in the year. Obviously up to today's date I believe it's 51 days. So, it would tell me for example that someone who has been off 10 days would have been off 5.1%. As each day passes the percentage would go down, or up if more time was taken off. It would finally tell me at the end of the year just how much time has been lost at a glance. If anyone has any ideas I would be most grateful. Bryan. Tag: Exclude matches in a list Tag: 138855
  - 12
    - Excel 2002 parameter query returns some blanks instead of data Hello and thanks up front... (I'm going bonkers), It's invoice time, so in my "Job Records" file, I'm using a parameter query (Excel 02, Microsoft Query) to return only those records that occur in one specified month. In the original Excel "Job Records" file on which the query is based, one column of data, labeled "PO", lists purchase order names (e.g. "VBL" for a verbal Purchase Order) or numbers (e.g. "102509"). The column is automatically formatted by Excel as "General", and I just let it ride. A second column of data, labeled "Funding Entity" lists codes - some codes are letters , others are numbers, (e.g. "GF" for Gen. Fund, "21" for 21st Fund) all formatted as "Text". When I execute the query, everything works great (the correct records, selected by month, are returned) except that the cells containing numbers formatted as "General", when returned in the "PO" column, are skipped so the resulting cell is just blank. And in the resulting "Funding Entity" column, cells containing letters (e.g. "GF") end up blank and all those containing numbers formatted as test, e.g. "21", show up just fine. So, I end up with a "PO" column containing all the entries that were "VBL", but only blanks where the entries that are numbers (formatted as "General") should be and a "Funding Entity" column containing only "21", but no "GF"s. I tried (feebly) to change the numbers formatting to "Numbers", but it didn't work. Anyone know where I went wrong? Tag: Exclude matches in a list Tag: 138854
  - 13
    - Get Column Header from Match in a Array? Here's what I am trying to do. I have a table of date, with the column names in Row 1, Column B-J. The Data from B2:J11 is dates. On a second sheet, I have the dates listed chronologically, I'd like to use a formula to take at the date in the second sheet, and look at the array on the first sheet and tell me what column it is in - or give me the text value from row 1 in that column. Tag: Exclude matches in a list Tag: 138852
  - 14
    - =?Utf-8?Q?Loosing_formula_when_changing_ro?= Iâ??m stymied and could use some help. I have a file that contains data in Sheet1 A1:A3 with values a,b,c. In Sheet2 B1:B3 I reference Sheet1â??s data with =Sheet1!A1 and so on. In Sheet2 if I try change the row reference from A1 to A2 I donâ??t get the value, I get the formula displayed =Sheet1!A2. The problem is that I have 2 columns like this and one works with reference changes the other doesnâ??t. I have checked cell formatting and option settings in all cells and they are all identical. The only clue Iâ??ve seen is that before the attempted change I can still do a precedent trace but after changing the row # to a valid row the trace indicates that the cell doesnâ??t â??contain a formula with a valid referenceâ??. The formula/link is getting clobbered, but I can find no settings that would effect that and would appreciate any hints. Tag: Exclude matches in a list Tag: 138848
  - 15
    - Automatically add calendar events? Using the powershell, is there a way to add calendar events to specific mailboxes? Our vacation time request system is a home grown web-based application. We'd like to configure it so that once the vacation time has been approved, the requesters Outlook calendar would then automatically be populated with "out of office" entries for the days off they requested. Thanks in advance! Tag: Exclude matches in a list Tag: 138847
  - 16
    - Auto sorting? Auto lock? --____QVNHWPNZZYRFJGJPVEVF____ Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: base64 Content-Disposition: inline; modification-date="Wed, 11 Mar 2008 08:26:31 -0500" SSBoYXZlIGEgd29ya3NoZWV0IGluIHdoaWNoIEkgZW50ZXIgZGF0YSB1bmRlciA4IHRpdGxlZCBj b2x1bW5zLiBBZnRlciBlbnRlcmluZyBpbmZvIG9uIHRoZSA4dGggYW5kIGZpbmFsIGNvbHVtbiwg SSB3b3VsZCBsaWtlIGZvciBleGNlbCB0byBzb3J0IGJ5IGNvbHVtbiAxLCB3aGljaCBpcyB0aGUg ZGF0ZSwgYW5kIGFsc28gbG9jayB0aGUgcm93LCBzYXkgcm93IDI0LCBwcmV2ZW50aW5nIG1pc3Rh a2VzIG9uIHByZXZpb3VzbHkgZW50ZXJlZCB0cmFuc2FjdGlvbnMuIElmIGFueW9uZSBrbm93cyBo b3cgdG8gaGVscCwgSSB3b3VsZCBkZWZpbml0ZWx5IGFwcHJlY2lhdGUgaXQuDQoNCkRhdmlk --____QVNHWPNZZYRFJGJPVEVF____ Content-Type: multipart/related; boundary="____UTWYPPFWWFEFDBBPDCPN____" --____UTWYPPFWWFEFDBBPDCPN____ Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: base64 Content-Disposition: inline; modification-date="Wed, 11 Mar 2008 08:26:31 -0500" PEhUTUw+PEhFQUQ+DQo8TUVUQSBodHRwLWVxdWl2PUNvbnRlbnQtVHlwZSBjb250ZW50PSJ0ZXh0 L2h0bWw7IGNoYXJzZXQ9dXRmLTgiPg0KPE1FVEEgY29udGVudD0iTVNIVE1MIDYuMDAuMjkwMC4z MjY4IiBuYW1lPUdFTkVSQVRPUj48L0hFQUQ+DQo8Qk9EWSBzdHlsZT0iTUFSR0lOOiA0cHggNHB4 IDFweDsgRk9OVDogMTBwdCBUYWhvbWEiPg0KPERJVj5JIGhhdmUgYSB3b3Jrc2hlZXQgaW4gd2hp Y2ggSSBlbnRlciBkYXRhIHVuZGVyIDggdGl0bGVkIGNvbHVtbnMuIEFmdGVyIGVudGVyaW5nIGlu Zm8gb24gdGhlIDh0aCBhbmQgZmluYWwgY29sdW1uLCBJIHdvdWxkIGxpa2UgZm9yIGV4Y2VsIHRv IHNvcnQgYnkgY29sdW1uIDEsIHdoaWNoIGlzIHRoZSBkYXRlLCBhbmQgYWxzbyBsb2NrIHRoZSBy b3csIHNheSByb3cgMjQsIHByZXZlbnRpbmcgbWlzdGFrZXMgb24gcHJldmlvdXNseSBlbnRlcmVk IHRyYW5zYWN0aW9ucy4gSWYgYW55b25lIGtub3dzIGhvdyB0byBoZWxwLCBJIHdvdWxkIGRlZmlu aXRlbHkgYXBwcmVjaWF0ZSBpdC48L0RJVj4NCjxESVY+Jm5ic3A7PC9ESVY+DQo8RElWPkRhdmlk PC9ESVY+PC9CT0RZPjwvSFRNTD4= --____UTWYPPFWWFEFDBBPDCPN____-- --____QVNHWPNZZYRFJGJPVEVF____-- Tag: Exclude matches in a list Tag: 138844
  - 17
    - Unexpained Shortcut to Personal.xls I have Office 2003's Excel set up on my desktop and my laptop under XP Home. They both work as expected. Personal.xls is in Program Files\Microsoft Office\OFFICE11\XLSTART on both machines. However, on the Desktop alone there is a shortcut to it in Documents and Settings\Main\Application Data\Microsoft\Excel\XLSTART. I delete the latter and open Excel and all the macros in Personal.xls that are tied to buttons and menu items work just fine without the shortcut. But when I close Excel, the shortcut reappears in Documents and Settings\Main\Application Data\Microsoft\Excel\XLSTART. This does not happen on the laptop. There must be something in the way I have the two Excels set up. The laptop has my name as the user name and the desktop has Main as the user name. This is the only difference I am aware of. This does not cause any problems that I can see but I am curious about why it should happen on one machine and not the other. I will appreciate any guidance on this riddle. Thank you. Tag: Exclude matches in a list Tag: 138837
  - 18
    - Please help: opening a datastream comma delimited Hello Anyone help? I'm trying to open a data stream in excel from my own remote server which uses comma delimitation and I'm trying to get Excel to open as such, instead of sticking all the data in one column. Someone suggested the code below might work from but on 'startrow' it throws up a 'compile error' that 'named argument is not found'. Anyone have an idea how to do it? I'm no expert. Workbooks.Open Filename:="http://x.x.x.x:8085/ppui/resultscsv?CUSTOMER=BOOKINGID=8165806", Origin:=437, Startrow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True Tag: Exclude matches in a list Tag: 138828
  - 19
    - Adding like occurences I can't figure this one out and need help. Sheet1 has names in col 1 and values in col 2 - I want to add totals together for everytime a certain name is found in col 1 in the sheet 2. (Name is listed multiple times on sheet 1) This is easy but I can't remember how to do it! Thanks for any help provided. Annette Tag: Exclude matches in a list Tag: 138823
  - 20
    - Drop-down lists --____APTVTIKANCHSADEVXJRW____ Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: base64 Content-Disposition: inline; modification-date="Wed, 11 Mar 2008 04:16:00 -0500" SXMgdGhlcmUgYW55d2F5IHBvc3NpYmxlIHRvIGNyZWF0ZSBhIERyb3AtZG93biBsaXN0IHRoYXQg aW5jbHVkZXMgdGhlIGRhdGVzIG9mIG9ubHkgdGhlIGN1cnJlbnQgbW9udGg/IElmIGl0J3MgTWFy Y2ggSSB3b3VsZCBsaWtlIGNvbHVtbiBBIHRvIG9ubHkgaGF2ZSBNYXJjaCAxc3QgdGhyb3VnaCB0 aGUgMzFzdCB0byBiZSBwb3NzaWJsZSBlbnRyaWVzLiBXaGVuIGl0IGJlY29tZXMgQXByaWwsIHRo ZW4gQXByaWwgZGF0ZXMgYXJlIG9ubHkgb25lcyBhbGxvd2VkLCBhbmQgc28gb24uIElmIHNvbWVv bmUgY291bGQgaGVscCB0aGF0IHdvdWxkIGJlIGdyZWF0IQ0KDQpEYXZpZA== --____APTVTIKANCHSADEVXJRW____ Content-Type: multipart/related; boundary="____KWGZCTCLPZSFANYZDVPO____" --____KWGZCTCLPZSFANYZDVPO____ Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: base64 Content-Disposition: inline; modification-date="Wed, 11 Mar 2008 04:16:00 -0500" PEhUTUw+PEhFQUQ+DQo8TUVUQSBodHRwLWVxdWl2PUNvbnRlbnQtVHlwZSBjb250ZW50PSJ0ZXh0 L2h0bWw7IGNoYXJzZXQ9dXRmLTgiPg0KPE1FVEEgY29udGVudD0iTVNIVE1MIDYuMDAuMjkwMC4z MjY4IiBuYW1lPUdFTkVSQVRPUj48L0hFQUQ+DQo8Qk9EWSBzdHlsZT0iTUFSR0lOOiA0cHggNHB4 IDFweDsgRk9OVDogMTBwdCBUYWhvbWEiPg0KPERJVj5JcyB0aGVyZSBhbnl3YXkgcG9zc2libGUg dG8gY3JlYXRlIGEgRHJvcC1kb3duIGxpc3QgdGhhdCBpbmNsdWRlcyB0aGUgZGF0ZXMgb2Ygb25s eSB0aGUgY3VycmVudCBtb250aD8gSWYgaXQncyBNYXJjaCBJIHdvdWxkIGxpa2UgY29sdW1uIEEg dG8gb25seSBoYXZlIE1hcmNoIDFzdCB0aHJvdWdoIHRoZSAzMXN0IHRvIGJlIHBvc3NpYmxlIGVu dHJpZXMuIFdoZW4gaXQgYmVjb21lcyBBcHJpbCwgdGhlbiBBcHJpbCBkYXRlcyBhcmUgb25seSBv bmVzIGFsbG93ZWQsIGFuZCBzbyBvbi4mbmJzcDtJZiBzb21lb25lIGNvdWxkIGhlbHAgdGhhdCB3 b3VsZCBiZSBncmVhdCE8L0RJVj4NCjxESVY+Jm5ic3A7PC9ESVY+DQo8RElWPkRhdmlkPC9ESVY+ PC9CT0RZPjwvSFRNTD4= --____KWGZCTCLPZSFANYZDVPO____-- --____APTVTIKANCHSADEVXJRW____-- Tag: Exclude matches in a list Tag: 138821
  - 21
    - Recipies of South INDIA Do you want to know to how to make mouthwatering,spicy foods? If you want to know how to make delicious South Indian foods please visit http://recipesoftamilnadu.blogspot.com/ Tag: Exclude matches in a list Tag: 138820
  - 22
    - time line in chart Hi How do I put a vertical line in a chart in excel at a given point? i.e. timeline Thanks Jon Tag: Exclude matches in a list Tag: 138817
  - 23
    - Pick several numbers from a list to get as near as possible to required total I have a list of several hundred policies each with a different value. Occasionally I have a request to 'sell off' policies to a certain value. At the moment I manually select policies from the list till I get 'close enough' to the total. Is there a way of automating this and getting the closest result possible? To put numbers to my problem above, suppose I have the following 9 policies 1 $11,234.67 2 $604.50 3 $7,632.00 4 $5,638.76 5 $16,345.98 6 $23,678.43 7 $15,678.44 8 $1,007.17 9 $53,713.97 I get a request to sell of $54,500 worth, at a glance I would probably select policies 8 & 9 (totaling $54,721.14), where as infact policies 3, 4, 5, 6 & 8 would be a better choice as they total $54,302.54 All help would be much appreciated Gerry Tag: Exclude matches in a list Tag: 138815
  - 24
    - Excel 2007 - Conditional Formatting copy/paste errors I find that if I create a conditional format at one cell and then copy that to a multitude of others, the variable references within the conditional format will not adjust accordingly. For example: At cell A1, I have the conditional format of "=IF(A1>0,1,0)". If I copy this to cell B2:B3 (in one stroke), I will find both cell B2 and B3 to have the same formula "=IF(B2>0,1,0)". This of course is fine for cell B2, but it is not fine for cell B3. I would expect cell B3 to reference itself; not cell B2. Another problem is that if the receiving cells should have a conditional format previously in place, the copy from A1 to B2:B3 will append to the preexisting conditional format as opposed to overwriting them. The only circumvention I have for this bug is to clear any possible conditional format before I paste to the receiving cells, B2:B3. So, how do I resolve this apparent bug in Excel? btw, this bug is not present in Excel 2003. Thanks, Bob. Tag: Exclude matches in a list Tag: 138813
  - 25
    - match and index function - getting it to keep its original reference I am using the match and index function to pull in data from a separate worksheet. I am using Column A as the reference cell, whereas my formula lies in Column F. The formula pulls in the data fine. The problem is, when I sort the data set, the match index function maintains the original reference cell and does NOT refer to the same row. So, for example, if my formula references A3, the formula continues to use A3 after I sort the cell (as if I had used an absolute reference). Does anyone know how to fix this? How can I pull in data using matchindex (or vlookup), but also be able to sort the rows without messing the formula up? Is there a way to use a list or named ranges to accomplish this? The actual formula I am using (in case it helps) is: =IF(ISNA(INDEX(CMS2!I:I,MATCH('CPT Summary for CMS Comp'!A27,CMS2! A:A,FALSE))),0,INDEX(CMS2!I:I,MATCH('CPT Summary for CMS Comp'! A27,CMS2!A:A,FALSE))) Thanks for any suggestions. Tag: Exclude matches in a list Tag: 138810

--____YYFNYPCQEPRARTJPNJTN____
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: base64
Content-Disposition: inline; modification-date="Sat, 14 Mar 2008 05:35:30
-0500"

QWxsLA0KDQpJIGhhdmUgYSBkeW5hbWljIHJhbmdlIGxpc3QgYnV0IHRoZXJlIGFyZSBtYW55IGR1
cGxpY2F0ZXMgd2l0aGluIDkwMCsgZW50cmllcy4gSXMgdGhlcmUgYW55d2F5IHRvIHByZXZlbnQg
ZHVwbGljYXRlcyBmcm9tIHNob3dpbmcgaW4gYSBkcm9wZG93bj8NCg0KRGF2aWQ=
--____YYFNYPCQEPRARTJPNJTN____
Content-Type: multipart/related; boundary="____FLAHYDPYDBEUWALLZPCC____"

--____FLAHYDPYDBEUWALLZPCC____
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: base64
Content-Disposition: inline; modification-date="Sat, 14 Mar 2008 05:35:30
-0500"

PEhUTUw+PEhFQUQ+DQo8TUVUQSBodHRwLWVxdWl2PUNvbnRlbnQtVHlwZSBjb250ZW50PSJ0ZXh0
L2h0bWw7IGNoYXJzZXQ9dXRmLTgiPg0KPE1FVEEgY29udGVudD0iTVNIVE1MIDYuMDAuMjkwMC4z
MjY4IiBuYW1lPUdFTkVSQVRPUj48L0hFQUQ+DQo8Qk9EWSBzdHlsZT0iTUFSR0lOOiA0cHggNHB4
IDFweDsgRk9OVDogMTBwdCBUYWhvbWEiPg0KPERJVj5BbGwsPC9ESVY+DQo8RElWPiZuYnNwOzwv
RElWPg0KPERJVj5JIGhhdmUgYSBkeW5hbWljIHJhbmdlIGxpc3QgYnV0IHRoZXJlIGFyZSBtYW55
IGR1cGxpY2F0ZXMgd2l0aGluIDkwMCsgZW50cmllcy4gSXMgdGhlcmUgYW55d2F5IHRvIHByZXZl
bnQgZHVwbGljYXRlcyBmcm9tIHNob3dpbmcgaW4gYSBkcm9wZG93bj88L0RJVj4NCjxESVY+Jm5i
c3A7PC9ESVY+DQo8RElWPkRhdmlkPC9ESVY+PC9CT0RZPjwvSFRNTD4=
--____FLAHYDPYDBEUWALLZPCC____--

--____YYFNYPCQEPRARTJPNJTN____--

Top

Re: Exclude matches in a list by H�ctor

Héctor
Fri Mar 14 18:09:16 CDT 2008

hi, David !

> I have a dynamic range list but there are many duplicates within 900+ entries.
> Is there anyway to prevent duplicates from showing in a dropdown?

AFAIK, if you don't want duplicate records in your dropdown list...
-> duplicate records *must* NOT be in your dropdown-source list

try with advanced filter, unique and copy to another location
-> and make the new location the source range for your dropdown list

hth,
hector.

Top