SUMIF Problems

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
    - Conditional Formating Is it possible to use conditional formatin in Excel 2003 to: value cell is in red if it's format is accounting - Dollars value cell is in blue if it's format is accounting - Euros Tag: SUMIF Problems Tag: 140853
  - 2
    - Forcing a textbox to update? Hi folks, Does anyone know anything at all about this problem: I have a worksheet containing a scroll bar, taken from the control toolbox. The code for this scrollbar'schange event copies information from about 20 different cells into about 20 textboxes, displayed on the worksheet. Again, they are control toolbox textboxes. I have a separate sub, PrintSelectedValues that sets the value of the scrollbar, then prints out the results, then changes the scrollbar value again before printing a new page, then changes...for about 50 different scrollbar values the problem is that the PrintSelectedValues sub to change the scrollbar's values seems to outpace excels ability to refresh the textboxes, and I get a jumbled mess of results. The first page to print is fine, then PrintSelectedValues changes the scrollbar's value but the textboxes don't update in time. The paper copy contains some information from the first scrollbar value, mixed in with some from the second. I've tried to use a DoEvents command before the print command but I still get the same problem Many thanks for any and all advice! \Tony Tag: SUMIF Problems Tag: 140852
  - 3
    - Adding/subtracting time and displaying hours, minutes, seconds I'm subtracting sunset-sunrise to get total daylight time. I'm then dividing that by 12 The formula I'm using is: =TIME(0,((H71-E71)*24*60)/12,0). Given the following: Sunrise: 5:09 AM Sunset: 6:39 PM the formula produces: 1:07:00. I want it to display the seconds. It should look like: 1:07:30. Well, according to my long-hand calculations I then use that in a subsequent table that looks like: 5:09 AM 6:17 AM 6:17 AM 7:25 AM 7:25 AM 8:33 AM 8:33 AM 9:41 AM 9:41 AM 10:49 AM 10:49 AM11:57 AM 11:57 AM1:05 PM 1:05 PM 2:13 PM 2:13 PM 3:21 PM 3:21 PM 4:29 PM 4:29 PM 5:37 PM 5:37 PM 6:45 PM Where the value on the right is the sum of 1:07:00. I don't want the calculation to round up or down. Thanks Tag: SUMIF Problems Tag: 140849
  - 4
    - In two minds Hi I need to write something to store and manipulate redords relating to packages passing through machines. At present, there are ten machines. Each has a reader of current number (i,e constanty increments and does not reset). On each machine, staff record: Date; package type, Start Number; Finish number; and there are five tally boxes for different types of rejection. Reports: For date range entered by user -package type -total number of packages -Tally of each rejection reason For date range entered by user - machine number - total of each type of package Machines operate 7 days per week. I am in two minds whether to automate this in Excel or Access. Can anyone offer valid reasons to go either one way or the other? If Excel, 1 sheet per machine or simply an additional column with machine number? Tag: SUMIF Problems Tag: 140848
  - 5
    - Change color of multiple autoshapes I need to change the color of several autoshape based on different cells I know how to change one autoshape using a worksheet_change event but i can't just copy and paste this and change the object name + cell name. is it possible to have multiple worksheet_change events in the same worksheet?? Tag: SUMIF Problems Tag: 140841
  - 6
    - how to input consecutive alphabet data? how to input consecutive alphabet data? for example a, b, c etc, k_a, k_b, k_c, etc. Tag: SUMIF Problems Tag: 140840
  - 7
    - conditional formating and use of colorindex Excel 2003, OS XP Proff I have a ss containing multiple line items v project gates of concept, feasibility, business case, design, build, test, deploy, close. The gate cells have projected dates entered into them. The default fill color is 'none' If a business case has been approved prior to the projected date, the cell is filled green. If 'today's date' equals or has exceeded the 'projected date', the cell is marked red. All of this to date has been handled manually but is time consuming to say the least. How can we conditionally format the gate cells to 1. if gate date not green and projected date is > than today's date then cell = default (no color fill) 2. if gate date is < projected date and color fill is green then cell = green 3. if gate date = or > projected date then cell = red 4. if gate date > projected date and color fill is green then cell is green It seems that we have to get the colorindex details of 'green' from the cell to work this through! Whats a simple way to do this? I hope it's clear... The other thing to add would be to sort the list on Red, or to have the projected date - today's date (say 1 week heads up) turn orange and sort on this color. Cheers Roger Tag: SUMIF Problems Tag: 140836
  - 8
    - Help Importing Text File and Getting Right Format - Help Pls Hi I have a text file that has our customers emails separated by commas. I want to get this list imported into excel with each of the email addresses appearing on a separate row in one column. I tried the import wizard and I get one of two results, all the emails in separate columns. Meaning they all show up in Row A under Column A-ZZZ, or they all show up in Row A Column A. The goal is to get each one in a separate row all under column A. Help is greatly appreciated. Thank you Brandon Tag: SUMIF Problems Tag: 140834
  - 9
    - Recovery file??? Hello all, I upgrade my MS Office from 2003 to 2007. Opened an Excel file and saved it as a new workbook. Now I have this hidden file with the name of my file preceeded with ~$ and xlsx extension that refuses to go away. If I delete it, it appears to go away then as soon as I refresh, it's back. My files are in my data folder on a network drive. The file is NOT open. My workstation has been re-booted but the server with the user data files has not. Anyone know how I can get rid of that file WITHOUT re-booting my server??? Thanks, John Tag: SUMIF Problems Tag: 140804
  - 10
    - How to name a sheet from text in a cell in another sheet ? I am very much a novice with using Excel, I'd prefer to be doing this in Access as that's what the data's aimed at in the long run, but the db I'm being forced to use is not mine and I can't work that out either - also work don't support Access so I can't use that to do the data transfer I'm being asked to do... oh why did I say I'd do this :( Down to the true business... I have created a workbook and am trying to name the sheets from cells in another sheet - all within the same workbook. I have been digging about the net and everything I've found that looks to be nearly right doesn't work :( Anyone out there willing to try to help me with this one ? Tag: SUMIF Problems Tag: 140800
  - 11
    - Upening EXCEL and Word Documents from OE OE blocks attached Word and Excel documents---what do I do to get them to be unblocked? -- thanks for your hlep Tag: SUMIF Problems Tag: 140797
  - 12
    - I'm In Over My Head I only have basic Excel skills. I need to automatically insert one of three predefined cell contents including unique background colors based on sampling a calculation. The predefined cell contents are in cells AC-D4, the calculation is in AB4 and the cell that needs to be changed is C4. The idea is to flag financials and schedules either green, yellow or red based on the presence and severity of a problem. Anyone have any ideas? Tag: SUMIF Problems Tag: 140794
  - 13
    - copying numbers generated by formulas Hello. I have a column that generated some numbers using formulas. Now that I have the numbers, I want to copy and paste the numbers elsewhere without copying and pasting the formulas. How do I do this? Thank you! Tag: SUMIF Problems Tag: 140792
  - 14
    - Workbook language translation For what I can see, it looks as if this happens because of Unicode and the language set used in the sending and receiving computers, but I not sure. Is there some other place during the transmission over the Internet that the language translation takes place? So my question is: How does an Excel workbook get translated from one language to another? Thanks for any help you may provide, Ron Tag: SUMIF Problems Tag: 140791
  - 15
    - html I want to write a help system for using my collection of vba procedures. Can anyone reccommend a good text on Microsoft html help workshop? I use XP and have html Help workshop 4.74. -- Thanks for your help Tag: SUMIF Problems Tag: 140787
  - 16
    - Cell range expand or contract I inherited an Excel workbook that pulls a great deal of its data from an Access db using MSQuery. Some of the worksheets contain bar charts that get their source data from the information pulled in from Access. I am having trouble finding a way to make the cell ranges that hold the chart source data expand or contract depending on the amount of data. For example, one month the source for chart A may be Data! A1:B5. The next month there may have been more transactions and the range would be Data!A1:B10. Right now the user goes in and manually adjusts the cell ranges. Is there some way to make this happen without user intervention? Any solutions/suggestions you could pass my way would be greatly appreciated. Thanks very much! SJ Tag: SUMIF Problems Tag: 140783
  - 17
    - Erasing Blank Cell horizontally. Hello, I've been trying to figure out this problem all night and I just can't find the answer T.T Basically, I have like 3000 rows and 366 columns of data containing only numbers. I have one column A filled from A1 till A3000, However columns are not fully filled horizontally. for an example, Row 1 will have number at colum A, D, and AA. Row 2 might have number at colum E, F, Z, AB and AC i.e. A B C D E F G H I J K ... 1 2 3 1 0 2 2 2 3 4 2 3 1 8 9 .. 4 1 5 2 4 5 6 2 6 8 7 1 0 8 . . . How do I automatically delete empty cells horizontally so it looks just like this below? 1 2 3 1 0 2 2 2 3 4 2 3 1 8 9 4 1 5 2 4 5 6 2 6 8 7 1 0 8 Pleae Please HELP thank you so much! Tag: SUMIF Problems Tag: 140770
  - 18
    - find blanks crashes on error I have a piece of code that doesn't respond to error handling: On Error GoTo question_end secondblank = Range("a" & firstqunw & ":a100").Find(What:="", LookAt:=xlWhole).Row It finds the second blank space in a column. When the column is empty it fails (as I would expect) but then instead of error handling it crashes with: run-time error 91 object variable or With block variable not set Any help or suggestions gratefully received tp full code is If ActiveSheet.UsedRange.Count < 2 Then MsgBox "There are no results available yet for that sheet" Else Dim firstblank As String Dim firstqunw As String Dim secondblank As String Dim firstqwei As String On Error GoTo question_error firstblank = Columns("a:a").Find(What:="", LookAt:=xlWhole).Row firstqunw = firstblank + 2 Cells.FindNext(After:=ActiveCell).Activate On Error GoTo question_end secondblank = Range("a" & firstqunw & ":a100").Find(What:="", LookAt:=xlWhole).Row Cells.FindNext(After:=ActiveCell).Activate firstqwei = secondblank + 1 End If Tag: SUMIF Problems Tag: 140764
  - 19
    - Workbook takes a long time to calculate Basically my question is whether Excel has any limits on the number of formula it can perform before it starts to perform really badly? Here is the background: I have a spreadhseet that takes nearly an hour when I try to modify a whole column due to the number of formula I have in there. I think it is due to the sheer number of calculations that Excel has to do rather than the CPU and RAM in my PC. Here are some of the basic specs of the PCs I have tried the spreadsheet on (All running XP Pro): - Dell Optiplex 260 with 512MB RAM - Optiplex 745 with 2.3GHZ processor and 2GB RAM - Dell Latitude D630 laptop with 2.3Ghz processor and 2GB RAM, - Dell Precision desktop with 4GB RAM and a decent processor. I have tried Excel 2002 on all PCs/laptops and MS Excel 2003 on the Optiplex 745. I don't understand why it takes so long to perform these calculations if my PC has a decent spec. When I move a columns from one place to another column Excel just sits there saying calculating cells 0% for a long time. Looks like it is recalculating all the formulas. Here are the characteristics for the spreadsheet: - Size is 30MB total - 2 Worksheets - 1 mainly for raw data and the other for calaculations that reference the 1st spreadsheet The 1st spreadsheet has the following: - 40,000 rows - The 1st 7 columns (A-G) contain text - The next 12 columns (H-S) contain numbers with decimal places with column after that (T) being a formula, which is the sum of the previous 12 columns. - The next 12 columns (U-AF) contain text - The next 12 columns (AG-AU) contain numbers (with some having decimal places). The 2nd spreadsheet: - 20,000 rows - The 1st 5 columns (A-E) is text - The next 12 columns have a fourmula for all rows which starts with =SUMIF(Spreadsheet1!B:B,Spreadsheet2!A:A,Spreadsheet!H:H) for column F and =SUMIF(Spreadsheet1!B:B,Spreadsheet2!A:A,Spreadsheet!I:I) for column G and so on. Any help will be greatly appreciated. Steve Tag: SUMIF Problems Tag: 140763
  - 20
    - Font, Default On my computer at work I have the default font set for a True Type font called Arial Terminal. It's a fixed width Arial font that I like for spreadsheets. I've changed it in Tools > Options > General. But when I open Excel, the font is set to regular Arial, the proportional spaced font, which I absolutely despise, even though Excel still shows Arial Terminal as the default font in Options. If I click on File > New and open a new spreadsheet, the font is Arial Terminal. In the registry in HKCurrentUser > Software > Microsoft > Office > 11.0 > Excel > Options, the font there is Arial Terminal. Can anyone suggest why Excel insists on using Arial, instead of the font I have selected? Tag: SUMIF Problems Tag: 140757
  - 21
    - [File Link] Problem with performance Hi all, i have a perfomance problem when use the function IFSUM with link extern Spreadsheet. The scenario: Spreadsheet Base File : SALES.XLS ~ 10MB Spreadsheet: ABC.XLS In the Spreadsheet [abc.xls] there is some calls to Spreadsheet [sales.xls] The Code Example: IFSUM('[SALES.xls]SALES.'!$E:$F; CONCATENATE($B$1;$A5);'[SALES.xls]SALES.'!$F:$F) The problem: In Excel 97 it´s working, but when I have been working with Excel 2003 sp2/sp3 and opening the file [abc.xls] or same I change some value in the cells, the Excel to stay Recalculate the cells for some minutes. I could work differently in Excel 2003? Thanks, Rodrigo Silveira rodrigosilveira7@gmail.com Tag: SUMIF Problems Tag: 140745
  - 22
    - Conditional Formatting for Dummies Another stupid inquiry from me, this time regarding conditional formatting. I have a cell that I would like to become shaded if any of four other cells are not blank; however, I would like it to deshade if all four cells are not blank. So I created conditional formatting with two formulas: #1: =OR(A1<>"",B1<>"",C1<>"",D1<>"") [SHADE CELL] #2: =AND(A1<>"",B1<>"",C1<>"",D1<>"") [DESHADE CELL] Clearly this isn't working, but I can't seem to figure out how to write it so it works. It's shaded if there is any value in any cell, and still shaded if all the cells have values. I'm sure the answer is simple but it's just not coming to me. Thanks for the help! Tag: SUMIF Problems Tag: 140743
  - 23
    - password protect I have a workbook with 4 pages, is it possible to password protect just one sheet to prevent it being opened. Thanks Tag: SUMIF Problems Tag: 140738
  - 24
    - Remove extra space between first and last names in merge I have tried everything. I did a mail merge in Excel and cannot seem to remove the extra space between the first and last name in the address block on the letter when no middle initial is available. My data fields are FirstName, MiddleInitial, LastName and some people do not have a middle initial so that field is blank for them in the data source. When it prints, the people without a middle initial have two spaces between their first and last names! Please help. thank you. Tag: SUMIF Problems Tag: 140735
  - 25
    - Macro that opens files with differing extensions I've got a macro that currently prompts the user to open a .CSV file. The command I use for this is: FName = Application.GetOpenFilename("Excel Files (*.csv),*.csv") This command only displays .CSV files in the "open" window that's displayed to the user. I'd like to alter this somewhat so that the user can open more than just .csv files. I'd like for it to list the .txt and .xls files as well. What would the adjusted GetOpenFilename command look like for this? Many thanks. Tag: SUMIF Problems Tag: 140729
- Next
  - 1
    - Adding date to filename? Hello Please could I beg a little advice from the experts here I'm using the following code to export data without quotation marks to text files. Sub Export() Dim r As Range, c As Range Dim sTemp As String Open "MyOutput.txt" For Output As #1 For Each r In Selection.Rows sTemp = "" For Each c In r.Cells sTemp = sTemp & c.Text & Chr(9) Next c 'Get rid of trailing tabs While Right(sTemp, 1) = Chr(9) sTemp = Left(sTemp, Len(sTemp) - 1) Wend Print #1, sTemp Next r Close #1 End Sub It works fine so long as the required cells are selected, but does anyone know how to amend the VBA to add the time and date to the filename? I'd be soooo grateful. I tried Open "MyOutput" & Format(Date, "yyyymmdd") & ".txt" For Output As #1 but that just crashed it. Any ideas? Many thanks tp Tag: SUMIF Problems Tag: 140726
  - 2
    - Automatically updating cells in one Excel file to another Hi All, I'm currently updating a project around staff attendance that I'm doing in Excel which will then go out to all of my offices. The thing is that as we are now a couple of weeks or so into the financial year I wanted to save my office managers the task of inputting all their staff's start and finish times over again. If the original file is called Staff1.xls and the new one Staff2.xls, is there a formula I could put in say E4 of the new one (Staff2.xls) which would check to see if the other file (Staff1.xls) was open and if so copy over the details. I know I can do a straight cell lookup but I wanted to combine this with an 'IF' function so if the file wasn't open then that particular cell could do something else like point to another cell within Staff2.xls or do a calculation. Reason for this is that all the managers have not completed their attendance records to date and currently those particular cells point to the previous day's attendance, meaning a staff's attendance time could be input on the first Monday of the financial year and those attendance times will then flow through until the year end, so for the managers it means less inputting for those staff on a fixed duty time. Any help greatly appreciated, Colin. Tag: SUMIF Problems Tag: 140724
  - 3
    - Excel attachments won't open w/o program being open I'm running Excel 2003 SP3 and when I open an Excel attachment via my Outlook it pops up an error message saying "The system cannot find the file specified". If I save the document to my desktop then double click on the document it opens up Excel but not the document. Once Excel is opened up I can click on open select the document and it opens up fine. I have never had a problem opening up Excel files before it just started happening. If anyone has any idea's please let me know. Thanks Tag: SUMIF Problems Tag: 140723
  - 4
    - SBERET.MSI file missing - Excel/Office XP All of my XP Office apps have been running fine since they were installed years ago, until today. Suddenly and inexplicably...as of today when I try to open Excel or any of my many Excel documents, Windows Installer initiates and starts searching for set up files and then prompts me to insert the Office Xp CD ROM, which I unfortunately cannot find. All my other Office Applications (Word, Outlook, etc.) are still running fine. Windows Installer directed me to a help file which indicates that the SBERET.MSI file is missing - and needed.. I've tried searching in all folders and sub-folders (both hidden and not hidden) on my hard drive but have not been able to locate this file. Any suggestions would be greatly appreciated. Thanks, Mark DiRollo Tag: SUMIF Problems Tag: 140722
  - 5
    - Highlighting cells Excel 2007 sp1 with vista 32 business sp1 Background: When you highlight multiple non-contigious cells (i.e. hold the control key down and select several cells that are not connected) Excel highlights the selected cells in light blue. Problem: This light blue does not show at all on many projectors. In some case you can adjust the color temp and this helps. We do hundreds of presentation in North America each year and can not control what projection equipment we will be using. Requested solution: I would change the light blue background that is applied to selected cells to some other color or alternatively making the color darker would probably work. Can this default setting be changed. Thanks, Will Tag: SUMIF Problems Tag: 140721
  - 6
    - Highlighting cells Excel 2007 sp1 with vista 32 business sp1 Background: When you highlight multiple non-contigious cells (i.e. hold the control key down and select several cells that are not connected) Excel highlights the selected cells in light blue. Problem: This light blue does not show at all on many projectors. In some case you can adjust the color temp and this helps. We do hundreds of presentation in North America each year and can not control what projection equipment we will be using. Requested solution: I would change the light blue background that is applied to selected cells to some other color or alternatively making the color darker would probably work. Can this default setting be changed. Thanks, Will Tag: SUMIF Problems Tag: 140720
  - 7
    - Excel to SQL ODBC link: not working when new field added Hi all, I use Excel 2007, and have some Excel files with ODBC link to tables on a Microsoft SQL Server (2005 version). If I change the number of records in the tables in sql and then refresh the ODBC link, everything works fine. However, if I add a field in SQL and then refresh in Excel, the new field won't show up. Do you have any idea why, and how I could fix it? Thanks! Tag: SUMIF Problems Tag: 140719
  - 8
    - Macro changes color Hello I have the following macro to set chart fonts to arial 10. But If I apply this macro to a chart that already has fonts colored differently (lets say some are red, some blue, some white) it changes all fonts to black. I tried to remove ColorIndex hoping i would than ignore color, but it doesn't help. How to make macro that will not change the set colors in the chart? Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic .Background = xlAutomatic End With Tag: SUMIF Problems Tag: 140713
  - 9
    - checkbox with relative reference? I have a checkbox in E5 controlling E5. If I copy it to K11, I want it to control K11. Now, how do I give it this kind of relative reference? Thanks, Sven Tag: SUMIF Problems Tag: 140707
  - 10
    - Summing #s while ignoring blanks... Hello all, Before I ask my question I just want to thank you all in this group for your amazing helpfulness... I'd be so lost without this group! Thank you all! Now onto business... H2:H350 all have values... J2:J350 are always changing. by me inputting and deleting etc.. I want to be able to sum H2:H350 ONLY if there is a value in it's corresponding J cell... so if J2, J3, and J20 have values add H2, H3, H20. if I put a # in J4 add H4 too. I hope I worded my question in a way that makes sense... thank you all again! Tag: SUMIF Problems Tag: 140699
  - 11
    - Macros disappear from personal macro workbook? Whenever I record a new macro to my personal macro workbook it works OK and I can see that a new module has been created ok when in the VBA editor, but when I exit excel and re-open the macro doesn't work and the module has disappeared? Can anyone advise why this is happening and how to prevent it ? Many thanks....J Tag: SUMIF Problems Tag: 140696
  - 12
    - Formula to Average non-numeric entries and skip if blank, and more I have the following dataset for a bar pool league (using Excel 2007): Cell# and Header: ----------------------------------- A (TeamPlayed/Date) B(Team Played/Date) ... P(Christmas Break) Q(TeamPlayed/Date) R(TeamPlayed/Date) ... AD (TeamPlayed/date) (last game in the season) Data (H=home, A=Away) ----------------------------------- WH=Win at Home WA=Win Away LH=Loss at Home LA=Losst Away 0=No show At the end of a row (player), I am trying to create these stats: ----------------------------------- AE (Total Possible # of Matches in the season) AF (Total Games Played) AG (Dedication Percentage) = AH + AI / AE AH (# of Wins) = WH + WA AI (# of Losses) = LH + LA AJ (Total Wins Percentage) = WH + WA/ AF AK (Wins at Home Percentage) = WH / AF AL (Wins Away Percentage) - WA / AF btw - FYI if interested, the Dedication Percentage is just to throw a bone and a pat on the back for the folks that have a better track record showing up more than some others! So for example if they showed up for every match whether a WH, WA, LH or LA L, it should be 100% dedication, which assumes no cell has a 0. In some cases someone may show but not play, but I will leave this one out instead of further complicating this mess. Besides I don't think the team captain kept track of this one anyway. I've created charts a few years ago with stock market data showing moving averages, medians, trends, etc, but I can't for the life of me to come up with formulas to evaluate non-numerics in each cell. I hope this is not too much to ask. I'm sure with someone with Excel experience, I assume this should be a walk in the park. :-) TIA -- Regards, Ace This posting is provided "AS-IS" with no warranties or guarantees and confers no rights. Ace Fekay, MCSE 2003 & 2000, MCSA 2003 & 2000, MCSE+I, MCT, MVP Microsoft MVP - Directory Services Microsoft Certified Trainer For urgent issues, you may want to contact Microsoft PSS directly. Please check http://support.microsoft.com for regional support phone numbers. Infinite Diversities in Infinite Combinations Tag: SUMIF Problems Tag: 140679
  - 13
    - Workbook password security I've been asked to oversee the implementation of a process where organisations would send us sensitive client data via password protected Excel workbooks (i.e. the workbook would have the password on it, not the sheets contained therein. This worries me because it was always my understanding that Excel was never intended to function as a secure data repository and should not be used that way. Having said that, my recent reading leads me to believe that it is the WORKSHEET passwords that are easily worked around, not the WORKBOOK passwords. From what I can gather, the only thing that can get around the workbook passwords are dictionary and brute force attacks (there appears to be no back door around the passwords). If this is the case then would it be reasonable to assume that a highly randomised 12-character (for instance) password containing upper and lowercase, numeric and special characters, would be an adequate foil against both dictionary and brute force attacks? Also, when a password is applied to a workbook, what encryption algorithm is used (eg AES 256 bit)? Tag: SUMIF Problems Tag: 140677
  - 14
    - Insert defined names in header/footer Hello group, Is it possible to insert defined names in header or footer? You can insert Doc Variables into header/footer in Word. Since Excel doesn't have Doc Variables, I was hoping I can insert Doc Proproties. But cannot. It seems defined names can't be used in header/footer either. Can anyone please confirm, or provide an alternate? Thank you! Tag: SUMIF Problems Tag: 140675
  - 15
    - Temporary save files and their accesibility Hello, We recently altered an Excel workbook and have subsequently saved it, we have since found that ideally we would take step back in time and not make the changes we have. I have looked around the discussion topics only to be disappointed by some of the replies stating that what Iâ??m asking is impossible. But here goes anyway: It is my understanding that when Excel saves it has a randomly named temporary file that it saves to, upon successful save the file is renamed to that of the file you are working on and the previously saved file is deleted. With that in mind surely there is A) a directory this files ends up in prior to "true" deletion and more importantly B) the ability to access it as it doesnâ??t pass though any other third party software before disappearing. This may sound incredibly naive but applying some logic to the problem there has to be a way around it, if only to view the previous sheets prior to amendments being made. Any help would be greatly received, Chris Tag: SUMIF Problems Tag: 140672
  - 16
    - Need help with non-alterable sheet We have a worksheet where I work that someone has tampered with and made it non-alterable. Only certain cells can even be clicked and most features have been greyed out altogether. The sheet was protected but the protection was not passworded and has been removed but that didn't do anything to help. What else, besides protection, could have a sheet locked like this? Tag: SUMIF Problems Tag: 140666
  - 17
    - excel 2007 localized function names -> english names This is a multi-part message in MIME format. ------=_NextPart_000_0025_01C89E65.327C1FA0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, Is there any way how to change localized function names in excel 2007 to = english ? It's silly, that MS localized also this. Thanks for replies ------=_NextPart_000_0025_01C89E65.327C1FA0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8"> <META content=3D"MSHTML 6.00.6000.16609" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3DVerdana color=3D#000080 size=3D2>Hi,</FONT></DIV> <DIV><FONT face=3DVerdana color=3D#000080 size=3D2></FONT> </DIV> <DIV><FONT face=3DVerdana color=3D#000080 size=3D2>Is there any way how = to change=20 localized function names in excel 2007 to english ?</FONT></DIV> <DIV><FONT face=3DVerdana color=3D#000080 size=3D2></FONT> </DIV> <DIV><FONT face=3DVerdana color=3D#000080 size=3D2>It's silly, that MS = localized also=20 this.</FONT></DIV> <DIV><FONT face=3DVerdana color=3D#000080 size=3D2></FONT> </DIV> <DIV><FONT face=3DVerdana color=3D#000080 size=3D2>Thanks for=20 replies</FONT></DIV></BODY></HTML> ------=_NextPart_000_0025_01C89E65.327C1FA0-- Tag: SUMIF Problems Tag: 140663
  - 18
    - Combining formulas from difffernt cells I am using Excel 2002. In cell A1 I have a long formula, and in cell A2 another long formula. How can I copy the formula in cell A2 and then add the formula to cell A1 so it has its formula plus [+] the formula in A2? Thanks, Tonso Tag: SUMIF Problems Tag: 140648
  - 19
    - Double if statements? Hello all... I currently have this formula, which works fine... =IF(A20="L",L20-H20,H20-L20) however now what I want to do is add an if statement to same cell that says essentially... If J20 is blank... return $0, otherwise do above formula. Is this possible? Thank you all, This group is a life saver!! Shhhh Tag: SUMIF Problems Tag: 140645
  - 20
    - Data Validation - Allow "" in Number Cell It's amazing how much time can be wasted trying variations of a custom formula in Data Validation without hitting on the right syntax :-( I have a cell (assume A1) formatted as a number to 2 decimal places. Normally a formula will enter the appropriate number in the cell, otherwise the formula will enter "". Sometimes the formula will be overwritten and a number will be entered manually. Because the number may be entered manually, I want to set a Data Validation check. Data Validation will need to allow both "" or any number (including 0.00). I was trying to use the ISNUMBER function in Data Validation custom - something like: =AND(A1<>"",NOT(ISNUMBER(A1))) but it doesn't seem to work. Can someone point me in the right direction? TIA -- Mike -Please remove 'safetycatch' from email address before firing off your reply- Tag: SUMIF Problems Tag: 140644
  - 21
    - Stacked charting Hi I have three series; Income Target, Confirmed Income and Provisional Income. I would like to stack Confirmed Income and Provisional Income on top of each other to make combined income and then I want the Income Target next to it so I can compare. Unfortunately as far as I can see charts either only stack all series or place all of them side by side, they don't mix between stacking and placing side by side. Is this correct or am I missing something. Regards Tag: SUMIF Problems Tag: 140640
  - 22
    - Display data using hlookup, index-match and offset Hi, I have a data set as follows: ABC Run 3 ABC Walk 2 ABC Sleep 5 XYZ Run 3 XYZ Walk 1 LMN Jog 10 LMN Sing 12 I am creating a formula wherein when the user selects an input from a list with options from Col 1 and 2, the formula displays the corresponding value of Col 3. Hence, when I choose ABC and Walk I get 2. I have tried to using index-match but am not able to display the data properly. Also, I tried using the offset function. The way I did it was by having to sort the col 1 first and then change the reference everytime there is a new entry. But the problem with this logic is that everytime I have a new entry like OPQ for eg. I'll have to add another IF statement to identify it. Also, I tried using row with hlookup which does not work in identifying the row no. =+ROW(HLOOKUP(A11,$A$1:$C$10,MATCH(B1,$B$1:$B$10,0),FALSE)) A11 and B11 has the input for col 1 and 2 resp. Thanks, Namit. Tag: SUMIF Problems Tag: 140639
  - 23
    - TMP files John, I know this is 12 months later .... but did you get a solution? I am having the same problem also using AVG 7.5. I cannot find the "scan on close" box in AVG can you point me there? (I am using Office 2007. Thanks if you can help me). Tag: SUMIF Problems Tag: 140637
  - 24
    - summing different values Range B3:F33 has differen values. Cells are formatted as accounting for 3 different currecy: Hebrew (NIS), Dollars and Euros In cell B35 I want to have the sum of the NIS Hbrew Shekels In cell B36 I want the summ of Dollars In cell B37 I want the sum of the Euros from the range. Is This possible??? Tag: SUMIF Problems Tag: 140636
  - 25
    - help moving from Excel 2000 to 2007 Does anyone have any ideas or resources to help an individual getting used to using Excel 2007 after using Excel 2000 for years and love love loving it? Today, after about 30 minutes of trying to find where to go to add option buttons or checkboxes (checking every menu, oh sorry, "ribbon"; typing term after term into the "Help" and getting no results that were actually helpful; doing a quick google search; checking every "ribbon" again), I started to type up a question to this newsgroup, but then decided to check Help one last time. (I wasn't sure if I was using the "offline" or "online" help. Why are there two types now, I ask you? Why?!?) After a few more unsuccessful results, I did finally find my answer. Radio buttons are on the "Developer" tab of course, which is not shown unless you dig around in the Excel Options and turn it on. Sorry, enough sarcastic ranting. I'd REALLY appreciate any tips on learning the "beauties" of Office 2007 so that I don't tear my hair out every time I sit down to create a spreadsheet (or a Word document for that matter) because I can't find anything. Or because what used to take two clicks now takes 27. Oh, what i wouldn't give for a nice little chart: this is how you used to do it, this is how it's done now. Thanks for any help! Karin Tag: SUMIF Problems Tag: 140631

Hi Can some one help me im having some trouble with it. It does not allow me
to say greater or smaller as cell A117 =SUMIF(A7:A114;<A117;C7:C114) does
anybody have an idea on what im doing wrong

Markus

Top

Re: SUMIF Problems by Ron

Ron
Thu Apr 17 06:29:00 CDT 2008

On Thu, 17 Apr 2008 02:22:01 -0700, Markus <Markus@discussions.microsoft.com>
wrote:

>Hi Can some one help me im having some trouble with it. It does not allow me
>to say greater or smaller as cell A117 =SUMIF(A7:A114;<A117;C7:C114) does
>anybody have an idea on what im doing wrong
>
>Markus

You need to put the operator in quote marks, and concatenate the cell
reference.

From HELP for that function:

Important Any text criteria or any criteria that includes logical or
mathematical symbols must be enclosed in double quotation marks ("). If the
criteria is numeric, double quotation marks are not required.

e.g.:

=SUMIF(A7:A114;"<"&A117;C7:C114)

--ron

Top

Re: SUMIF Problems by Markus

Markus
Fri Apr 18 00:12:00 CDT 2008

Thank you

"Ron Rosenfeld" wrote:

> On Thu, 17 Apr 2008 02:22:01 -0700, Markus <Markus@discussions.microsoft.com>
> wrote:
>
> >Hi Can some one help me im having some trouble with it. It does not allow me
> >to say greater or smaller as cell A117 =SUMIF(A7:A114;<A117;C7:C114) does
> >anybody have an idea on what im doing wrong
> >
> >Markus
>
> You need to put the operator in quote marks, and concatenate the cell
> reference.
>
> From HELP for that function:
>
> Important Any text criteria or any criteria that includes logical or
> mathematical symbols must be enclosed in double quotation marks ("). If the
> criteria is numeric, double quotation marks are not required.
>
>
> e.g.:
>
> =SUMIF(A7:A114;"<"&A117;C7:C114)
>
> --ron
>

Top