formulas in cells change when using filter function

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Misc
- 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
    - Circular References I have a csv file saved in 2003 on a laptop. The laptop is the office laptop and when I am home, I access my home network from that laptop using a wireless that is, in a sense, in a DMZ for a Small Business Server network. I log into the home network using an RDC over VPN. The VPN is established from the wireless in my home which runs on a separate network. Sorry, I know that doesn't belong in this question about circular references but I don't want a simple answer here. I created a csv file on the laptop and then copied it over to the Network machine where I tried to open it in Office 2007 Excel. When I open it, there are about 1000 circular references. Obviously too many to fix. When I open the same file on the laptop in Office 2003 there are no circular references. Meanwhile, I noticed that my firewall (Zone Alarm) went into a total shutdown. I have never seen Zone Alarm fail by itself. Using the system will crash around it. Guess Zone Alarm is gettting better. Question is, can I remove the 1000 or so Circular References easily? I can do them one at a time by reading the information, deleting it, moving to another cell then moving back and reentering the information in the cell. But I have no intention of doing it a thousand times or more. Also, why would Excel show that the file itself was locked down by the other machine where it was copied from even though the file was not open on that machine? There was a message box stating that I could receive a notification as soon as the file was released. I went back to the other machine and even though the file was closed, EXCEL still showed up in the processes (Task Bar). When I killed the EXCEL process, the notification came on to tell me the file was release. So this brings me to another question... is Zone Alarm locking down the EXCEL file when it gets copied over from one machine to another? It would do that if it were tracking the IP and the network access for trust. Trust exists for the hone Small Business server network. It does not exist for the wireless LAN that the laptop is on. How can EXCEL possibly have become this complicated to run? What do I do? Have to reboot now... Zone Alarm is mad at my machine. -- Regards, Jamie Tag: formulas in cells change when using filter function Tag: 770377
  - 2
    - Macro in WB assigned to toolbar, after "save as" points to differn Let me begin by saying I'm not a programmer (any more), I'm a Spanish interpreter at a Court. I have done all of the following using the "macro record" and "customize toolbar" features from the Excel menus - I do not do any VB programming. I have created an Excel 2003 spreadsheet, running in Windows XP, that contains a simple macro, stored in the workbook itself, to do some find and replace commands. Let's call the worksheet "Master_file.xls". Let's call the macro "My_macro" The macro is in the spreadsheet. I created a custom toolbar (let's call it "smiley_face"), attached to this spreadsheet, and assigned the macro to the toolbar. The name of the assigned macro appears as My_macro. It does not indicate any excel file name. I made the spreadsheet read-only - I want to be sure my end-users at other Courts canâ??t mess it up. Hereâ??s the problem: My users paste data into the spreadsheet, press the smiley face button, (which runs My_macro) and then "save as". It's read-only so they rename it as "Saved_File". The next time my user opens "Master_File", pastes data, and then presses the "smiley face" button to run the macro, Excel opens "Saved_File" to get the macro. The name of the macro assigned to the toolbar now says 'Saved_File'!My_macro When the user tries to do a save as "Saved_File", excel says it can't save, since that file is already open (since it opened it to get the macro). They have to close â??Saved_Fileâ?? first, then "save as" again, and they are confused about what happened. Sometimes they wind up not saving anything. Iâ??m perplexed about why the macro address pointer assigned to the toolbar changes to the file name last saved. I tried hard-coding the name into the original worksheet, but that doesnâ??t seem to make any difference. I would like the macro to always run from â??Master_Fileâ??, rather than from the file last saved. Can I do this without coding, or by just editing the existing macro code in the VB editor? Or what are my options? Hereâ??s the macro code: Sub Replace_Chars() ' ' Replace_Chars Macro ' Macro recorded 8/31/2007 by Maintenance ' Cells.Replace What:="@", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Cells.Replace What:="~*F-", Replacement:="", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub Thanks a lot! Activoz_Interpreting Tag: formulas in cells change when using filter function Tag: 770373
  - 3
    - COMPATIBILITY REPORT DETAIL How can you find the compatibility issues with a prior verison of excell when using excell 2007 particularly formulas linked to closed workbooks Tag: formulas in cells change when using filter function Tag: 770372
  - 4
    - How do I copy the content of multiple cell in excel How do I copy the content of multiple cell in excel. (instead of entering the edit line of every cell, copy the text and pasting) Tag: formulas in cells change when using filter function Tag: 770368
  - 5
    - Excel Formula Question I am in need of some help or guidance with a spread sheet I am making that will subtract and add daily #2 Fuel oil usage for my plant. I can not seem to come up with a formula that will add gallons and inches of fuel back in to the row should I receive a oil delivery during the month. Ex: On the first day of the month i have 70 inches of oil that is multiplied by 126 totaling 8,820 gallons. On day 2 I have 66 inches of oil for a total of 8,316 gallons and so on. Say on day 13 i am down to 50 inches and i receive 3,780 gallons of oil it will not add back in my rows to calculate what i just received. I do have a copy of the spreadsheet that I would be happy to send to any one if they could contact me via e-mail with any suggestions at maineff@aol.com Tag: formulas in cells change when using filter function Tag: 770367
  - 6
    - Syntax problem with code Below is a macro I am testing. I am trying to create 26 command buttons, starting with Commandbutton6, but a caption starting with number 1. Something is wrong with the "Activesheet.OLEObject" line and I don't know what. Private Sub CommandButton2_Click() Dim h, w, t, l As Long Dim k As Integer h = 21 w = 91.5 l = 50 t = 92 For k = 1 To 26 ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton." & k + 5, Left = l, Top = t, Width = w, Height = h) .Object.Caption = "Button " & k + 5 t = t + 38 If k = 7 Or k = 14 Or k = 20 Then l = l + 145 t = 92 End If Next k End Sub Can someone tell me what is wrong? Thanks One other question. I have seen the syntax similar to what I show below. If using this syntax, what is "newbutton" DIM as? Set newbutton = Applicationsheet.OLEObject.Add(Forms.CommandButton....) Thanks, Les Tag: formulas in cells change when using filter function Tag: 770364
  - 7
    - Run macro only if Field E10 = "WO ID" and AB10 = "Spec Sizing" hello all. I want to run a macro only: if Field E10 = "WO ID" and AB10 = "Spec Sizing" if field are different then it must show a message saying that the file is not the correct one. please help Thanks -- Lorenzo DÃaz Cad Technician Tag: formulas in cells change when using filter function Tag: 770356
  - 8
    - Nesting COUNTIF In Excel 2003, is there a way to combine the functions COUNTIF(A1:A10,"Joe") and COUNTIF(B1:B10,">5")? In other words, the array in Cols A and B has scores from 0 to 10 for multiple trials by Joe, Bill, and Jane; I want a count of all of Joe's scores that are over 5. Tag: formulas in cells change when using filter function Tag: 770355
  - 9
    - Nested "If Statement" I am trying to write a "Nested If Statement". The purpose is to pick the correct grade if it fails within a cetain range. I have dont everything write. However, I discovered that I cant write more than 7 statements. The grading system is more than 10 grades. Please help.. Thank you EZZAT Tag: formulas in cells change when using filter function Tag: 770354
  - 10
    - Copy a cell based on check box answer I have two fold questions. Would someone tell me how I can add Check box in Excel? I need to copy content of two other cells to a summary page if the box is checked on the original spreadsheet.. any idea? Tag: formulas in cells change when using filter function Tag: 770352
  - 11
    - Text to Columns Question I have text in a column that is similar to this: "Last Name <two spaces> firstname<Space>Initial" and possibly a spouse name and initial on the end of that. My goal is to get the last name in a separate column. I want to use the text to columns features but I can't do fixed width because names are different lengths and if I use the <space> delimiter it separates everything (last, first, initial, spouses name, Initial). What would work is if I could set the delimiter to a double space but as far as I know excel only excepts one character delimiters. I'd like to be able to do this without complicated formulas but am really looking for any solutions availible. Thanks for reading, Ben Tag: formulas in cells change when using filter function Tag: 770351
  - 12
    - print a multi paged excel spreadsheet width onto one page i use an excel spreadsheet for accounting purposes but it goes from approx a to o i need to print this on one page in width how do you do this. Tag: formulas in cells change when using filter function Tag: 770349
  - 13
    - .jpg file inserted in Excel 2003 file does not apear Appearance of .jpg file that was inserted in an Excel 2003 file is inconsistent once the file is emailed. Once the file is emailed and opened with Excel 2002, and on another computer network using Excel 2003, the .jpg image just appears as a box and inside the box text reads "An Image". Tag: formulas in cells change when using filter function Tag: 770339
  - 14
    - Eliminate #VALUE! in Formula Result =FIND("/",C30) If the above formula does not find the slash #VALUE! is returned. How do I eliminate that result and obtain a number? I am attempting to setup a telephone directory for a condominium association. I am attempting to obtain TEXT following the slash using the MID function. Most of the text I am searching does not contain a slash. If anyone has a template that might assist me in this endeavor, it would be greatly appreciated. Tag: formulas in cells change when using filter function Tag: 770337
  - 15
    - Changing part of a formula Hello I have a =sum formula: =SUM('C:\Documents and Settings\Andrew\Desktop\CHK2 minaturisation\[03_Dec07_0243.txt]03_Dec07_0243'!$B$119:$M$126) that totals the values of an 12 by 8 range. I would like to have a column of the variables 0243 0244 0245 0246 so I can get the total of each of these data files in the next column. Could someone suggest an approach? Many thanks Andrew Tag: formulas in cells change when using filter function Tag: 770334
  - 16
    - Column headings The column headings in an Excel spreadsheet switched from letters (A,B,C, etc.) to numbers (1,2,3, etc.) This happened in only one of my spreadsheets. Why did this happen, and how can I change the columns back to letters? Tag: formulas in cells change when using filter function Tag: 770326
  - 17
    - Counting letters in a cell My formula is =LEN(A1) and copied down. Is there a variation of this formula to count just the letters in each cell without counting the spaces between words ? Thanks for the reply. -- Socrates said: I only know, I don''''''''t know nothing. I say : I don''''''''t even know, I don''''''''t know nothing. Tag: formulas in cells change when using filter function Tag: 770325
  - 18
    - How to create Excel Form in Excel 2003 Because of the limited fields for a Data Form, it appears that I have to create an Excel form. I cannot find a source for how to do this. I've set up the entry info on one tab, have the fields for the data on another tab. What do I need to do to be able to move from record to record in the entry tab and do other things similar to an Access form? -- TDay Tag: formulas in cells change when using filter function Tag: 770323
  - 19
    - Excel-How to get sheet taskbar and toolbar at same time on page When I minimize the ribbon I lose the top header info and full ribbon deletes the sheet taskbar or toolbar. Can you help -- Roger Estelle Tag: formulas in cells change when using filter function Tag: 770322
  - 20
    - Problem with code I have the following code below. I have many more textboxes but this code has been made short. The data which the userform gets its information is on two worksheets: Sheet1= customers Sheet5= cuatomers2 The problem I am having is that it will not recall, to the textboxes, the information in "Sheet5". I hope I have explained it, any help would be grateful. Option Explicit Private Sub CommandButton1_Click() Dim LastRow As Object Application.EnableEvents = False Set LastRow = Sheet2.Range("a100").End(xlUp) LastRow.Offset(1, 0).Value = TextBox1.Text LastRow.Offset(1, 1).Value = TextBox2.Text If vbYes Then TextBox1.Text = "" TextBox2.Text = "" ComboBox1.Text = "" ComboBox2.Text = "" Else End If Set LastRow = Sheet5.Range("a100").End(xlUp) LastRow.Offset(1, 0).Value = TextBox3.Text LastRow.Offset(1, 1).Value = TextBox4.Text MsgBox "Do you want to enter another record?", vbYesNo If vbYes Then TextBox3.Text = "" TextBox4.Text = "" ComboBox3.Text = "" ComboBox4.Text = "" TextBox1.SetFocus Application.EnableEvents = True Call CommandButton99_Click Else End If End Sub Private Sub CommandButton2_Click() Dim FoundCell As Range Application.EnableEvents = False If Me.ComboBox1.ListIndex = -1 Then 'nothing filled in Beep Exit Sub End If With Worksheets("customers").Range("A:A") Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _ After:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) End With If FoundCell Is Nothing Then 'this shouldn't happen! Beep Else Me.TextBox1.Value = FoundCell.Offset(0, 0).Value If IsDate(FoundCell.Offset(0, 1).Value) Then Me.TextBox2.Value = Format(FoundCell.Offset(0, 1).Value, "dd-mmm-yy") Else Me.TextBox1.Value = "" Me.TextBox2.Value = "" End If Me.ComboBox1.Value = FoundCell.Offset(0, 9).Value Me.ComboBox2.Value = FoundCell.Offset(0, 13).Value End If With Worksheets("customers2").Range("A:A") Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _ After:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) End With If FoundCell Is Nothing Then 'this shouldn't happen! Beep Else Me.TextBox3.Value = FoundCell.Offset(0, 2).Value If IsDate(FoundCell.Offset(0, 1).Value) Then Me.TextBox4.Value = Format(FoundCell.Offset(0, 1).Value, "dd-mmm-yy") Else Me.TextBox3.Value = "" Me.TextBox4.Value = "" End If Me.ComboBox3.Value = FoundCell.Offset(0, 9).Value Me.ComboBox4.Value = FoundCell.Offset(0, 13).Value Application.EnableEvents = True End If End Sub Private Sub CommandButton99_Click() Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet For Each wks In Worksheets(Array("customers", "customers2")) With wks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = FirstRow To LastRow Step 1 If Application.CountIf(.Range("a1").EntireColumn, _ .Cells(iRow, "A").Value) > 1 Then 'it's a duplicate .Rows(iRow).Delete End If Next iRow End With Next wks Unload Me End Sub Private Sub CommandButton3_Click() ' keybd_event VK_SNAPSHOT, 0, 0, 0 DoEvents keybd_event VK_LMENU, 0, _ KEYEVENTF_EXTENDEDKEY, 0 ' key down keybd_event VK_SNAPSHOT, 0, _ KEYEVENTF_EXTENDEDKEY, 0 keybd_event VK_SNAPSHOT, 0, _ KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0 keybd_event VK_LMENU, 0, _ KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0 DoEvents Workbooks.Add Application.Wait Now + TimeValue("00:00:01") ActiveSheet.PasteSpecial Format:="Bitmap", _ Link:=False, DisplayAsIcon:=False ActiveSheet.Range("A1").Select ActiveSheet.PageSetup.Orientation = xlLandscape ActiveSheet.PageSetup.Zoom = 80 ActiveWindow.SelectedSheets.PrintOut Copies:=1 ActiveWorkbook.Close False End Sub Private Sub CommandButton4_Click() UserForm7.Show End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Call CommandButton99_Click End If End Sub Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) TextBox1.Value = UCase(Me.TextBox1.Value) End Sub Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) TextBox2.Value = Format(TextBox2.Value, "dd-mmm-yy") End Sub Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) TextBox1.Value = UCase(Me.TextBox1.Value) End Sub Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) TextBox2.Value = Format(TextBox2.Value, "dd-mmm-yy") End Sub -- kk Tag: formulas in cells change when using filter function Tag: 770321
  - 21
    - Index vs % What's the difference between an index and percentage growth/decline? Example: Last year's volume was 158 vs 2006's volume of 97. If I divide 158 / 97 I get 162.9. Isn't that the same as 62.9%? Why would I use one over the other and if I decide I don't want it to read 162.9, just 62.9, how do I get that to happen? Thanks. Tag: formulas in cells change when using filter function Tag: 770320
  - 22
    - Calender Control I have added Calender Control 10.0 as an add-in. I want to allow the user to use a calender to select a date for input via Inputbox. Once the date is selected and entered, a macro is going to use the date. I have no clue how to activate/use the calender control. How is this accomplished? Thanks, Les Tag: formulas in cells change when using filter function Tag: 770312
  - 23
    - last non-empty cells is there a formula for displaying the last non-empty cell or the last used cell in a column or row? if there is kindly notify me at donald_dick13@yahoo.com. i needed it badly. Tag: formulas in cells change when using filter function Tag: 770306
  - 24
    - copying conditional formatting Hi, I have a row of 5 cells. I want to format them so that when two of the 5 cells are >0 the whole set of cells change color. I used the countif function to do this and it works fine. I now want to copy this conditional format for all 5 cells down the other 50 rows. I have tried the usual paste, paste special, but these do not work. Any help would be appreciated Thank you David Williams Tag: formulas in cells change when using filter function Tag: 770304
  - 25
    - Searching in 'variable' worksheets Hi, I'm a quite amatuer in Excel so I need a help in the following: I developed a spreadsheet with several worksheets named each by months of year. Each monthly sheet contains datas in same structures - they are daily production figures, losses etc. Now I want to make a separate worksheet which extracts these datas from any of the monthly sheets for a determined date - making a Daily Production Report. I guess the right function will be the VLOOKUP - I have no problem with using this function. But how can I define that it searches in the right monthly sheet? Secondly how can be this DailyProductionReport worksheet (only this) be sent to pre-defined mail-recipients? Any help would be much appreciated. FicsiPapa Tag: formulas in cells change when using filter function Tag: 770298
- Next
  - 1
    - Partitioning Excel cell contents into separate cells I have acquired a large spreadsheet with a column of cells each of whose contents are like [1.234 6.712 5.436] Numbers like these, contained in square brackets, are from another application. How do I extract each number and place them in cells in adjoining columns, to get: column 1 column 2 column 3 1.234 6.712 5.436 ? Tag: formulas in cells change when using filter function Tag: 770297
  - 2
    - New Macros Not Being Listed In Macro Dialog Box? I inserted in a NEW workbook a Chip's Macro presented below; Then I also saved the file; Closed & reopened the same; But upon Alt+F8 the macro name is not found to be listed in the Alt+F8 dialog Box nor can be run from VBA editor by Alt+F5? Why??? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim DateStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count > 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Formula) Case 4 ' e.g., 9298 = 2-Sep-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2) Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2) Case 6 ' e.g., 090298 = 2-Sep-1998 DateStr = Left(.Formula, 2) & "/" & _ Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2) Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4) Case 8 ' e.g., 09021998 = 2-Sep-1998 DateStr = Left(.Formula, 2) & "/" & _ Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4) Case Else Err.Raise 0 End Select .Formula = DateValue(DateStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid date." Application.EnableEvents = True End Sub -- Best Regards, FARAZ A. QURESHI Tag: formulas in cells change when using filter function Tag: 770291
  - 3
    - Pivot Table Calculation Hi there I have a worksheet which contains a list of the products we analyse each month and columns for the date the product was submitted for testing and the date the product was released from testing. I also have a cycle time column which is the number of days it took for the product to be released from the date it was submitted. I have created a pivot table from this data so I can look at individual (or groups) products to see how many were released each month (count of release month) and what the average cycle time for the testing was each month. What I need now is to calculate the average cycle time per product per month to use as a baseline however when I add a calculated field to the pivot table and create the formula "average cycle time"/"count release month" I get incorrect figures. For example, "average cycle time" = 8 days "count release month" = 4 products which should give me average cycle time / product of 2 days - I get something like 0.004. Can someone tell me what I might be doing wrong or whether it's possible to do this calculation in a pivot table? Thank you, Silena Tag: formulas in cells change when using filter function Tag: 770289
  - 4
    - MATCH & INDEX ISSUE? I have a table with agents in column A, with various rates for various services in Columns B-F. I want to look up agent X and service Y and have the cell return the correct rate. Pretty simple, but I'm stumped! HELP! Tag: formulas in cells change when using filter function Tag: 770283
  - 5
    - Can you determine if hyperlink points to empty folder? Our little cabana club uses Excel for our a photo id system. Each member household has a folder that contains photos for their family members. One column of the spreadsheet contains a hyperlink to each folder. i am trying to do something that requires me to know which folders are empty, and which folders contain at least one picture. Currently i am keeping track of this manually (putting an X the column next to the hyperlink if the folder is not empty.) This is time consuming and only as accurate as the last time i update this column. Also, it is possible i might put an X where it doesn't belong or omit one that does. So i was wondering if there was a way to automate this. Is there any function or macro, etc. that would automatically determine if the hyperlink points to an empty folder or not? -- Thanks for the help - Denise Tag: formulas in cells change when using filter function Tag: 770278
  - 6
    - Watermark Causing Print Problems I added a watermark to an Excel 2007 doc. Now, when I print, the last page doesn't show any text. It's there in print preview, but when I print, I only get the header (where the watermark is), and the footer. This is only an issue with the LAST page. The rest print just fine. I can remove the watermark and print the last page and the text shows up. But I NEED the watermark on all pages. Any suggestions would be greatly appreciated, I've been trying to fix this for 7 hours! Tag: formulas in cells change when using filter function Tag: 770272
  - 7
    - CONCATENATE Question I have two colums one with a first name and one with a last name I want to CONCATENATE the first name and last name together, but I only want the first initial of the first name and then the full last name to upload to a database for users, is there any way in access for this to be accomplished. Thanks Tag: formulas in cells change when using filter function Tag: 770271
  - 8
    - time stamp question Ok i figured out how to do a time stamp, but know i am trying to format the rule that JE McGimpsey provides in his website. So that the date stamp does not appear in next cell but in another cell. Below is a link with the example of the rule. http://www.mcgimpsey.com/excel/timestamp.html Thanks Tag: formulas in cells change when using filter function Tag: 770270
  - 9
    - Sorting Data Advice please Assuming I have 20 Columns or more with headers. Column C has a date field, and I have define a name for it "Date" relevant to each row. I want to sort all the rows in date order. Question 1 Dragging the mouse over a large number of colums an rows could be tedious. What is the quickest way to sort the rows in date order automatically without having to select the cell range everytime a new row of data are entered? Tag: formulas in cells change when using filter function Tag: 770269
  - 10
    - Name Conflict I finally resolved the problem I had opeing a file. In the "Name Conflict" box, it says, "name cannot be the same as a built-in name". Then it gave me the "old name" and a box to enter the "new name". The old name was "Print_Titles", after trying every name under the sun, I simply omitted the "_" (under score) and entered the new name as "printtitles". The file opened. I have no idea where the name "Print Titles" came from and no idea what "built-in name" means, but the file opened. -- Roger Tag: formulas in cells change when using filter function Tag: 770260
  - 11
    - conditional formating Hi there I am created a file to keep track of marks. I want every other line to have the back ground fill in. Sometimes students names will be added or deleted. I don't want to have to change the format for each line when a student is added or deleted. Is there a way to do this? -- Thank-you! Ruth Tag: formulas in cells change when using filter function Tag: 770259
  - 12
    - file format question I've created a worksheet for our group to use. However, one of our members has quattro pro. Is there any way to convert to a format that she can use without losing my formulas and such? Thanks. Tag: formulas in cells change when using filter function Tag: 770250
  - 13
    - 2D graph with label I'm trying to produce the following graph Program Name Long Leverage Gross M1 23.06% 80.00% M2 23.86% 96.67% M3 25.27% 134.00% M4 25.44% 118.30% M5 27.87% 128.85% M6 28.88% 132.00% M7 32.33% 247.10% M8 36.46% 152.60% M9 42.50% 45.61% M10 45.72% 160.25% I want Long on the X axis, Leverage Gross on the Y axis and for each point (X,Y) I want the Program Name tab next to it. Everything in a scatter plot. Anybody knows how to do that? Thank you much. Tag: formulas in cells change when using filter function Tag: 770249
  - 14
    - delete items How do you delete saved items from under recent Documents when you save as it keeps on recent documents how do you delete them Thanks Tag: formulas in cells change when using filter function Tag: 770245
  - 15
    - How to open a secured Access db from Excel Hello guys I have been trying to find a solution for this problem in the last 3 days. I have a secured Access database. This db contains a Macro that i will like to run from Excel. I need the code to run the Macro from Excel. Any ideas? Thanks in advance Tag: formulas in cells change when using filter function Tag: 770244
  - 16
    - Inserting Hyperlinks I need to insert hyperlinks into excel aiming at movies on my HDD. (Don't ask, its a favour for someone) Which works fine, until I save and re-open the excel file. It appears to be because excel shortens the path and therefore breaks the link. I have tried the Properties, Summary and Hyperlink base fix but find this doesn't work for me. The 'Cannot find specified file' error persists. I have also used =HYPERLINK function with no luck either. How can I stop exel shortening the path? Andrew Tag: formulas in cells change when using filter function Tag: 770241
  - 17
    - Variable range column summation and averaging Excel 2003 SP3 WinXP Pro SP2 Header in Row 1 Data starts in Row 2 I'd like to sum [SUM($Fx:$Fy)] a range of cells, n, in a column and obtain their averages [SUM($Fx:$Fy)/n]. My issue is that I'd like to vary the 'n' (cell range) by column so as to compare the fitness of any particular 'n' to another relative to my data and datapoints. So, I've manually setup 'n=4' and 'n=20' and 'n=21'. For n=20, each time I need to manually count the starting point for the summation because the previous 20 cells are blank. Then I need to change each column's 'x and y' [start and end rows] to reflect the new range. How can I setup the formula for the averages so I can just reference a value in, say, Row 1 or 2? Hence, I could have Row 2, Column F be '4' which would then propagate the general formula to start displaying at $F6 for the averages of SUM($F3:$F6)/4? The formula would need to know to start at Row 3 and continue adding through Row 6 and then divide by 4. (Note: Row '6' less Row '3' = 3 then add 1 and I get the value of 4 for the range, if you understand what I'm trying to say here.) I need the logic/formula for the IF(...) and the establish how to do the ROW starting and ENDING points relative to the quantity of datapoints to average. Maybe I'm not too sure how to test what ROW I'm on..... THANKS!!! Tom Tag: formulas in cells change when using filter function Tag: 770238
  - 18
    - Help I have a workbook with numerous worksheets in it. Each work sheet has information down colum A. In the first Worksheet i want it to gather all the information in colum A of each persons Sheet. For example. In the main worksheet it will say Employee1!$A$1 in Row 2 Column B. What i am trying to do is copy that formula, but I dont want to have to change the names. Is there a function code that i can do so that it will automatically change the names? I dont know if this makes sense, but any help would be appreciated. Tag: formulas in cells change when using filter function Tag: 770235
  - 19
    - Reference every 13th cell from another tab. Is there a formula that will reference a cell in a seperate tab for every 13th cell. For example: In cell R7 it looks at cell =+Equity!E166. Cell S7 needs to be 13 rows below =+Equity!E166... So, R7 equals =+Equity!E166 S7 equals =+Equity!E179 T7 equals =+Equity!E192 I need to repeat this for multiple colums. Tag: formulas in cells change when using filter function Tag: 770232
  - 20
    - Bi-weekly date formatting To define a payroll bi-weekly payperiod. ie. 02/24/08 - 03/08/08 Want to enter one date in cell C4. say 03/14/08 I know that the formula =C4-19 will get the start date 02/24/08. I know that the formula =C4-6 will get the ending date 03/08/08. How do you format the two expressions together (with a dashed between) into a single cell: 02/24/08 - 03/01/08 ? Thanks Tag: formulas in cells change when using filter function Tag: 770224
  - 21
    - Changing Columns and Rows around to sort Excel 2003 - SP2 I have a spreadsheet given to me by a co-worker. Example: A1 B1 Name John Title HR Department HR This goes down about 1500 rows. 300 some people are listed. Is there an easy way to have the Column become a row. Example: A1 B1 C1 Name Title Department John HR HR Joe IT IT If I could take this sheet and do that it would make life so much easier and I could sort by it. Is there a good way of doing this? Tag: formulas in cells change when using filter function Tag: 770216
  - 22
    - How to sort date columns? I have a spreadsheet with the following column headings: Name Occupation Start Date Termination Date The issue that I'm having is that I'm trying to sort by the termination date. Not all employees have a termination date so therefore their row is blank. I need excel to be able to sort by termination date but I also need it to place all people that have a termination date at the bottom of the spreadsheet. I tried to sort by the termination date but it's doing the opposite of what I would like. It places all that have a date at the top followed by the blanks. Is there something that I can do to solve this issue? Thanks for your help Tag: formulas in cells change when using filter function Tag: 770213
  - 23
    - how to automatically add row column headings to inserted table? I have inserted tables into Word and/or Outlook. I would like to "turn on" the Row and Column headers so that the table is referenced. Tag: formulas in cells change when using filter function Tag: 770211
  - 24
    - Using data from multiple workbooks to generate a report How can I automate collecting information from 30+ workbooks that all have the same format (i.e. Cell B25 in all workbooks has the same type of information) and generating a report or another spreadsheet from the collected information. For example, all of my workbooks contain information about equipment at 30 different locations. I would like to query all of the workbooks and get a list of what is contained in Excel Cell B25. Tag: formulas in cells change when using filter function Tag: 770207
  - 25
    - How do I send email addresse from excel spredsheet to aol contact Please help Tag: formulas in cells change when using filter function Tag: 770202

column L uses a 'countif' formula to determine the number of times a specific
# appears in column F. after formulas were entered, i filtered the data, and
the range for many of those formulas changed. how do i fix this?

Top

Re: formulas in cells change when using filter function by Tyro

Tyro
Sat Mar 15 18:34:06 CDT 2008

How do the formulas change? Can you show an example? I'm a very poor mind
reader

Tyro

"hoosier data" <hoosier data@discussions.microsoft.com> wrote in message
news:780FABA4-992E-4026-A7FE-EC8727585EB5@microsoft.com...
> column L uses a 'countif' formula to determine the number of times a
> specific
> # appears in column F. after formulas were entered, i filtered the data,
> and
> the range for many of those formulas changed. how do i fix this?

Top

Re: formulas in cells change when using filter function by hoosierdata

hoosierdata
Sun Mar 16 09:23:00 CDT 2008

sorry; in col L, the countif statement reads: countif(f2:f189,"some specific
#") - where i'm looking for the number of times a specific # appears in col F.
The formulas in this column never changed until i filtered data, and now
when I go back, there are several iterations of it; countif(f6:f189,"spec #")
countif(f18:f189,"spec #")
countif(f23:f189,"spec #"), etc
does that help?

"Tyro" wrote:

> How do the formulas change? Can you show an example? I'm a very poor mind
> reader
>
> Tyro
>
> "hoosier data" <hoosier data@discussions.microsoft.com> wrote in message
> news:780FABA4-992E-4026-A7FE-EC8727585EB5@microsoft.com...
> > column L uses a 'countif' formula to determine the number of times a
> > specific
> > # appears in column F. after formulas were entered, i filtered the data,
> > and
> > the range for many of those formulas changed. how do i fix this?
>
>
>

Top