all Excel file freezes when I try to save
I'm on a mac and all my issues with excel first started when I tried saving a sheet from a file that had special read/write permissions on (my document). After I copied over this sheet to a separate excel document/ blank sheet, I tried to save the new file as a new excel document. When I pressed "save as" and then "save" the entire save box turned grey and froze. It froze for an infinity amount of time and now whenever I try to save the document it freezes every time (also, it automatically does "save as" function regardless if you press command + s). I now have to force quit the document every time in order to get the frozen sheet to go away. THEN I tried opening a totally separate file (new blank docs, other spreadsheets, etc.) on excel and now NOTHING--ZERO EXCE FILES- save properly. EVERYTHING freezes when I try to save it. I went and checked for microsoft updates (because I have it set to manual check) and updated the latest versions/updates available. STILL NOTHING SAVES. What else can I do? Any help would be super appreciated.
Transfer date to a table
So at work we keep a long spreadsheet of all the enquiries we get, when we get them, if we win them so on and so forth. At the minute it’s just one long spreadsheet with one enquiry after the next. What I need to do is transfer it to a spreadsheet, but I need to link it so we see how many enquiries we get each month, how many we win and how much they’re for. How do I do that?
Need Help with Sorting (Macro)
Hi All, I'm trying to run the Macro below but it crashes every time it get to the highlighted .Apply line. I have no idea why since it did not crash on the first .Apply line. Can someone please point me to the right direction? Thanks Dim arng As Range Dim brng As Range Dim crng As Range Set arng = wsSAdj.Range("A:A") Set brng = wsSAdj.Range("J:J") Set crng = wsSAdj.Range("S:S") For Each icell In brng.Cells If Right(icell.Value, 5) = "Total" Then Worksheets("Slides - ADJ").Sort.SortFields.Add Key:=Range( _ icell.Offset(-1, 3), icell.Offset(-1, 3).End(xlUp)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Slides - ADJ").Sort .SetRange Range(icell.Offset(-1, 1), icell.Offset(-1, 1).End(xlToRight).End(xlUp)) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End If Next For Each icell In crng.Cells If Right(icell.Value, 5) = "Total" Then Worksheets("Slides - ADJ").Sort.SortFields.Add Key:=Range( _ icell.Offset(-1, 3), icell.Offset(-1, 3).End(xlUp)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Slides - ADJ").Sort .SetRange Range(icell.Offset(-1, 1), icell.Offset(-1, 1).End(xlToRight).End(xlUp)) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End If Next
Need help with concatenate formula
I've managed to put together a spreadsheet because I'm trying to reduce time it takes to do manual data entry. I've tried some VBA but my skills are no where near what they should be to use it. My formula skills are okay, but I learned it on my own so my knowledge is limited at best. To keep it brief, I work for a newspaper as a data entry clerk in the sports department. We have to enter schedules in their system Adobe InCopy. Obtaining the schedule data requires manual lookup on specific high school athletic websites. Example: I manually go to high school A website athletic schedule and find out who they are playing a certain day. I then go into the company system and manually type team A at team B, 4:30 p.m. ... Yes, typing the periods in between pm is quite tedious. In the spreadsheet I put together, I managed to allow typing a number that represents the school so instead of type out the word "team A" I type "1" and that specific team enters into a specific cell. Likewise for the team they are playing and the comma and time. The time is also represented by a certain number; i.e. 3=3:30 p.m. After typing the numbers in that represent the words, I created another cell that includes those words all concatenated together in one cell. Ergo, once I'm done typing the numbers, the sentence of "team A at team B, 3:30 p.m." is ready to be copy and pasted into the company system. It has shaved off approximately 20 to 40 seconds of data entry saving a ton of time. Now what I'm trying to do is hit up a different part of entering these schedules. Sometimes these schools have a tournament amongst multiple schools. When this happens the data entry appears as such: "Lincoln High School tournament (team A, team C, team F), 2 p.m. The data in the parenthesis can vary from 1 school to as much as 14 schools. I managed to create something similar to the above method of entering a number and then the data generates in a concatenated cell. When I did this, I used rows H25 all the way down to H37, allowing the user to enter the number of the school and then having it generate over to the concatenated cell. The problem I'm having, is that (as you will notice in my formula) when I delete a number that represents a school, the field where the concatenated data comes from turns to #N/A, thus completely removing the entire concatenated cell to an error showing the #N/A in the cell where the concatenated data should be. I've gotten as far as using IFNA and ISNA along with other formulas like TEXTJOIN and ISBLANK and the furthest I got where I'm pretty much stuck is IFNA and/or ISNA don't fully read the cell range of H25 to H37. The data that appears in the concatenated field only reads two schools (1 of which is randomly from the middle of the list). If someone can help me here that would be great. I'm trying to get it so that the list from H25 to H37 is variable to the user and they can type in whatever schools they need to and for the concatenated cell to ignore blanks when there isn't anything entered in, for example cells H30 to H37. In my example only cells H25 to H29 would be filled with a number representing a school and the format I explained earlier in the concatenated cell would appear like so: "Lincoln high school tournament (team A, team C, team F, team G), 2 p.m." Here are the formula's I'm trying to use, however if anyone knows of a better one or more efficient please let me know: Original one I created (it works but I can't delete anything out of cells H25 to H37): =CONCATENATE(E21," ","(",G21,","," ",H25,","," ",H26,","," ",H27,","," ",H28,","," ",H29,","," ",H30,","," ",H31,","," ",H32,","," ",H33,","," ",H34,","," ",H35,","," ",H36,","," ",H37,")",",",H21) 2nd one: This was the closest I came to making it work with IFNA, but it doesn't read all the schools in H25 to H37: =CONCATENATE(E21," ","(",G21,","," ",(IFNA(H25&",",""&" "&(IFNA(H26&",",""&" "&(IFNA(H27&",",""&" "&(IFNA(H28&",",""&" "&(IFNA(H29&",",""&" "&(IFNA(H30&",",""&" "&(IFNA(H31&",",""&" "&(IFNA(H32&",",""&" "&(IFNA(H33&",",""&" "&(IFNA(H34&",",""&" "&(IFNA(H35&",",""&" "&(IFNA(H36&",",""&" "&(IFNA(H37&")",""))))))))))))))))))))))))))) Thank youHelp with IF function. Please help!
I know this should be easy but we had no help at all with this. Here are the instructions for this step: "Insert an IF function in cell E5 to calculate the total due. If the student has chosen to personalize the ring, there is an additional charge of 5% located in cell B21 that must be applied; if not, the student only pays the base price. Use appropriate relative and absolute cell references." Thanks
Trying to get Multiple Columns and Rows to Come Out of an IF Statement
I am trying to make a spreadsheet in Excel that will display multiple rows and columns if an IF statement is true. For example, we are making a machine that has different zones, and different parts associated with each zone. So if one machine has 10 zones, I want a list of coordinating part numbers, quantities and part descriptions to show up. If I choose a machine with 15 zones, I want the corresponding information to show up for that. Is this possible?
Conditionally formatting text
Hi experts, I want to conditionally format the contents of the cells in one column based on it if mates the contents of the cell in the column next to it. There is an order number in the first column, and I want to format the batch numbers in the second column on if they match the order number or not. E.g. A3: 1732987 B3: 32987 I want to go green as it matches A4: 1732654 B4: 132458 I want to go red as it doesn't match All of the order numbers and batch numbers are different, How would I go this please?