Search Results

Search found 4727 results on 190 pages for 'excel 2002'.

Page 36/190 | < Previous Page | 32 33 34 35 36 37 38 39 40 41 42 43  | Next Page >

• Useful Excel keyboard shortcuts

  - by Ben Lings

  What keyboard shortcuts do you use in Excel? Things I've discovered recently and found very useful are: Shift + Space - select the current row Ctrl + Space - select the current column Ctrl + Shift + Space - select the block of contiguous cell Ctrl + + - Insert (as in the context menu). If the current row is selected, will insert a new row. Ctrl + - - Delete (as in the context menu). If the current row is selected, will delete entire row. What (apart from the normal cut, copy, paste, etc) do you use? Ctrl + 1 to open the Format dialog. Shift + F2 to add/edit a cell comment. Shift + F2, followed by Esc to select the current cell comment, which can then be moved around with the arrow keys (????) or deleted by pressing Del. Ctrl + ???? to move to the last non-blank cell in a series. This is usually the edge of a table, but not if you have blank cells in the path. Pressing End followed by an arrow key does the same thing. Alt + F11 to open the VBA editor. Alt + = to start a SUM() formula and go straight to selecting cells to be summed. Ctrl + G or F5 to jump to a cell by typing its coordinates (e.g. C3) Ctrl + Home to jump to the top left, usually A1 unless you you are in a frozen split view, in which case it will jump to the top left of the "data" area. Ctrl + ; and Ctrl + Shift + ; to insert the current date and time, respectively. I know Ben Lings already posted this one, but I find it indispensable.

  Read the article

• Split Excel worksheet into multiple worksheets based on a column with VBA (Redux)

  - by Ceeder

  I'm rather new to VBA and I've been working with the code generously displayed and explained by Nixda: Split Excel Worksheet... My only challenge is I've been trying desperately to find a way to include the top 3 rows as a title bu it seems to only allow for one. Here's the code have: Dim Titlesheet As Worksheet iCol = 23 '### Define your criteria column strOutputFolder = (Sheets("Operations").Range("D4")) '### <--Define your path of output folder Set ws = ThisWorkbook.ActiveSheet Set rngLast = Columns(iCol).Find("*", Cells(3, iCol), , , xlByColumns, xlPrevious) Set Titlesheet = Sheets("Input") ws.Columns(iCol).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Set rngUnique = Range(Cells(4, iCol), rngLast).SpecialCells(xlCellTypeVisible) If Dir(strOutputFolder, vbDirectory) = vbNullString Then MkDir strOutputFolder For Each strItem In rngUnique If strItem < "" Then Sheets("Input").Select Range("A1:V3").Select Selection.Copy ws.UsedRange.AutoFilter Field:=iCol, Criteria1:=strItem.Value Workbooks.Add Sheets("Sheet1").Select ActiveSheet.PasteSpecial ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=[A4] strFilename = strOutputFolder & "\" & strItem ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlWorkbookNormal ActiveWorkbook.Close savechanges:=False End If Next ws.ShowAllData Is there something I can change to include these lines? Thanks so much, this code provided by Nixda has taught me a great deal!

  Read the article

• Pasting formatted Excel range into Outlook message

  - by Steph

  Hi everyone, I am using Office 2007 and I would like to use VBA to paste a range of formatted Excel cells into an Outlook message and then mail the message. In the following code (that I lifted from various sources), it runs without error and then sends an empty message... the paste does not work. Can anyone see the problem and better yet, help with a solution? Thanks, -Steph Sub SendMessage(SubjectText As String, Importance As OlImportance) Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookRecip As Outlook.Recipient Dim objOutlookAttach As Outlook.Attachment Dim iAddr As Integer, Col As Integer, SendLink As Boolean 'Dim Doc As Word.Document, wdRn As Word.Range Dim Doc As Object, wdRn As Object ' Create the Outlook session. Set objOutlook = CreateObject("Outlook.Application") ' Create the message. Set objOutlookMsg = objOutlook.CreateItem(olMailItem) Set Doc = objOutlookMsg.GetInspector.WordEditor 'Set Doc = objOutlookMsg.ActiveInspector.WordEditor Set wdRn = Doc.Range wdRn.Paste Set objOutlookRecip = objOutlookMsg.Recipients.Add("[email protected]") objOutlookRecip.Type = 1 objOutlookMsg.Subject = SubjectText objOutlookMsg.Importance = Importance With objOutlookMsg For Each objOutlookRecip In .Recipients objOutlookRecip.Resolve ' Set the Subject, Body, and Importance of the message. '.Subject = "Coverage Requests" 'objDrafts.GetFromClipboard Next .Send End With Set objOutlookMsg = Nothing Set objOutlook = Nothing End Sub

  Read the article

• Neophyte question about using Subtotal and CountIf in Excel

  - by Andrew

  Hi, I'm using Excel and having some problems with Countif and I don't understand how it works differently from SubTotal. I used the GUI to subtotal stuff and all the subtotals are right. Then I attempted to use the Countif to see how many requirements passed. That worked for the first subtotal only. It's easy to see why. When I look at the box for the subtotal, it says: =SUBTOTAL(3,C286:C292) When I look at my formula for passed requirements, I have: =IF(ISTEXT(A285),COUNTIF(C286:C338,"=Passed"),"") Notice that the last column is wrong. How did the Subtotal manage to keep this correct? I typed in the formula for passed requirements and dragged it down the page. Everything behaved as expected (even the bit about ISTEXT dutifully figured out which row was which), but it got the last row wrong. Any ideas? SRS Maintenance Count 7 44 SRS Maintenance Passed SRS Maintenance Passed SRS Maintenance Passed SRS Maintenance Passed SRS Maintenance Passed SRS Maintenance Passed SRS Maintenance Passed SRS Reports Count 12 43 SRS Reports Passed SRS Reports Passed SRS Reports Passed SRS Reports Passed SRS Reports Failed SRS Reports Passed SRS Reports Passed SRS Reports Failed SRS Reports Passed SRS Reports Passed SRS Reports Failed

  Read the article

• Transpose matrix-style table to 3 columns in Excel

  - by polarbear2k

  I have a matrix-style table in excel where B1:Z1 are column headings and A2:A99 are row headings. I would like to convert this table to a 3 column table (column heading, row heading, cell value). It does not matter in what order the new table is. A B C D A B C A B C 1 H1 H2 H3 1 H1 R1 V1 1 H1 R1 V1 2 R1 V1 V2 V3 => 2 H1 R2 V4 or 2 H2 R1 V2 3 R2 V4 V5 V6 3 H1 R3 V7 3 H3 R1 V3 4 R3 V7 V8 V9 4 H2 R1 V2 4 H1 R2 V4 5 H2 R2 V5 5 H2 R2 V5 6 H2 R3 V8 6 H3 R2 V6 7 H3 R1 V3 7 H1 R3 V7 8 H3 R2 V6 8 H2 R3 V8 9 H3 R3 V9 9 H3 R3 V8 I've been playing around with the OFFSET function to create the whole table but I feel like a combination of TRANSPOSE and V/HLOOKUP is required. Thanks EDIT I have managed to come up with the correct formulas. If the data is in Sheet1 like in my example above, the formulas go in Sheet2: [A1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99), OFFSET(Sheet1!$A$1,0,IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"") [B1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),0),"") [C1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"") The formulas are limited to B1:Z1 for the headings and A2:A99 for the rows (these can be increased to their maximums if required). The COUNTA() formula returns the number of cells that actually have values, which limits the number of rows returned to headings*rows. Otherwise the formulas would could go on for infinity because of the MOD function.

  Read the article

• How to read an Excel file, get and set the information using POI

  - by user1399713

  I'm using Java to read a form that is in an Excel spreadsheet that the user fills in with information about geometric shape. Ex: Shape :_________ Color :_________ Area: _________ Perimeter:________ So far the code I have can I can read what I want in the form and print out the values of Shape, Color, Area, Perimeter. public class RangeSetter { /** * @param args * @throws IOException */ public static void main(String[] args) throws IOException { FileInputStream file = new FileInputStream(new File("test2.xls")); //C:\Users\Yo\Documents // Setup code String cname = "Shape"; HSSFWorkbook wb = new HSSFWorkbook(file); // retrieve workbook // Retrieve the named range // Will be something like "$C$10,$D$12:$D$14"; int namedCellIdx = wb.getNameIndex(cname); Name aNamedCell = wb.getNameAt(namedCellIdx); // Retrieve the cell at the named range and test its contents // Will get back one AreaReference for C10, and // another for D12 to D14 AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getRefersToFormula()); for (int i=0; i<arefs.length; i++) { // Only get the corners of the Area // (use arefs[i].getAllReferencedCells() to get all cells) CellReference[] crefs = arefs[i].getAllReferencedCells(); for (int j=0; j<crefs.length; j++) { // Check it turns into real stuff Sheet s = wb.getSheet(crefs[j].getSheetName()); Row r = s.getRow(crefs[j].getRow()); Cell c = r.getCell(crefs[j].getCol()); if (c!= null ){ switch(c.getCellType()){ case Cell.CELL_TYPE_STRING: System.out.println(c.getStringCellValue()); } } } } What I want to do is to create a method that gets the that information and another that sets it. So far I can only print to the console

  Read the article

• Excel 2007 - "The macro may not be available in this workbook" Error

  - by Psycho Bob

  We use an Excel sheet that has been protected to prevent modification of it from end users. All in all they are only able to edit certain tabs to add information that will then be used to generate information on other tabs using equations and such. On the tab with the equations, a button is present called "Prep for Internal Hard Copy Print." This button runs a macro that selects the information on the tab, unprotects it, then sends a print job to the user's default printer that contains the unprotected content. Normally this works like a champ. This time around, however, the macro is throwing the following error: Cannot run the macro "FILENAME.xlsx'!MacroName'. The macro may not be available in this workbook or all macros may be disabled. As far as I can tell, the macros are still present within the workbook. This sheet is normally a .xlsm though the user saved it with a different filename as a .xlsx. Also, the macros appear only as MacroName in the .xlsm file and not "FILENAME.xlsx'!MacroName' as it does in the .xlsx. Finally, when I open the .xlsm it asks if I want to enable the macro content while the .xlsx does not prompt for this. Can anyone tell me what's going on with this sheet or know of a way that I can get the macros working in the .xlsx without having to start over with a different sheet?

  Read the article

• Excel 2007 pivot table does not aggregate properly

  - by Patrick

  I am using a an excel pivot table to summarize some data and just found a problem. The problem deals with how aggregate values are calculated. Let's say I have a table of data with three columns: Name, Date, Value. If I create a table where Name and then Date are used as Row Labels and Value is the aggregate value, ie Average. The pivot table will look something like this: +John .3450 5/14/2010 1.234 5/15/2010 3.450 5/16/2010 -3.25 What I think should be happening here is that the values for each date are averaged and then those values are averaged to come up with the value in the same row as the Name, John. But that is not what it does. It takes the average for each date, which it shows across from the date, but then instead of taking the average of those numbers, it actually uses the raw data and computes the average for all of John's values. It should show the average of the daily averages to correspond with the tree hierarchy, but instead just shows me the average for all of John's values. It essential will only aggregate at one level, but visually creates sub levels that it is not using. Does anyone know how to change this or understand by what logic this makes sense? Why would I create any sub groupings if I cannot compute aggregates on them?

  Read the article

• How to Programmatically Split and Manipulate Rows of Data From Excel

  - by Charlene

  I am hoping one of you will be able to help get me started on this issue. I need to create some sort of macro or VBA code to split and manipulate rows of data in Excel. For this example, we have 5 rows of data. The first 3 rows are item information for Order # 0000000000-00 and the last 2 rows are item information for order # 0000000000-01. I need one row ("HDR") for each order number, and one row ("ITM") for each product per order. I have included an example below showing the data I will receive and the desired outcome. Raw Data: order-id product-num date buyer-name product-name quantity-purchased 0000000000-00 10000000000000 5/29/2014 John Doe Product 0 1 0000000000-00 10000000000001 5/29/2014 John Doe Product 1 2 0000000000-00 10000000000002 5/29/2014 John Doe Product 2 1 0000000000-01 10000000000002 5/30/2014 Jane Doe Product 2 1 0000000000-01 10000000000003 5/30/2014 Jane Doe Product 3 1 Desired Outcome: HDR 0000000000-00 John Doe 5/29/2014 ITM 10000000000000 Product 0 1 ITM 10000000000001 Product 1 2 ITM 10000000000002 Product 2 1 HDR 0000000000-01 Jane Doe 5/30/2014 ITM 10000000000002 Product 2 1 ITM 10000000000003 Product 3 1 Any and all help would be much appreciated!!! Thank you.

  Read the article

• Excel 2007 - Adding line breaks in a cell and no line over 50 characters

  - by Richard Drew

  I have notes stored in an excel cell. I add line breaks and dates every time I add a new note. I need to copy this to another program, but it has a line limit of 50 characters. I want a line break for each new date and for when each date's comment goes over 50 characters. I'm able to do one or the other, but I can't figure out how to do both. I'd prefer words not to be split up, but at this point I don't care. Below is some sample input. If needed for a SUBSTITUTE or REPLACE function, I could add a ~ before each date in my input as a delimiter. Sample Input: 07/03 - FU on query. Copies and history included. CC to Jane Doe and John Public 06/29 - Cust claiming not to have these and wrong PO on query form. Responded with inv sent dates and locations, correct PO values, and copies. 06/27 - New ticket opened using query form 06/12 - Opened ticket with helpdesk asking status 05/21 - Copy submitted to [email protected] 05/14 - Copy sent to John Public and [email protected] Ideal Output: 07/03 - FU on query. Copies and history included. CC to Jane Doe and John Public 06/29 - Cust claiming not to have these and wrong PO on query form. Responded with inv sent dates an d locations, correct PO values, and copies. 06/27 - New ticket opened using query form 06/12 - Opened ticket with helpdesk asking status 05/21 - Copy submitted to [email protected] om 05/14 - Copy sent to John Public and email@custome r.com

  Read the article

• Numbering grouped data in Excel

  - by Jeff

  I have an Excel spreadsheet (2010) with data similar to this: Dogs Brown Nice Dogs White Nice Dogs White Moody Cats Black Nice Cats Black Mean Cats White Nice Cats White Mean I want to group these animals but I only care about species and color. I don't care about disposition. I want to assign group numbers to the set as shown here. 1 Dogs Brown Nice 2 Dogs White Nice 2 Dogs White Moody 3 Cats Black Nice 3 Cats Black Mean 4 Cats White Nice 4 Cats White Mean I was able to select all the species and colors, then from the data tab select 'advanced', then 'unique records only'. This collapsed the data so that I could number the visible rows. Then when I 'cleared' the filter I could easily just fill the blank areas under the numbers with the number above. The problem is that my real data has far too many rows for this to be practical. Also, the trick about entering 1 in the first cell, 2 in the cell below, selecting both then dragging the corner down to 'auto-number' doesn't seem to work when you're viewing filtered rows. Any way to do this?

  Read the article

• duplicate data from another sheet in Excel

  - by Max

  I have a rather large Excel document with a lot of separate sheets in it. There is some info (email, last name, first name) that has to be the first three columns on each sheet. In order to be sure that no mistakes are made, I created a "Person" sheet that only contains those three columns. On the other sheets, I want to get the info from that Person sheet. I can get the email column in several ways (right now, I have =Person[Email] in that column), and then I use that to get the last name and first name. So, there isn't a problem getting the data into those other sheets; but now, I want to sort by last name or first name (this is all in a table). What happens is that if I sort by Name, then you can see a flash where it re-orders the entire table, but then the =Person[Email] gets run again and the first column resets back to the order that is in the Person sheet. So this is even worse--not only can't I sort properly, but now the entire table is messed up because all of the data is in name ascending order except for the email addresses which are in the default order. Is there a way to get the email column to replicate in all other sheets, but then stop updating so I can sort/etc? Thanks in advance

  Read the article

• EXCEL workbook, intermitently, takes 30 seconds to load

  - by Julio Nobre

  I am trying to figure out why a simple .XLS EXCEL workbook is taking, randomly, 30 seconds to open. Before answering: Please, bear mind the following: Problem symptoms Hanging is intermitent and it takes exactly 30 seconds; During hanging there is no cpu or disk activity; It only happens during workbook load. Every runs smooth after that; Windows Explorer.exe hangs on folder, but all other folders, system and applications are still responsive; There are no consecutive hangings. I have to wait for while to reproduce this behaviour; All workbooks where located on a local drive (C:\BPI); The workbook has no macros and no addins; Office 2003 is being used for several years; The computer is running Windows XP; Computer has several network mapped drives, all addressed to main file server; Recently, main fileserver was replaced by Windows 2011 SBS Standard Edition What I have done so far I have traced machine Explorer.exe, using Process Monitor, added Duration column, and filtered by Duration 1. That's is how I found that hanging was taking exactly 30 seconds. For further information, please refer to Oliver Salzburg tutorial. Using Process Monitor, I have also figured out than five operations were taking most of sample collecting duration. Looking at sample image below, column Operation below you will notice that one single operation was taking 29 seconds; I have tried different workbooks (all of them smaller than 30 KB); I have, temporarily, removed all shortcuts on User Document's folder that were pointing to network drives or shares; I have runned CCleaner to fix registry issues; I made sure that there were no external links on tested workbooks; I have reproduced this behaviour for hours; I have extensivelly researched for hours on the web; Process Monitor's collected and filtered data

  Read the article

• How do I `SUM` by multiple columns in Excel

  - by dwwilson66

  I have a comma delimited file that includes two columns date/time (which imports as Excel's mm/dd/yyyy hh:mm custom format) and status of 1 or 0. The status represents a piece of equipment either being on or off. I'm trying to generate a graph that will show, hours up vs. down by day. CONSIDER: 1/1/2012 00:00, 1 1/1/2012 03:00, 0 1/1/2012 14:00, 1 1/3/2012 00:00, 0 This tells me that the equipment was up for three hours, down for eleven hours, and then up for thirty-four hours (across two calendar days). However, I would like to generate a graph that shows how many hours PER DAY we were up or down. CONSIDER: 1/1 XXXXXXXXXXXXX----------- (up 13, down 11) 1/2 XXXXXXXXXXXXXXXXXXXXXXXX (up 24) To me, it seems that I need to generate a dataset summing HOURS by STATUS by CALENDAR DAY...but I can't seem to find a flavor of pivot table or nested SUM(IF(SUMIF(...))) combination to make it work. Most troubling is accounting for date changes...in my example above, since my uptime starting at 14:00 on 1/1/2012 crosses midnight, I need to know that 10 uptime hours get totalled with 1/1/2012 and 24 uptime hours get totalled with 1/2/2012. I may be able to do something with a calendar list to drive the date summation, but then I need a way to compare 01/01/2012 to 01/01/2012 03:00 as equal. There's got to be a way along the lines of if(INTEGER-PORTIONS-OF-SERIAL-DATES-ARE-EQUAL,TOTAL-HOURS-IF-VALUE-IS_1,0) but nothing's worked so far. Any suggestions? I've been battling this most of the day, and need a fresh perspective. Thanks

  Read the article

• Cumulative average using data from multiple rows in an excel table

  - by Aaron E

  I am trying to calculate a cumulative average column on a table I'm making in excel. I use the totals row for the ending cumulative average, but I would like to add a column that gives a cumulative average for each row up to that point. So, if I have 3 rows I want each row to have a column giving the average up to that row and then the ending cumulative average in the totals row. Right now I can't figure this out because I'd be having to reference in a formula rows above and below the current row and I'm unsure about how to go about it because it's a table and not just cells. If it was just cells then I know how to do the formula and copy it down each row, but being that the formula I need depends on whether or not a new row in the table is added or not I keep thinking that my formula would be something like: (Completion rate row 1/n) where n is the number of rows up to that point, here row 1, then ((Completion rate row 1 + Completion rate row 2)/n) for row 2 so n=2, and so on for each new row added. Please advise.

  Read the article

• Finding matching columns in excel

  - by fakaff

  I've never used excel before so I need the simplest solution available, and this is a work assignment due this week so I didn't have time read much of the documentation. Basically, I have two tables, A and B, and they are both thousands of rows long. Description of my task: right now (since I don't know better) I'm manually doing this: Go to row i in table B. Select entries in columns B(a, b, c) of that same row. Look for a row in table A where column A(b) matches row B(a). Paste the entries of columns B(a) of row i at the end of the row found in the last step. Repeat for row i + 1. Example: row B(cat, dog, mouse) matches A(mammal, cat, Mr. Whiskers). So I would paste B after A and have A(mammal, cat, Mr. Whiskers, cat, dog, mouse). Note: I am not joining tables. I am merely extending table A by pasting row A(b) if row A(b) matches row B(a). Also, sometimes entries are spelled slightly differently. Using wildcards to search for candidates would be of help. As the description should let on, this task is very tedious and inefficient if I don't know how to automate some operations (there are thousands of entries). Any quick tips as to how to be more productive is a big help.

  Read the article

• Using the full width of an Excel chart with two Y-axes

  - by Jørn Schou-Rode

  I am trying to create a line chart in MicrosoftExcel 2007 with two data series, each with their own Y-axis. First, I create a simple chart by selecting the two data series, and choosing Insert > Charts > Line from the Ribbon. I now see the following chart in my workbook: I then continue my quest by right clicking one of the data series (lines) and choosing Format data series > Series Options > Secondary Axis. My chart is now looks like this: This is almost what I want. I did not expect to see the gap between the last X-axis tick point (x = 5) and the secondary (right most) Y-axis. Why does Excel introduce this gap? Is there anything I can do to avoid it? I have tried right clicking the X-axis and seleting Format Axis > Axis Options > Position Axis: Between tick marks, but that only introduces a similar gap on by the primary (left most) Y-axis.

  Read the article

• Microsoft Excel not graphing

  - by SmartLemon

  Im not sure if this is a math question or a su question. The experiment was relating the period of one "bounce" when you hang a weight on a spring and let it bounce. I have this data here, one being mass and one being time. The time is an average of 5 trials, each one being and average of 20 bounces, to minimize human error. t 0.3049s 0.3982s 0.4838s 0.5572s 0.6219s 0.6804s 0.7362s 0.7811s 0.8328s 0.869s The mass is the mass that was used in each trial (they aren't going up in exact differences because each weight has a slight difference, nothing is perfect in the real world) m 50.59g 100.43g 150.25g 200.19g 250.89g 301.16g 351.28g 400.79g 450.43g 499.71g My problem is that I need to find the relationship between them, I know m = (k/4PI^2)*T^2 so I can work out k like that but we need to graph it. I can assume that the relationship is a sqrt relation, not sure on that one. But it appears to be the reverse of a square. Should it be 1/x^2 then? Either way my problem is still present, I have tried 1/x, 1/x^2, sqrt, x^2, none of them produce a straight line. The problem for SU is that when I go to graph the data on Excel I set the y axis data (which is the weights) and then when I go to set the x axis (which is the time) it just replaces the y axis with what I want to be the x axis, this is only happening when I have the sqrt of "m" as the y axis and I try to set the x axis as the time. The problem of math is that, am I even using the right thing? To get a straight line it would need to be x = y^1/2 right? I thought I was doing the right thing, it is what we were told to do. I'm just not getting anything that looks right.

  Read the article

• In which order does Excel process its formulae?

  - by dwwilson66

  I've got a fairly large spreadsheet with major calculations going on, and it's starting to slow down every time a value that's part of a calculated field is modified. I'm in the process of optimizing the file, adding arrays where I can, and seeing where I can shave off a few milliseconds here and there. Let's say there's data in Columns A-H. Column H is set based on relationships between values in Columns A, B and C, which change dynamically from an outside program. Users enter the data in Column F. Formulas in D & E calculate relationships between F & H and H & D, respectively. How does Excel manage formulae in the case, for instance, where they're dependent on data further into the sheet? Will my value in H be available the first time that the formulae in D & E calculate? or, will D & E calculate based on an old value for H, because H's update hasn't happened yet? Are there any efficiencies to be gained by positioning dependencies in particular rows or columns in the speadsheet? Do positions above and left the current position get processed sooner than things below and to the right?

  Read the article

• Excel equivilant of java's String.contains(String otherString)

  - by corsiKa

  I have a cell that has a fairly archaic String. (It's the mana cost of a Magic: the Gathering spell.) Examples are 3g, 2gg, 3ur, and bg. There are 5 possible letters (g w u b r). I have 5 columns and would like to count at the bottom how many of each it contains. So my spreadsheet might look like this A B C D E F G +-------------------------------------------- 1|Name Cost G W U B R 2|Centaur Healer 1gw 1 1 0 0 0 3|Sunspire Griffin 1ww 0 1 0 0 0 // just 1, even though 1ww 4|Rakdos Shred-Freak {br}{br} 0 0 0 1 1 Basically, I want something that looks like =if(contains($A2,C$1),1,0) and I can drag it across all 5 columns and down all 270 some cards. (Those are actual data, by the way. It's not mocked :-) .) In Java I would do this: String[] colors = { "B", "G", "R", "W", "U" }; for(String color : colors) { System.out.print(cost.toUpperCase().contains(color) ? 1 : 0); System.out.print("\t"); } Is there something like this in using Excel 2010. I tried using find() and search() and they work great if the color exists. But if the color doesn't exist, it returns #value - so I get 1 1 #value #value #value instead of 1 1 0 0 0 for, example, Centaur Healer (row 2). The formula used was if(find($A2,C$1) > 0, 1, 0).

  Read the article

• Prevent 'Run-time error '7' out of memory' error in Excel when using macro

  - by MasterJedi

  I keep getting this error whenever I run a macro in my excel file. Is there any way I can prevent this? My code is below. Debugging highlights the following line as the issue: ActiveSheet.Shapes.SelectAll My macro: Private Sub Save() Dim sh As Worksheet ActiveWorkbook.Sheets("Report").Copy 'Create new workbook with Sheets("Report"(2)) as only sheet. Set sh = ActiveWorkbook.Sheets(1) 'Set the new sheet to a variable. New workbook is now active workbook. sh.Name = sh.Range("B9") & "_" & Format(Date, "mmyyyy") 'Rename the new sheet to B9 value + date. With sh.UsedRange.Cells .Value = .Value 'eliminate all formulas .Validation.Delete 'remove all validation .FormatConditions.Delete 'remove all conditional formatting ActiveSheet.Buttons.Delete ActiveSheet.Shapes.SelectAll Selection.Delete lrow = Range("I" & Rows.Count).End(xlUp).Row 'select rows from bottom up to last containing data in column I Rows(lrow + 1 & ":" & Rows.Count).Delete 'delete rows with no data in column I Application.ScreenUpdating = False .Range("A410:XFD1048576").Delete Shift:=xlUp 'delete all cells outwith report range Application.ScreenUpdating = True Dim counter Dim nameCount nameCount = ActiveWorkbook.Names.Count counter = nameCount Do While counter > 0 ActiveWorkbook.Names(counter).Delete counter = counter - 1 Loop 'remove named ranges from workbook End With ActiveWorkbook.SaveAs "\\Marko\Report\" & sh.Name & ".xlsx" 'Save new workbook using same name as new sheet. ActiveWorkbook.Close False 'Close the new workbook. MsgBox ("Export complete. Choose the next ADP in cell B9 and click 'Calculate'.") 'Display message box to inform user that report has been saved. End Sub Not sure how to make this more efficient or to prevent this error.

  Read the article

• Separate "Year" to several worksheets according to one column....

  - by HACHI

  hello! This task is driving me mad... please help! Instead of manually type in the data, i have used VBA to find the year range, put into one column and delete all duplicate ones. But since excel could give more than 20 years, it would be tedious to do all the filtering manually. AND, now i need excel to separate the rows that contain the specific year range in any one the three columns and put them into a new sheet. e.g. The years that excel could find in the three columns(F:H) are ( 2001,2003,2006,2010, 2012,2020.....2033).. and they are pasted in column "S" in sheet 1. How could i tell excel create new sheets for the years ( sheets 2001, sheets 2003, sheet2006....),search through column (F:H) in sheet 1 to see if ANY of those columns contain that year, and paste them into the new sheet. To be more specific, in the newly created "Sheet 2001", the entire row where column(F:H) contains "2001" should be pasted. and in the newly created "Sheet 2033", the entire row where column(F:H) contains "2033" should be pasted.. Enclosed please find the reference. http://www.speedyshare.com/files/23851477/Book32.xls I have got sheet "2002" and "2003" here as results but for the real one i will need more years' sheets (as many as how many excel could extract in the previous stage; as shown in column L ) ...... I think this task should be quite usual (extracting by date), but i couldn't google the result....Pleas help!!I am very clueless about how to do LOOPING.. so please advice and give in more details! Thanks

  Read the article

• How to Setting Non-scrolling Rows/Columns

  - by joe

  Here things i want to do .. I am having Colum A...Z I am having data in Row 1..10 A - is Title From B ..Z . I am having differnet values . When i scroll into Z colum is hided . I want to show my A ( title ) all times even if i scrolled . How to acheive this in Excel 2007

  Read the article

• Help to bypass password for sample projects of excel.

  - by Munna

  Hello Friend.. I am doing project in EXCEL VBA. i am taking refernce of some projects made in excel vba. But when I trid to open that excel sample projects, it ask me for password. so how can bypass password for that excel project so i can take refrence so that i can take refrence of sample. Please help....

  Read the article

• Is there a way to sort digits within a cell?

  - by Iszi

  I'd like to know if there's an Excel Function, or a formula, that can be used to sort digits within a cell. For example, if the value of A1 is 6193254807 and B1 has our formula with parameters set to sort ascending then B1 should equal 0123456789 (leading zero included). Of course, if the parameters were set so that it would sort descending then B1 should equal 9876543210. Is there a way to do this, without wandering into VBScript, or macros, or something similarly exotic?

  Read the article

< Previous Page | 32 33 34 35 36 37 38 39 40 41 42 43  | Next Page >