Advanced Filters cause Excel crash

Does anyone have the fix to the problem that, 
intermittently, Excel (all versions) crashes when you try 
to use an advanced filter.

In a room of machines, all using an identical starting 
spreadsheet, one or more of them may crash when trying to 
implement a very simple advanced filter.

Any solutions?
6/30/2003, 8:43:29 AM

Hi... I have this list of products from A2:A250 , companys orders B2:B250 & C3:C250 and so on... what i would like to do is to be able to wiew only the filled cells all together at the same time for all the companys... say you have en order on B3 , C216 , D59 then I would like after filtering just see those three orders and not all 250 products so I would have to scroll and be observant to see what orders are made... you get it? hmm :confused: Thanks / Jennifer -- Hamidam ------------------------------------------------------------------------ Hamidam's Profile: View this thread:

Advanced Filter
1 replies , 11/14/2003, 3:14:08 PM
I asked the following question yestrday and received the following answer which works fine, BUT the question has changed slightly today: Q: I have a sheet of data which I need to filter. The sheet contains several 100 rows of data, the data is split into 3 categories and column B has either 1, 2 or 3 in it depending on which category it is in. I want to filter the sheet to show ALL category 1 data and any category 2 or 3 data which have an entry in column O. A: =OR(B2=1,AND(OR(B2=2,B2=3),LEN(O2)>0)) Todays question includes another criteria: I want to filter the sheet to show ALL category 1 data. All category 2 or 3 data which have an entry in column O, and no entry in column L. Gareth

Advanced filter I think
4 replies , 6/27/2007, 2:12:01 PM
I think this can be done by advanced filter but I can't get it to work! I want to filter my worksheet by 10 different criteria that are in the same column. If it can be done with an advanced filter how would I set it up? If it can't be done using this advanced filter what are the other options I have? Thanks in advance

Advanced cell function. is it possible
11 replies , 1/26/2007, 7:17:18 PM
Hi all, I`m having a problem on excel and I would like to know if it`s possible to be done without programming in VB: Let's say i have the following: A B C 1 100 100 1100 The formula should be on C1, and it should evaluate to: Everytime cells A1 and B1 have the same value, C1 = A1 + 1000 If B1 for example change to 200, C1 shoulb stay 1100, until A1 changes to 200 also, when C1 should change to 1200. i'll try to do some simulation to make it clear: A B C 1 100 100 1100 A B C 1 200 100 1100 A B C 1 300 100 1100 A B C 1 300 300 1300 A B C 1 300 500 1500 Or, in other words, C1 should be equal to 1000 + the value of A1 or B1 on the last time they were equal. Thanks in advance.

Help on Interest calculation? Advanced maths ?
16 replies , 4/25/2007, 4:27:44 PM
This worksheet works, for one scenario. Need help, please, for the second scenario Initial deposit �250, invest regular �250 per month. Gross interest 5.6%, calculated daily, paid on the anniversary of account opening (i.e 366 days) The 5.6 is in range-named cell "APR". Cells B11..B376 are "=$E10...", Cells $D10... are "=$B10...+$C10...) Formula in (CALC column) cells E10...E375 is =POWER(APR/100+1,1/365)*$B10.... Spreadsheet accuracy - exact to the penny (agrees the bank's printed statement). 06 A B C D E F 07 day daily deposit subtotal CALC daily 08 # total interest 09 10 0 0.00 250.00 250.00000 250.03732 0.03732 11 1 250.04 250.03732 250.07465 0.03733 12 2 250.07 250.03733 250.07250 0.03733 continue to row 380 (366 days) with 12 monthly deposits of �250 day daily deposit daily int # total total total 376 366 3342.22 3250.00 92.22 Second scenario is slightly different. There's just ONE initial deposit, interest still calculated daily, but added monthly. �100,000 would thus become �105,750 after 1 year, at 5.75% gross interest rate, with 12 interest payments credited into the "deposit" column. I'm using the following formula to calculate the Annual Effective Rate (AER). Gross interest rate of 5.75 (rangename APR) The number "12" (in cell rangename PERIOD) = the number of periodic interest payments - so =(POWER((1+APR/100),1/PERIOD)-1)*PERIOD*100 which converts to =(POWER((1+5.75/100),1/12)-1)*12*100, evaluates to 5.60381 (which is in "rangenamed" cell AER). Using a spreadsheet formatted similar to example above, the "CALC" cells have slightly changed formula =POWER(AER/100+1,1/365)*$B10 ,(AER replacing APR). BUT I cannot get a sensible result - it's known totals - should be �105,750.00 / �5750.00. Where am I going wrong ? Is there a logic fault? (I did take out what would have been duplication of the 12 monthly interest additions, in the running total column). I'm not an advanced mathematician. Have a friend, who is, he created the Excel formulae from the British Banking Association;s (hugely complex) mathematical formulae. HELP ! please?

advanced filter
1 replies , 1/13/2006, 3:54:02 AM
never tried using advanced filter, and am having a bit of difficulty understading 1) is it my best solution, 2) placement of the different pieces. Here's my situation: I have a list of accounts from a chart of accounts. To the right of the list, I have "1" indicators for several different people (each occupying his/her own column), with the "1" indicating that the person does or does not need to use the account listed in the line that the "1" is in. Based on this 1 for each person, I'd like to make named ranges that summarize only the accounts (with several columns of the data describing each account) that are relevant for that person. My thought was filtering the list for each person's set of 1's. So it looks like this: A B C D E F MajorAcct MinorAcct AcctDesc Acct# Person1 Person2 MajAcctTxt1 MinAcctTxt1 AcctDesc1 Acct1 1 MajAcctTxt2 MinAcctTxt2 AcctDesc2 Acct2 1 MajAcctTxt3 MinAcctTxt3 AcctDesc3 Acct3 1 MajAcctTxt4 MinAcctTxt3 AcctDesc4 Acct4 1 I want a filter that will, in a different place than the original list (maybe off to the right) show me a subset of the entire list for each person (so, two subsets), with the basis being that a "1" is listed for that person. I need, in each subset, to have all four columns of txt show up for each subset entry. what I need help on is 1) is advanced filter my answer or is there something I don't know about that works better, 2) where do I place the cursor to set up the filter, 3) what are the things I indicate for the filter dialogue that will get me setup the right way. Thanks so much. Sorry for all the detail, but I always figure it's better to explain more than have a request with guessing on the other side about what it means. -- Boris

A Year in advance ?
11 replies , 2/27/2008, 8:42:00 AM
Hello, I am using Excel 2007 My problem is this, I am entering dates with a renewal date of a year in advance minus 1 day : A1 B1 30/01/08 29/01/09 20/03/08 19/08/09 01/01/08 31/12/08 01/02/09 31/01/10 and so on ..... Presently I am using Vlookup() to put the information into B1,B2,B3,etc automatically,however the date data is getting increasing long. Is there a formulua that I can enter in B1,B2,etc that will achieve this without resorting to Vlookup() All offers of help greatly accepted, and my thanks to all respondents FinanceGuru

Advance Filter
1 replies , 4/9/2004, 6:57:34 PM
I tried to use the advance filter to filter information by using 2 separte worksheets. I opened the Advance Filter, selected Filter the list, in-place. In the List range: field I entered the worksheet name and column (All!$E:$E), from the worksheet with all the data. In the Criteria range: field I entered worksheet name and cells with criteria to filter (Criteria!$A$1:$A$7). I was able to filter this information once. Since then either Excel shuts down or nothing is filtered. I've tried to do it on the same worksheet based on the help instructions and nothing is filtered. Please Help! Thanks, Kay

Advance filter not filtering unique records only
1 replies , 5/22/2007, 8:17:48 PM
Hi, I have a user who has four large lists, he wants to combine them into two master lists; but hide or delete duplicates. I showed him how to use Advance Filter and he said it worked perfectly for combination; but the second worksheet did not filter only unique records. He assured me he followed the directions and click "Unique Records" in the Advance filter dialog box; but it didn't work. Can you guys give me a few more avenues he can pursue: (I suggested misspelled entries, and double-checking his work.) Thanks Kim

advanced if?????
5 replies , 7/29/2005, 4:38:01 PM
Hi, I have a spreadsheet which looks a bit like this.. Phone number cost 0121 25 01212 26 012124 27 0121249 27 0121249357 24 013 24 0131 25 01312 26 01312463 27 0131246452 24 im sorry but my example table is not very good. fyi the numbers afte the space in each row should be the next column... I need to find a way to cost the phone numbers i enter eg. if i need to find out how much 0121249357 will cost it will tel me 24 but if i need to find 0121249367 i need it to find 27 (0121249) is there a way i can get excel to look thru a string of say 9 digits but if it doesnt find a match then look thru the 1st 8 digits, then 7 then 6 till it finds a match??? sorry if im not very clear but i dont really know excel or th terminology very well. would be very grateful if some1 could help. Thanks, Tara -- tara ----------------------------------------------------------------------- taran's Profile: View this thread:

Hello, We are contemplating to use MS build as the foundation for our asset production pipeline but in order to do this we need to have two fundamental functionalities. 1. Dynamic dependencies. Some dependencies are static but other dependencies needs to be calculated by looking at the content of the source file, because the file may contain references to files that needs to be built. (The problem is similar to C and C++, where the .cpp files contains references to .h files which in turn may reference other files and unlike make (and more similar to jam) we want to be able to cope with this in general without forcing manual update of the dependencies in the build scripts. 2. Advanced nochange detection In some cases it is alright that changes are determined based on the modification timestamp of the input files for th build step, but sometimes we would like to use md5 checksums instead because a file might be updated frequently but with no changes due to mechanics earlier in the dependency graph. If the build step in question is very expensive, it would be nice if the system would be able to detect that although the file is updated there is no change. It is possible to code this in the different tools but it would be nice if it was supported first class by the build system itself. --- How are these problems solved by MS build and is any detailed documentation/samples/etc. that illustrate how to set the system up and integrate these dependency trackers and advanced nochange detectors? Any help or suggestions would be greatly appreciated Kind regards Ulf Johansen

MVP's - Advanced Users
0 replies , 7/4/2003, 1:15:22 AM
Looking for people who might be interested in co-authoring or participating in a project with potential commercial exposure. You're very good at this thing called Excel. In fact, you're the experts. We come to you when we don't understand. Have you ever thought about publishing a book on Excel? Not just another Excel how to book. A different kind of book. If interested, please reply to: b************

Advanced Filter ...??
4 replies , 4/13/2007, 9:32:02 AM
Help please … Scenario - I have a number of workbooks that have links to a ‘master’ to manage days spent on about 20 projects. To make entering of relevant data easier for the ‘Team leaders’ I would like to display only the projects that relate to the team members even if they have not yet allotted time to it – ie from below this would be P1 & P2 . The table below is a very simplified version of the data, there are a number of links both to and from the ‘master’ and a number of filters already applied … I think I can do this with a formula in an ‘Advanced Filter’ but cannot work out how to display the rows that contain no data for some of the team members – I have also tried with ‘If’ statements and ‘lookup’ but I think I am making it more complicated than it is … any thoughts would be gratefully received … Name Project Week 12 Week 13 Total Mr A P1 3 3 6 P2 2 2 4 P3 0 Ms B P1 2 2 4 P2 0 P3 0 Mr C P1 0 P2 3 3 6 P3 0 Thanks in advance … -- Thanks and have a good day Ruth

Advanced Probability.
10 replies , 5/28/2008, 7:04:00 PM
Hello, I have a set of data that is 14 cells. I want to find the probability that the average of 5 randomly selected cells out of these 14 is over a certain point. HOW?!

Advancing by One Month
8 replies , 6/11/2004, 7:43:31 PM
Say I have an equation in Excel in B1 set to add 9 months to whatever is typed in A1. The problem is that the default seems to be that 1 month to Excel is about 30 units, so that 9 months would be 270. Unfortunately, this means that if you type in 31-May-2003, for example, it will bring up 25-Feb-2004 when it ought to be 31-Mar-2004. Is there a way to set it up so that Excel will look at the date typed, and simply increase the month portion of the value 9 times (adding to the year if appropriate)?

Advanced Countif
6 replies , 12/12/2007, 2:21:02 PM
Good morning, nothing can ever be easy in the Army. ;-) I have a workbook with multiple worksheets. The First sheet contains totals etc. I want to be able to count all occurences of data in different tabs and return those values to specific locations on the first sheet. Example. I have SGT Jones, SGT Smith and SGT Adams. Each of these have specific mission numbers that I need to track for each month. I want to track,for example, how many times each of them acquires a bolt and how many times each acquires a nut for each month. So I have OCT, NOV, DEC sheets along with the MAIN sheet in the workbook. On the main Sheet I have each of the Soldiers names in Column A and I want to tally the number of bots and nuts for each month in the columns to the right. I have tried multiple times to do a countif, but I can't get it to work right. I can provide a sample of the workook if necessary. Any help would be greatly appreciated.

Advanced filter
1 replies , 9/23/2005, 12:14:31 AM
I have a data base with hundreds of email addresses. I want to filter out certain addresses from the list, for example those which are clearly not domestic such as those ending in ".de" Here is an example of a section of the list: a********** a********** a********** a************** b******* b******** b********** How do I use advanced filter to delete such selections? Others might be .cn, .tw. etc. Thanks you

Lewis Shanks
Advanced Filter
15 replies , 3/13/2007, 2:12:31 PM
In using office 2003 and playing with advanced filter. When I run it I am getting : 'The extract range has a missing or illegal field name' error message. All the columns have field names - cant find anything on MS other than for Excel 97/98 and missing column headings Any help much appreciated A

Advanced Filtering
3 replies , 7/31/2003, 7:19:19 PM
Does anybody know how to use the Advanced Filter feature in MS Excel? Your help would be appreciated.

Advanced Filter in Excel no longer working
0 replies , 11/4/2005, 3:31:04 PM
Dear Excel gods, goddesses, and gurus, An Advanced Filter I had been using successfully to extract data to another worksheet is no longer working. I have checked and rechecked the the range, criteria, and copy to: specs and have tried simplifying the criteria and filtering the list in place to no avail. The result is nothing is being filtered out. Urgent. Need today for a blood drive. Thanks.

Advancing to next letter, programatically
1 replies , 11/30/2005, 8:44:38 PM
I have records which are tracking the version of a document using the alphabet for the version number. Originally the request was for the version to go up to "G", but they have now found they would like the version to be "endless". So, I'm trying to figure out how to go from "A" to "B" (and "AA" to "BB" - etc.) withough using 2 or 3 (see below) hardcoded lines per letter/round. I am currently testing the idea of "A" thru "A9" for each letter (with 'minimal' ?? coding), but I'm not sure yet if the customer will be satisfied with that option. Here is a snippet of what I currently have in use: ' Case "A" ' strVer = "B" ' Case "B" ' strVer = "C" ' Case "C" ' strVer = "D" ' Case "D" ' strVer = "E" ' Case "E" ' strVer = "F" ' Case "F" ' strVer = "G" ' End Select

Why won't advanced filter return filter results?
5 replies , 9/11/2006, 5:46:01 PM
I use advance filter a lot. For some reason, I cannot get it to return rows based on criteria for column A, which is a number formatted to text. If I choose to filter the invoice number three columns over it returns the filtered rows just fine.

Advanced Filter Case Sensitive
4 replies , 5/20/2004, 8:43:34 PM
Hi, Is there any way to make an advanced filter case sensitive? For example, I want to type DOG in the criteria range and run the filter and only pull records that are DOG, not dog. Thanks very much.

Joseph S Johnson
Advanced Apology
13 replies , 5/15/2008, 12:28:01 PM
I've been searching the internet for the past 1.5 days and have yet to find an example of the formula I'm looking for.... so if this is easy, I apologize in advance. I want a count of the number of cells where column "I" values = "NA" and column "D" values = "0". In the example below, only the 2nd & 5th rows would meet the criteria. I D NA 5 NA 0 xx/xx/xx 0 xx/xx/xx 0 NA 0 xx/xx/xx 7 NA 3

Advanced chart question
1 replies , 7/28/2008, 12:03:45 PM
Hi, I have an interesting question on charting, hope someone can help me out with it. For a performance analysis I am showing both the performance of a portfolio (line) and its corresponding benchmark (bar). In order to make the out- or under performance more visible I would like to give to "balls" in the line a red color in case of under performance and a green color in case of out performance. Obviously you can do this manual but I have over twenty graphs with 20 points;- (........... Any suggestions if this can be set automatic?? many thanks! Rgds Robert

I have three basic (for programmers) conditional formatting problems to solve in Excel for which I require some help. These are the last 3 conditions I need to complete for the worksheet so any help will be really appreciated. They are as follows: 1. If cell Y2 is > 0 and cell Z2 = 0 I need to change the colour of cell Z2 2. If cell Y2 or cell AA2 > 0 and cells V2, W2, or X2 = the value "Z" I need to change the colour of the cell containing "Z" (V2, W2 or X2). If changing the colour of only the single cell containing "Z" cannot be done, I guess changing the colour of the whole row would be okay. 3. If cell Y2 or cell AA2 > 0 and cell R2 <> 100 I need to change the colour of the row Thanks to the google group community in advance!

Advanced Filter
4 replies , 7/5/2003, 9:44:36 AM
Hi, Is there any limit to the number of unique items that Advanced Filter will find in a list? I have about 52,000 rows in a single column and it shows 1500 or so as unique. I believe auto filter has a limit of around one thousand, is there a similar restriction on Advanced? I've looked everywhere I can think of and found no reference at all to limits. Thanks in advance, Alan

advanced filter
2 replies , 8/9/2003, 4:06:19 PM
Hi all: I'm using advanced filter on a lot of data and I'd like to speed up the process a little. I would like the list to sort automatically after enter data into my criteria field(s) instead of having to click on advanced filter every time and run the search. Any ideas? Wayne

Wayne Burritt
PowerPoint 2007 I've got my tablet in presenter mode, and I can't annotate my slides during a presentation. The ink help topic from Microsoft says: "To write on slides during your slide show, do the following: In Slide Show view, right-click the slide that you want to write on, point to Pointer Options, and then click a pen or highlighter option. Hold down the left mouse button and drag to write or draw on your slides." If only it were that easy. Left clicking the mouse does allow me to draw on the slide, but as soon as release the left button (or remove the pen) it advances to the next slide. There has to be an easy explanation or fix, but I have not found one anywhere. I tried searching these posts but got nothing. And, I don't want to call and pay to have them give me an easy solution to something that clearly isn't working according to their instructions and isn't addressed in any of the help topics. Please Help.

Advanced filter
1 replies , 2/4/2008, 7:05:56 AM
please I want to know what is benefit of using advanced Filter? Any example or web page demonstrate advanced Filter Thank you

Hi By using the following I am able to count the number of lines that have data: iDataRows = Application.WorksheetFunction.CountA(ActiveSheet.Columns(1)) For x = 1 To iDataRows 'Your processing code here Next x Then use the same info or data for this Advance Filter as a target cell for after the "Range" as a "CriteriaRange" first cell data (+ 1) and then calculating the end cell (+ 7), and copy into a cell down: Range("A1:S100").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "A101:B107"), CopyToRange:=Range("A110"), Unique:=False How do I use this "iDataRows" variable to perform this function? I am not familiar at all with VB.

Advanced Filter
3 replies , 5/21/2009, 6:28:16 PM
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am a new Mac user and am having difficulty adjusting to Office for Mac. Incidentally, I am using the Student version. I regularly download a list of job opportunities with the following fields as column headings: a brief job description (text), wage (contents may be text or number), job type (text), location (text)) and date posted (text formatted with a 2-digit year). I have tried to filter the "Location" using a formula ="<>West*" and also using a simple a text entry (West*). These techniques worked nicely in Excel for Windows. The examples above result in the entire list being filtered out (all rows of the list are hidden). Searching Excel help for Advanced Filter returned only help for Autofilter and I was unable to locate a similar post on your forum. Can someone show me where I am going wrong? Thanks in advance for your help. u2b

advanced lookup?
1 replies , 11/7/2007, 4:13:18 PM
Good morning to all: I have a spreadsheet S1 where a cell B1 contains the value "MN" and a cell A2 contains the value "AB" I have a spreadsheet S2 where I have a cell A2 containing the value "MN" and cell B2 contains "AB" and the cells C2 to L2 contain prices and the cells above C1 to L1 contain the names of the suppliers. I am trying to put in the cell B2 of the spreadsheet S1 a formula that would retrieve the smallest price (obtained from the cells C2 to L2 in spreadsheet S2) and the name of the supplier who has the smallest price (obtained from the cells C1 to L1 in the spreadsheet S2). Ideal would be to eliminate the zero or blank values from the formula I tried to combine SMALL and INDEX with no success... hairs are getting grayer by the minute here. Can anyone assist? Thanks a bunch for your time MK

Michel Khennafi
Screwup in Outlook 2003 advance search
1 replies , 9/21/2005, 4:51:02 PM
Whenever you are doing an advance search in Outlook 2003 instead of searching the entire mailbox as it was done in previous versions of outlook, Outlook 2003 was reconfigured to only search the current folder you are in. This means that after you select the advance search you have to go back and select the mailbox which is a pain in the neck and non added value activity. ---------------- 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.