Autofilter - No. of Rows

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
    - Startup Hello My Excel VBA is sometimes used by other people, and they ara "abusing" my codes by using toolbar commands. I want to make Excel VBA and to hide / disable all toolbars and any other Excel command. I want that only my buttons can be used. Beforehand thanking for any help,7 Sb. Tag: Autofilter - No. of Rows Tag: 138753
  - 2
    - flip columns to rows Is there any way to 'flip' a worksheet with one command, so that columns become rows (or rows become columns)? Tag: Autofilter - No. of Rows Tag: 138748
  - 3
    - Outsourcing Data Entry 100% guaranteed or your money back Data Entry Outsourcing Services ensures high quality, result oriented in time. We promise to deliver excellent output at low turn around times and cost-effective rates. Our dedicated team of highly experienced professionals to provide you high quality outsourcing services. Do visit us at http://www.dataentryoutsourcing.co.uk/dataentry_services.php, to know more about our Data Entry Outsourcing Services and avail our services at affordable rates. Data Entry Outsourcing provides data entry services like numeric data entry, textual data entry, image data entry, data format, data conversion and also online data entry, offline data entry with 99.98% accuracy and time bound. Data Entry Services also provides form data entry, data capture, HTML/ SGML coding, image scanning, file conversion with low cost, high quality, time bound and high data security. By taking advantage of Data Entry Outsourcings domain knowledge, quality processes, high-tech infrastructure, and global delivery options, clients are able to perform better while allowing them to concentrate on their core competencies. Based in U.K., Data Entry Outsourcing operates from offshore multiple production centers in India. Contact us for more information about Data Entry Services. E-mail Us: info@dataentryoutsourcing.co.uk Phone (India): +91-794-000 3000 Fax : +91-794-000 3002. Tag: Autofilter - No. of Rows Tag: 138745
  - 4
    - Remove Final Character from Cell. Hi All, I am trying to remove the final character from a cell with a find and replace. the cell contents are like the following example. 07APR07A needing to be 07APR07 But other cells in the list are already in the format of 07-Apr-07 07APR07* I have managed to remove the * by using the tide ~ character. So I don't know if there is a similar character to remove the last character only. But am stuck with the last character. Can someone please help. Tag: Autofilter - No. of Rows Tag: 138741
  - 5
    - runtime error '429' activex component can't create object We recently moved to "visual studio 2008" . Our addin workd find on our development machine which has Excel 2003 and Excel 2007. When we create the installation package and install onto a machine with only Excel 2003 we get the "runtime error '429' activex component can't create object". Any ideas please. Thanks in advance Tag: Autofilter - No. of Rows Tag: 138738
  - 6
    - Excel crashed with ntdll.dll when opening a file Dears, My Excel crashed with ntdll.dll when opening a file Error signature AppName: excel.exe AppVer: 10.0.6834.0 ModName: ntdll.dll ModVer: 5.1.2600.2180 Offset: 00010f29 I am using Office XP with SP3, Windows XP with SP2. Thanks B. G. Tag: Autofilter - No. of Rows Tag: 138736
  - 7
    - Putting a "period" at the end of every cell's-worth of text Hi. I have a large number of cells in a column, each of which contains text. Some cells of text end in a period, some do not. Is it possible to write a function that would add a period to the end of each textbox? I am not worried about corner cases, i.e., where the text ends with "M.D." or suchlike. Thanks for any help - Tag: Autofilter - No. of Rows Tag: 138727
  - 8
    - Time passed sinec date in column A I am trying to greate a document that will tell me when someone needs to renew thier training. I have 1, 2, 3, & 4 yearly training programs. I want to have a system where I can set conditional formating to goy amber when it has been 10 months since the date and then Red when over a year> I have a date on column A. In column B I want to know how many months have passed since that date. I am sure the solution is easy, but I can't work it out!! Many thanks Steve Tag: Autofilter - No. of Rows Tag: 138726
  - 9
    - I am interested in knowing some of your favorite Excel Add-ins Hello - I have been too cheap to buy any excel add-ins or plug-ins, and then recently I downloaded one (i.e., the PUP v6 toolbar), and realized that it was worth much more than the $40 price tag. I would be interested to hear some of your favorite add-ins (either free or purchased) and why you find them to be valuable. It would also be helpful to have the link to where they can be found. Thanks! - M Tag: Autofilter - No. of Rows Tag: 138725
  - 10
    - Data grouping and averaging in excel 2003 I am looking into purchasing a data logger that will collect voltage, amperage with a time scale. Below is a sample of the data and a summary to be generated above the data. I have some problems with how I can have a msexcel spreadsheet automatically calculate averages and total times for each zone. I would like a summary for many separate zones with a dozen or so sessions that have recording to last from 10 to 100 seconds within each session and automatically disregard calculating any values in column C that are <30 & all rows in column B that have been disregarded in column C. Averaging for volts and amps will include all readings within each zone. To calculate time, I would like to calculate the difference between when the amps go above 30 & the time when the amps drop below 30 + the time one row down. Formulas In A2 =(A11-A9)+(A15-A13)+(A20-A15) In B2 =AVERAGE(B9:B10,B13:B15,B18:B19) In C2 =AVERAGE(C9:C10,C13:C15,C18:C19) In A3 =(A24-A22)+(A29-A26) In B3 =AVERAGE(B22:B23,B26:B28) In C3 =AVERAGE(C22:C23,C26:C28) A B C D 1 total time avg volts avg amps total zone 2 00:00:54 25.3 122 b 3 00:00:30 25.0 125 f 4 5 6 7 8 time volts amps zone 9 13:30:12 24.9 131 b 10 13:30:18 25.3 124 b 11 13:30:24 39.4 12 b 12 13:30:30 38.4 9 b 13 13:30:36 26.3 130 b 14 13:30:42 24.9 114 b 15 13:30:48 25.3 125 b 16 13:30:54 39.4 2 b 17 13:31:00 38.4 8 b 18 13:31:06 25.2 111 b 19 13:31:12 25.1 117 b 20 13:31:18 39.4 3 b 21 13:31:24 38.4 13 b 22 13:31:30 25.1 125 f 23 13:31:36 25 131 f 24 13:31:42 38.4 7 f 25 13:31:48 37.6 11 f 26 13:31:54 25 124 f 27 13:32:00 24.9 130 f 28 13:32:06 24.8 114 f 29 13:32:12 37.6 11 f Can anyone help me with this? And an I on the right track? Can this be accomplished without VBA? (as I have no success with it in the past) Thank you for your consideration. Tag: Autofilter - No. of Rows Tag: 138705
  - 11
    - Data grouping and averaging questions in excel 2003 I am looking into purchasing a data logger that will collect voltage, amperage with a time scale.Below is a sample of the data and a summary to be generated above the data. I have some problems with how I can have a msexcel spreadsheet automatically calculate averages and total times for each zone. I would like a summary for many separate zones with a dozen or so sessions that have recording to last from 10 to 100 seconds within each session. I would like to automatically disregard calculating any values in column C that are <30 & all rows in column B that have been disregarded in column C. Averaging for volts and amps will include all readings within each zone. To calculate time, I would like to calculate the difference between when the amps go above 30 & the time when the amps drop below 30 + the time one row down. Formulas In A2 =(A11-A9)+(A15-A13)+(A20-A15) In B2 =AVERAGE(B9:B10,B13:B15,B18:B19) In C2 =AVERAGE(C9:C10,C13:C15,C18:C19) In A3 =(A24-A22)+(A29-A26) In B3 =AVERAGE(B22:B23,B26:B28) In C3 =AVERAGE(C22:C23,C26:C28) A B C D 1 total time avg volts avg amps total zone 2 00:00:54 25.3 122 b 3 00:00:30 25.0 125 f 4 5 6 7 8 time volts amps zone 9 13:30:12 24.9 131 b 10 13:30:18 25.3 124 b 11 13:30:24 39.4 12 b 12 13:30:30 38.4 9 b 13 13:30:36 26.3 130 b 14 13:30:42 24.9 114 b 15 13:30:48 25.3 125 b 16 13:30:54 39.4 2 b 17 13:31:00 38.4 8 b 18 13:31:06 25.2 111 b 19 13:31:12 25.1 117 b 20 13:31:18 39.4 3 b 21 13:31:24 38.4 13 b 22 13:31:30 25.1 125 f 23 13:31:36 25 131 f 24 13:31:42 38.4 7 f 25 13:31:48 37.6 11 f 26 13:31:54 25 124 f 27 13:32:00 24.9 130 f 28 13:32:06 24.8 114 f 29 13:32:12 37.6 11 f Can anyone help me with this? And an I on the right track? Can this be accomplished without VBA? (as I have no success with it in the past) Thank you for your consideration. Tag: Autofilter - No. of Rows Tag: 138703
  - 12
    - How to add the date after the "A1" Hi, I type "The information gathered as of March 9, 2008" on cell "A1" Is there a way to make a date default like "The information gathered as of " & Date (default). Your help would be much apprecated. Tag: Autofilter - No. of Rows Tag: 138697
  - 13
    - Compatibility Question I have created a file in Excel 2003, and saved as "Excel 97-Excel2003 &5.0/95 Workbook" and I am running XP. The file utilizes several macros, VLOOKUP and COUNTIF commands. My question is if this file is distributed throughout the general public, what do you believe could be some unforeseen compatibility issues I am not aware of as it will obviously be opened on several versions of Operating Systems and versions of Excel? Do I have a 50% chance of success that it will be compatible? What would be your guess at the percentage of success? What are my options to make it more successful throughout more OS systems and Excel versions? Will some type of compatibility pack be the answer? Thanks for the anticipated help. JR Tag: Autofilter - No. of Rows Tag: 138687
  - 14
    - RIGHT & UPPER Function help I am working in Excel 2002. The postal address is in cell A1 with the postcode. I am looking to take the postcode out of the cell and put in new cell and turn into CAPITAL letters. 1 The High Street London ln22 4tb I am struggling with the function for this. I am at =RIGHT(A1,8) which takes the postcode from the end of the address and enters ino cell A2 but I thought i may have been able to add the UPPER to the end of the function but this does not work. I can do this as =UPPER(A2) in cell A3 but was hoping to undertake the augument in the same cell. Also can I adapt the augument to include where I only have 6 characters ie LN24tb? Many thanks Roger Tag: Autofilter - No. of Rows Tag: 138685
  - 15
    - Excel 2000 opens then immediately closes Running under Vista Home Premium 64. When I open a worksheet/workbook it loads, appears on the screen then immediately closes. Have carried out 'repair', prefer not to un-instal re-install. Help! Tag: Autofilter - No. of Rows Tag: 138684
  - 16
    - dividing one time by another If I use =B1/C1 to divide one time by another (eg. 76:12 by 43:23, where the times are minutes:seconds) I get weird answers. If I reformat the cells with [m]:ss, the formula doesn't work at all. Any suggestions? Adrian Tag: Autofilter - No. of Rows Tag: 138680
  - 17
    - Open Excel File in a new Window Hi, I am not sure if this is an Excel problem or it is in Office in General. If I open two MS Word Files, they open in two separate windows. However if I open two Excel files - they open in the same Window. The benefit of having two separate windows is that they can be placed side-by-side esp. if you have multiple monitors. Is this possible in Excel?? I hope my description is clear. If not let me know and I would try to explain again. Regards, O.O. Tag: Autofilter - No. of Rows Tag: 138664
  - 18
    - Match & Index function - Cell format problem that is keeping it from Hello - I am trying to use the Match and index functions together. Typically, I have been able to match and pull in the data without a problem. But now that I am using a text file that I export into excel, I am running into problems. Basically, the formula won't work (even though I have tried trimming the data, changing all columns to text, general, and number. The only way that the formula works is when I copy and paste the actual reference cell into the reference array from the range that I am seeking to pull in the data. So for example, my formula reads: =INDEX(Sheet2!B:B,MATCH(Sheet1! A419,Sheet2!A:A,FALSE)), but the only way that I get the data from Sheet2 column B is by physically pasting Sheet1!A419 into column A in in Sheet 2. Then and only then will the values from Column B in sheet2 appear in Sheet 1-- as the formula is intended to do. Is anyone familiar with this problem? Does the function typically work better when the sheets are formatted as general, versus number, versus text? Thanks for any suggestions. (Also, another problem I am experiencing with the files----and it may be related-----is that the formulas will not work altogether. That is, any formula that I punch into the cell does not produce a value, but instead the cell shows the formula when I press enter. However, this I am usually able to fix by formatting the book as general and then retyping the formula in the cells). Tag: Autofilter - No. of Rows Tag: 138657
  - 19
    - Formula Can anyone help please with this formula. =COUNTIF(B208:AE208,"H") This works fine to count the number of H's in a row. What I need is to also count HD as .5 in the same row and add it to the final total. For instance in the first row H H HD H = 3.5. I may at a later date also need to add to it again with a S also to count 1. Can anyone help with the formula please. Maybe COUNTIF isn't the correct one to use. Thanks. Bryan. Tag: Autofilter - No. of Rows Tag: 138653
  - 20
    - Something New Excel 2003 SP2. I've been revising a workbook and came across an Excel quirk that's new to me. I haven't read about anything similar in the newsgroups. The workbook in question usually has all worksheets protected and the workbook protected, but I've unprotected the workbook and all worksheets and unhidden all but one worksheet. Some of the worksheets have embedded spaces in their names. There are no worksheet events, but there are workbook Open, Activate, Deactivate and BeforeClose events. There's also one udf, named HasFormula, that takes a Range object as its argument and returns TRUE/FALSE if the first cell in that range has a formula. I only use this udf in conditional formatting formulas. I'm editing formulas in one of the worksheets that's usually hidden. Call it Sheet C. The formulas were something like ='Sheet B'!X99 and I want to change them to =IF('Sheet B'!X99<>"",'Sheet B'!X99,'Sheet A'!C123) I edit the formula in a cell in Sheet C, inserting the 'IF(' at the beginning, copying the original cell reference in the formula bar, appending '<>"",', pasting the cell reference, and appending a comma. Then I press [F2] to change to Enter mode, repeatedly press [Ctrl]+ [PgUp] to move to 'Sheet A', move to cell C123 in that worksheet (now in Point mode), type a right parenthesis and then press [Enter]. Here's the quirk: Excel tries to enter the edited formula into 'Sheet A'!C123 rather than into the cell in Sheet C that I began editing. Fortunately, 'Sheet A'!C123 has a validation rule that rejects the formula. I should also mention that Sheet A has separate frozen panes, and C123 is in the lower, unfrozen pane. Has anyone else had Excel do this? I can work around it, so I'm not looking for an immediate fix, but this is definitely annoying. If there's an easy fix, I'd be grateful for any pointers. Tag: Autofilter - No. of Rows Tag: 138648
  - 21
    - returning more columns with vlookup? Hi all by default vlookup returns 1 column. Is there a way to return more than 1 (not necessarily adjacent) columns? example instead of =VLOOKUP(38, A2:L10, 3, FALSE) using something like =VLOOKUP(38, A2:L10, (3,7,9), FALSE) that would return cells in columns 3, 7 and 9 ? Thanks! Tag: Autofilter - No. of Rows Tag: 138627
  - 22
    - Deleting pictures Hi I have a series of pictures in a spreadsheet that I am wanting to delete but am unable to find another way than individually selecting each one and manually deleting. I DO NOT want to hold CTRL and select as there are over 600 pictures to delete. Any ideas? any help is appreciated. Andrew Tag: Autofilter - No. of Rows Tag: 138622
  - 23
    - Formula - If then else Using Excel 2002: SP2 Greetings, I have not used Excel at any depth in for several years so I am rusty with formulas, etc. I wish to set up a formula so that When column B = "NNE" column C = "N" When column B = "ENE" column C = "NE" When column B = "NNW" column C = "N" When column B = "WNW" column C = "NW" etc Basically I want to reduce 16 compass points to 8 but the groupings may be based more on preference than what logic would suggest. I have never used a VB script and don't even know how to set up one though I am an experienced programmer (HP Basic, C/C++, etc). Thanks, gtb Tag: Autofilter - No. of Rows Tag: 138621
  - 24
    - Sum of Colored Cells hi all, suppose i have a range of cells, and that range needs to summed depanding on the cells color. for example, i need to sum the cells colored with green in one cell, and the cells colored with red in another cell and so the blue ones. is there any way to do that? many thanks in advance. Tag: Autofilter - No. of Rows Tag: 138619
  - 25
    - exclude headings from sort Hi all how can I set my column headings (cells in row 1) so that they stay on top when I do sorting? Thanks! Tag: Autofilter - No. of Rows Tag: 138616
- Next
  - 1
    - Paste Special Default Most often if not always when I use Paste Special in Excel 2003, I want to use the Value option. The default is All. Is there any way I can change the default to Value? Thank you. Tag: Autofilter - No. of Rows Tag: 138612
  - 2
    - Delete Asterisk besides Numbers in a Cell Hi, I have an Excel Spreadsheet with numbers. Some of the numbers have an Asterisk besides them. E.g. 324* Is there a simple way to delete all Asterisk's in the entire spreadsheet. (There are no other Asterisk's than these unwanted ones.) If I use the Find and Replace i.e. search for * and keep the replace thing blank - it deletes the entire sheet. Any ideas? Thanks a lot. O.O. Tag: Autofilter - No. of Rows Tag: 138607
  - 3
    - slow to open files Hello, Excel 2007 files are very, very slow to open. I have saved my 2003 files to the 2007 format and they still open slowly. Has anyone else had this problem? Is there anything that can be done about it. I have noticed it on two different computers (both running Windows XP). Thanks................SBennett Tag: Autofilter - No. of Rows Tag: 138606
  - 4
    - Stopwatch in Excel I'm creating a spreadsheet for logging railway performance timings. I need a stopwatch in excel that can give a split / lap reading, preferably when the enter key is pressed. I reckon it can be done with the now() function, as this can give the needed accuracy (1/100 second) if the format hh:mm:ss.00 is used. I've not got much experience with macros etc, so I would be very grateful for any suggestions on ways to go about this. Ideally, when the split button is pressed, it will give the split time and the difference from the previous split time in a new cell, so by the end I have a list of times. I will be able to use these to calculate average speeds etc. Thanks. Tag: Autofilter - No. of Rows Tag: 138601
  - 5
    - Venturing into date formulas... Do not know if this is possible, but believe all is possible given the right guidance... Have a worksheet and shall we say in column C there is a date Jan 1, 2008 Now in column E I would like it to take the date from column C and show the number of days left in a 2 year period from the original input date of 1/1/08 So basically when you input 1/1/08 it would show say 730 in E and if you change the date in C to February 1, 2008 it would show 699. Is this possible????? Tag: Autofilter - No. of Rows Tag: 138594
  - 6
    - suggestion I have numbers in a column and an example of the numbers in one cell is 0 -000-2108-000 My problem is that I want to remove the first numbers, or hide them or sort the cell such that 2108 is in front. The bottom line is that I want to sort the column by the 2108 number and do it in an automated way rather than going 640 cells down deleting the front numbers, because I will also need them later. Any suggestions? Tag: Autofilter - No. of Rows Tag: 138591
  - 7
    - Cell search & reporting Hi Is there a formula that would search one cell for a month and then search a range of other cells for a date and then look below and report into the formula cell what is reported possibly in a different colour depending on what is reported. e.g. A B C D E F G 1 Date 31â?¦Month Decemberâ?¦Year 2007 2 3 MON TUE WED THU FRI SAT SUN 4 31 1 2 3 4 5 6 5 smith E E DO S E DO DO 6 jones DO N DO DO DO DO N 7 8 9 The cell in A9 would search cell B3 to find the month required, then would search cells A4 to G4 for the correct date e.g. recognise that the required date is the 3rd of january in cell D4. It would then look below in jones's line and report back S in red. Thanks in advance Jason Tag: Autofilter - No. of Rows Tag: 138583
  - 8
    - Saving All Documents when closing XL 2007 Hi All, I am upgrading to XL 2007 from 97 and having a problem which sounds simple but is annoying as hell. I have as many as 150 XL files open at one time (all linked to one another) and with office 97 I would exit XL and then be prompted to either save the files one at a time or "yes to all". The "yes to all" option does not appear when I follow the saem steps in 2007, is there an option somewhere where I can enable this or has this feature been dropped? Tag: Autofilter - No. of Rows Tag: 138578
  - 9
    - Arrays, Dates & Blank Cells Folks, I'm struggling to resolve this Execl formula. I've got a workbook with 2 sheets: Summary and Source Data. The workbook is used a template so that data exported from another system can be copied and pasted into the Source Data sheet and the Summary sheet will programmatically parse the data to provide the results that I am looking for. The Source Data sheet has a number of named ranges. My problem relates to the range named DATE (D2:D10000). This range contains dates formatted as "=DATE(2008,1,1)" for Jan 1, 2008 as an example. In virtually every case, there will be blank cells in the range since the data is less than 10K rows). The Summary sheet uses formulas to summarize the data contained in the Source Data sheet by month, category, and so forth. In cell $B$16, a numeral (1-12) is entered to specifiy the month for which the records in Source Data are to be summarized. I then use two formulas that examine the dates in the DATE range and provide the MIN and MAX date so the date range can be displayed. Here are the array formulas I am using to get these MIN and MAX dates: {=MIN(IF(MONTH(Date)=$B16,Date,"N/A"))} {=MAX(IF(MONTH(Date)=$B16,Date,"N/A"))} Here is my problem (assuming that the data in Source Data contains transactions from 01/01/08 through 03/06/08): When 1 is entered in $B$16: MIN returns 0-Jan-00 (INCORRECT - should be 1-Jan-08) MAX returns 31-Jan-08 (correct) When 2 is entered in $B$16: MIN returns 1-Feb-08 (correct) MAX returns 29-Feb-08 (correct) When 3 is entered in $B$16: MIN returns 1-Mar-08 (correct) MAX returns 6-Mar-08 (correct) When 4 is entered in $B$16: MIN returns 0-Jan-00 (INCORRECT - should be N/A) MAX returns 0-Jan-00 (INCORRECT - should be N/A) I think that the blank cells in the DATE range are screwing me up but I am at a loss on how to check for them and also how to report N/A when the month in $B$16 represents no transactions in the Source Data sheet. Appreciate any advice. Thanks, David Tag: Autofilter - No. of Rows Tag: 138574
  - 10
    - Determine Which Columns Are Hidden with VBA I have a spreadsheet which will be used by individuals to update an Access database. The columns are locked, but I do allow them to hide columns to make the data more customized. Periodically they will refresh their data from the database. In order to avoid forcing them to reformat to their preference (and thus encourage them to refresh more frequently) I would like to determine which columns they have hidden so I can unhide, update/append the new data, then hide the columns again. The same information would be advantageous, though less so for the autofilter properties. I have tried in vain to look at the help files and have not found the correct text with which to search in this group to find the answer. Thanks in advance if you can help. Tag: Autofilter - No. of Rows Tag: 138571
  - 11
    - Excel for mac 2008 Almost without fail, while working on a lengthy worksheet my excel crashes. Tag: Autofilter - No. of Rows Tag: 138569
  - 12
    - Cell formulas that expire after a month I have a spreadsheet that I'm using to track progress over the period of a year. One sheet is raw data that is manually entered daily and one sheet is monthly statistics based on that data. In the statistics worksheet are cell formulas calculating statistics relating to each month based on the raw data. If I change the data in March, I don't want January's or February's statistics to be changed. Is there a way to do this? Thanks, PaulH Tag: Autofilter - No. of Rows Tag: 138567
  - 13
    - Excel 2007 Conditional Formatting problem I have a conditional format that works fine on the previous version of Excel, but not so well on 2007. It is a formula condition: =AND($D$13 = "N",LEFT($D$6,2) = "AP")=TRUE D13 is the cell with the conditional format in it and D6 is a drop down of items, some of which start with "AP" and some of which start with "GL". (Basically, I'm having D13 turn red when D13 has a value of "N" and D6 starts with "AP".) It works just fine if you are changing the value of D13. It doesn't work at all if you are changing the value of D6-- UNTIL you hit F2 on D13 (or otherwise enter the edit-cell mode). Anyone have any ideas what the problem might be? Thanks. Tag: Autofilter - No. of Rows Tag: 138564
  - 14
    - Creating a Pivot Table using multiple ranges Using Excel 2007, how do you create a pivot table using multiple ranges? The option does not appear in the Create Pivot Table wizard. I thought I could use something like this: 'Page 1'!$A$6:$T$28805:'Page 2'!$A$6:$T$28805 ; but it wasn't accepted as a valid range. Thanks, Ross Tag: Autofilter - No. of Rows Tag: 138560
  - 15
    - Counting Consecutive Months in Top Third Ok. I need to have a formula which would look at a number of columns and tell me now many 'consecutive' months someone has been in the top 33% of the numbers in that column to date. IE . . .Ann was in the top 33% in Jan and Feb but not in March so in March I would want the formula to spit out '2', In April I would want it to start over again at '1' (If in fact she was in the top 33% in March, if not then it should say '0'). Hardest part in figuring this out (in my head anyway) is how to get it to give me the number as of the current date. Figures =NON() would have to be in there somewhere but not sure how. Hope I explained all that properly. JAN FEB MAR Jane 34.5 44.6 77.8 Bob 22.3 12.5 34.6 Bill 32.6 87.7 44.4 Jill 44.3 66.5 34.8 Ann 87.6 55.2 15.6 Eric 74.5 33.3 13.8 Jim 44.7 22.8 22.3 Lisa 56.4 55.4 55.9 Sam 67.1 64.8 70.0 Tag: Autofilter - No. of Rows Tag: 138553
  - 16
    - pivot table percentages Using XP and Office 2003 I created a pivot table to analyze a group of email address extensions. (".com" or ".org" or ".gov", etc.) The pivot table returned the count of each extension type and the grand total. Now I want to create an additional column that will calculate the percentage of the total for each extension type. I built the first formula at the end of the first row of data in my pivot table. The problem I am having comes when I try to copy that formula down or even when I try to copy and paste special that formula. I do not understand the way the formula displays in the first cell where I built it, so I don't know how to correct the formula for ease of copying. Anyone know what I'm talking about?? Tag: Autofilter - No. of Rows Tag: 138546
  - 17
    - excel excel Tag: Autofilter - No. of Rows Tag: 138544
  - 18
    - Macros All, I have a template that I put together a marco in. Whenever I send it to my colleage the macro does not work. How do I make the macro travel with the template or gets copied whenever I copy the template into other drives. Thanks. Tag: Autofilter - No. of Rows Tag: 138533
  - 19
    - Formula for Exam Marksheet Hi to all, If in ANY of these cells (F7,H7,J7,L7,N7,P7,R7,T7,V7,X7,Z7,AB7,AD7,AF7,AH7) contains the letter T,P,W,Wn or TH (any of these), the result in cell AJ7 would be the letter X. What is the formula? I appreciate your kind help. Thank you. Tag: Autofilter - No. of Rows Tag: 138531
  - 20
    - #VALUE I have a simple spreadsheet for tracking an account balance. Column X is withdrawals, Y is deposits and Z is balance. An entry in Z25 would be =Z24-X25+Y25 Since line 25 will contain either a withdrawal or a deposit, one of the cells in X or Y of line 25 will be empty, and the contents of that empty cell will (supposedly) be assumed to be zero. The spreadsheet has been working well and returning the values anticipated until recently, when the cell in Z has been displaying #VALUE. I can get the correct result by entering a zero in the empty cell in column X or Y. However, this is a new development, something not necessary before. Obviously something has changed in the way the value of the empty cell is being determined, but I am unable to determine what it is. All three columns have been formatted as number with two decimal digits and a comma thousands separator.. -- Live simply, love generously Tag: Autofilter - No. of Rows Tag: 138526
  - 21
    - Change lookup range depending on result of 2nd lookup Hi, am trying to vary the name of the lookup range depending o the result of a 2nd lookup -- NYC =vlookup(A1,range1,4,0) LA =vlookup(B1,range2,4,0) . . . SFO =vlookup(C1,rangeXXX,4,0) Rather than having to manually edit the ranges I would prefer excel to lookup the ranges by something like NYC =vlookup(A1,vlookup(A1,RangeTable,2,0),4,0) RangeTable: NYC range1 LA range2 SFO rangeXXX Have tried to nest two vlookups as above but am having no luck Any suggestions? Thanks, sab_ya Tag: Autofilter - No. of Rows Tag: 138523
  - 22
    - I need Excel Help I have another question about my project that I don't understand, if anyone could help that would be awesome! consultants who work on a project also receive a bonus if the fees exceed estimates, otherwise the consultant received no bonus. Create one formula that calculates the bonus by referencing the appropriate bonus rate in the table given by the number of hours the consultant worked. Does this make sense to anyone? I really need help. Tag: Autofilter - No. of Rows Tag: 138517
  - 23
    - IF Function Help I have a project that requires me to calculate bonuses for employees. I am pretty sure that I need to use an IF function. Here is the question: If the project fees do not exceed estimates the manager receives an 100 bonus. Algebraically, this is Bonus=(actual-projected)*bonus rate if (actual-projected)>0, 100 otherwise. Create a formula that calculates the project manager bonus. This is a completely different language that I just can't seem to decipher. If anyone can help, that would be great! Tag: Autofilter - No. of Rows Tag: 138516
  - 24
    - 2002 Symbol Insert Problem Hi, Using Excel 2002 under WinXP Pro SP2: I wanted to insert a Wingdings symbol, but was unable, because the font list only showed fonts from "A" to "S". Since Wingdings starts with "W", I was unable to use the insert symbol Wingdings. The fonts from "A" to "Z" are "ALL" available when typing standard entries in Excel. All the fonts are also available in Word 2002, both in regular entry and using the symbol insert. Ditto in Publisher and PowerPoint, able to use insert symbol Windings. So, it looks like something happened to Excel Insert Symbol that's just showing fonts from "A" to "S". Fonts "T" to "Z" are not available. Is this a known issue with Excel 2002 and is there a fix? Thanks, Mark Tag: Autofilter - No. of Rows Tag: 138513
  - 25
    - not able to print from line a4 The following code ends in print preview starting line 7 and not line 4. any suggestions please. I changed A7 to A4 in the with statement and still the same problem. Sub Picture22_Click() Dim c As Range Application.ScreenUpdating = False With ActiveSheet.Range("A7:A159") Do Set c = .Find("", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Hidden = True Loop .Range("A4:D160").PrintPreview .EntireRow.Hidden = False End With Application.ScreenUpdating = True End Sub Tag: Autofilter - No. of Rows Tag: 138503

Does anyone know of any way to increase the number of rows displayed in
Autofilter, becauase when working on large ranges it doesn't display all
values.

Any help greatly appreciated.....

Re: Autofilter - No. of Rows by Pete_UK

Pete_UK
Mon Mar 10 08:57:30 CDT 2008

There is a limit on what can be displayed in the filter pull-down of
1000 items. However, you can still apply filters to values that can
not be seen. Debra Dalgelish gives some techniques here:

http://www.contextures.com/xlautofilter02.html#Limits

Hope this helps.

Pete

On Mar 10, 1:39=A0pm, "Me!" <du...@dummy.dummy> wrote:
> Does anyone know of any way to increase the number of rows displayed in
> Autofilter, becauase when working on large ranges it doesn't display all
> values.
>
> Any help greatly appreciated.....

Re: Autofilter - No. of Rows by Alan

Alan
Mon Mar 10 08:58:56 CDT 2008

The limit is 1000 rows (in 2003 anyway, not sure about 2007), and I don't
think it can be increased. There are some good work rounds here,
http://contextures.com/xlautofilter02.html#Limits
Regards,
Alan.
"Me!" <dummy@dummy.dummy> wrote in message
news:ejSKeQrgIHA.5280@TK2MSFTNGP02.phx.gbl...
> Does anyone know of any way to increase the number of rows displayed in
> Autofilter, becauase when working on large ranges it doesn't display all
> values.
>
> Any help greatly appreciated.....
>

Re: Autofilter - No. of Rows by Me!

Me!
Mon Mar 10 11:38:34 CDT 2008

Thanks guys...Most appreciated,

"Alan" <alan111@ntlworld.com> wrote in message
news:eEkgsbrgIHA.2268@TK2MSFTNGP02.phx.gbl...
> The limit is 1000 rows (in 2003 anyway, not sure about 2007), and I don't
> think it can be increased. There are some good work rounds here,
> http://contextures.com/xlautofilter02.html#Limits
> Regards,
> Alan.
> "Me!" <dummy@dummy.dummy> wrote in message
> news:ejSKeQrgIHA.5280@TK2MSFTNGP02.phx.gbl...
>> Does anyone know of any way to increase the number of rows displayed in
>> Autofilter, becauase when working on large ranges it doesn't display all
>> values.
>>
>> Any help greatly appreciated.....
>>
>