|
How about you paste it in to PowerPoint as a table instead of an image? It will make it easier for other people to update to update slides.
|
# ? Aug 8, 2013 22:52 |
|
|
# ? May 13, 2024 11:08 |
|
It used to be the case that pasting excel tables in PPT would be awful and mess everything up. But apparently it solves my problems if I paste a table using "Keep Source Formatting" (Alt-h-v-k) which shows up under Paste Options and not the Paste Special menu. New PPT appears to be a lot better at dealing with Excel->PPT table pasting, so that's what I'm going with. Thanks.
|
# ? Aug 9, 2013 00:31 |
|
Is there a way to convert a date value, such as 31-Aug, into the day of the week ("Saturday")? The only way I can think of to do it is to create a lookup table with the named day for each date value, then use the DAY function and a lookup function to convert each date value into the day of the week. That seems way too complicated for something so simple, but Excel's date formats don't seem to include a day name format. edit--Google's my friend; I'm a dumbass. Ron Don Volante fucked around with this message at 03:11 on Aug 12, 2013 |
# ? Aug 12, 2013 03:08 |
|
I have a worksheet that has a formula: =AND(VALUE($E$1)-VALUE(A4)>0,VALUE($E$1)-VALUE(A4)<=7) $E$1 is todays date and column a is a vertical list of dates so I'm trying to find a particular date in that list that is 7 or less days ago but not today and apply conditional formatting to it. Problem is I have to do this for someone with a Mac and he's not seeing the formatting even though I can. I'm guessing it has something to do with the 1462 day difference in the two programs date systems (Mac uses a 1904, Windows 1900) but adding +1462 didn't seem to help.
|
# ? Aug 12, 2013 21:51 |
|
wikipe tama posted:I have a worksheet that has a formula: Not sure what is going wrong for you, but you can just apply 2 of the pre-built formats - highlight days within last 7, then format Today back to default.
|
# ? Aug 12, 2013 23:40 |
|
wikipe tama posted:I have a worksheet that has a formula: Zerodot has a better solution, but try replacing VALUE() with DATEVALUE().
|
# ? Aug 12, 2013 23:45 |
|
Huh it looks like datevalue did the trick Pegged Lamb fucked around with this message at 01:11 on Aug 13, 2013 |
# ? Aug 12, 2013 23:48 |
|
Is there an easy way to make Excel calculate and display ratios? For example if A1 = 8, and A2 = 24, I want A3 to show 1:3. But I can't find any easy way to do this (or do it at all, for that matter...). Suggestions? EDIT: \/ Success! Thanks guys. melon cat fucked around with this message at 04:30 on Aug 14, 2013 |
# ? Aug 13, 2013 03:07 |
|
melon cat posted:Is there an easy way to make Excel calculate and display ratios? =SUBSTITUTE((TEXT(A1/B1,"?/?")),"/",":") It takes the value and turns it into a fraction, then removes the / and puts in a :. Rusty as gently caress in Excel I need to frequent this thread more.
|
# ? Aug 13, 2013 03:27 |
|
melon cat posted:Is there an easy way to make Excel calculate and display ratios? You can calculate the ratio manually, and manipulate the text so that it shows up in that format. Essentially: =A1/GCD(A1, A2) & ":" & A2/GCD(A1, A2)
|
# ? Aug 13, 2013 03:28 |
|
The issue with both of those is that your result is text, not a number. However, Excel will gladly parse it to a number if you use it as one...but it won't be what you expect: 3*"1:3" is 0.13125, not 1. There is a number format that displays numbers as fractions ('#" "?/?' and similar, it's built-in), which will make arithmetic work correctly (well, it will display the fraction approximation up to the specified size, but use the exact number in computations). But I'm not aware of any way to make it work with ":" instead of "/".
|
# ? Aug 13, 2013 11:49 |
|
You could use Range.Value = Replace(Range.text, "/", ":") but then you'd lose the ability to do math with the values since they would have to be converted to text.
|
# ? Aug 15, 2013 21:33 |
|
ShimaTetsuo posted:The issue with both of those is that your result is text, not a number. However, Excel will gladly parse it to a number if you use it as one...but it won't be what you expect: 3*"1:3" is 0.13125, not 1. I haven't tested it out, but how about '#" "?:?' as a custom number format? EDIT: It doesn't work in Excel 2007. Old James fucked around with this message at 01:02 on Aug 16, 2013 |
# ? Aug 16, 2013 00:59 |
|
This has got to be the stupidest question I've ever asked, but I can't think of a better way to word it to Google the answer. When creating a scatter plot of a large array of data, the plot always appears at the bottom of the array in Excel. For example, a 2 x 1500 array of data that I turn into a scatter plot, the plot appears around row 1500 (bottom of the array). To bring the plot to the top I have to click and drag the plot to the top. This is a slow and tedious process. How can I get the plot to the top of the spreadsheet quickly or am I just retarded with creating charts in Excel?
|
# ? Aug 16, 2013 22:48 |
|
You can cut and paste charts, it will paste so that the upper left hand corner starts in the upper left hand corner of the cell you have selected.
|
# ? Aug 16, 2013 22:53 |
|
ZerodotJander posted:You can cut and paste charts, it will paste so that the upper left hand corner starts in the upper left hand corner of the cell you have selected. I am a moron. Thanks!
|
# ? Aug 16, 2013 22:54 |
|
Old James posted:I haven't tested it out, but how about '#" "?:?' as a custom number format?
|
# ? Aug 20, 2013 22:07 |
|
Is it possible for an Excel VBA macro to interact with an external program, in this case a web browser? I have a macro that generates the javascript code, in Excel, for filling out a web form. I'd like for Excel to send that code directly to the browser and execute it.
|
# ? Aug 23, 2013 00:06 |
|
gandlethorpe posted:I'd like for Excel to send that code directly to the browser and execute it. I just ripped this off some forum, haven't tested it because this is a terrible idea code:
|
# ? Aug 23, 2013 03:09 |
|
Can I ask what the danger is?
|
# ? Aug 23, 2013 15:49 |
|
I have zero experience with Excel, but my company has given me the task to update an Excel file that calculates how much money each department has blown using our copyshop. For the most part it works fine, but as when the costs get too high (above a thousand Euros), Excel seems to freak out. e: solved it, the width of the table wasn't big enough. Grawl fucked around with this message at 02:04 on Aug 29, 2013 |
# ? Aug 28, 2013 11:53 |
|
I've run into an annoying issue with a spreadsheet I created a while back: When I set up this spreadsheet, I made all of the cells below row #121 hidden (Ctrl + Shift + down arrow > Right click > Hide). But I've run out of space on this spreadsheet, and I want to unhide the cell Iv'e hidden from view. But when I right click > unhide, my hidden cells won't re-appear. And any new rows I add won't appear, either. How do I fix this?
|
# ? Sep 3, 2013 20:24 |
|
The UI isn't very intuitive but what you need to do is click on the heading for the bottom row (the 121) and drag down, as if there were more rows available to select there. It'll give you the little tooltip that's like "1 million rows selected" and then you can unhide.
|
# ? Sep 3, 2013 20:52 |
|
ZerodotJander posted:The UI isn't very intuitive but what you need to do is click on the heading for the bottom row (the 121) and drag down, as if there were more rows available to select there. It'll give you the little tooltip that's like "1 million rows selected" and then you can unhide.
|
# ? Sep 3, 2013 21:56 |
|
I'm using Excel 2010. Imported data from a text file, one of the columns is date and time listed as MM/DD/HHZ (as in Zulu time). I need to convert it to a recognized format for chart reasons. So for example, 08/13/15Z -> 08/13 15:00. Is there a way to do that?
|
# ? Sep 10, 2013 18:00 |
|
Fucknag posted:I'm using Excel 2010. Imported data from a text file, one of the columns is date and time listed as MM/DD/HHZ (as in Zulu time). I need to convert it to a recognized format for chart reasons. So for example, 08/13/15Z -> 08/13 15:00. Is there a way to do that? Assuming that everything is going to be in MM/DD/HHZ format, and that the year is always going to be 2013, you could use the following (assuming your imported text value is in A1): =DATE(2013,MID(A1,1,2),MID(A1,4,2)) + TIME(MID(A1,7,2),0,0) This will convert it to Excel's numeric date/time notation, which you can then format as you please, use in charts, etc.
|
# ? Sep 10, 2013 18:10 |
|
docbeard posted:Assuming that everything is going to be in MM/DD/HHZ format, and that the year is always going to be 2013, you could use the following (assuming your imported text value is in A1): Looks like that did the trick, thanks!
|
# ? Sep 10, 2013 18:46 |
|
Fucknag posted:I'm using Excel 2010. Imported data from a text file, one of the columns is date and time listed as MM/DD/HHZ (as in Zulu time). I need to convert it to a recognized format for chart reasons. So for example, 08/13/15Z -> 08/13 15:00. Is there a way to do that? In VBA, this is code:
|
# ? Sep 11, 2013 21:00 |
|
I'm having a bit of a problem with VBA and date formatting and I cannot get the drat thing to work. I'm pulling a date from a different piece of software in the format ddmmyy. So 1st March 2013 would be "010313". It comes as a string. I need it to be assigned to a cell in "dd-mm-yy" format. Currently I'm splitting the input into "dd" "mm" "yy" and combining them DateSerial. However, no matter what I try to do VBA\Excel keep getting confused between England and American date formatting. If the day of the date is <=12 it swaps it around so 1st of march would become 03/01/13 instead of 01/03/13. This is more than just a display error however as it then, on later calculations assumes the date is in English formatting. So, assume all dates are 1st March 2013. Going in 01/03/13 (1st March 2013) Passing to Cell 03/01/2013 (Americanised into 1st March 2013) Used in later calculations 03/01/2013 (Sheet now thinks it is 3rd Jan 2013) code:
Checking in the locals window sDate does appear to be stored correctly, so the 1st of March would be stored as "#01/03/13#" it doesn't, however, pass this through correctly to the worksheet. Also gently caress dates, gently caress dates forever. Cast_No_Shadow fucked around with this message at 10:46 on Sep 16, 2013 |
# ? Sep 16, 2013 10:41 |
|
Cast_No_Shadow posted:Also gently caress dates, gently caress dates forever. How are you getting the inputs? Datepart() or Left()/Mid()/Right()? If the first, you are leaving it up to Excel to assume which part is month and day, so try the second.
|
# ? Sep 16, 2013 13:04 |
|
I initially tried slicing the string as mentioned which didn't work. Latest try I've just pulled the two digit inputs separately rather than as a "ddmmyy" 6 digit string, which again doesn't seem to work. Surely there is a way to actually get VBA to recognise that I want to explicitly define this number as the day and this number as the month. I'm telling you exactly how it is, stop making retarded guesses.
|
# ? Sep 16, 2013 13:19 |
|
I just tried it and its returning the correct behaviour. Maybe try WorksheetFunction.Text() instead?
|
# ? Sep 16, 2013 14:25 |
|
Both of these are working for me using Excel2013code:
|
# ? Sep 16, 2013 14:37 |
|
Had a fiddle with your suggestions and still cannot get this to work. I also think I haven't explained myself properly. I have a choice of inputs. All as string. Either "010313" (ddmmyy) Or A trio of "01" (dd) "03" (mm) "13" (yy) Which I'm pulling from a spaghetti mess of a Cobol system from the 70's. I need to turn this into a date variable both VBA and excel understand. Yet nothing I try appears to work. What should happen. 1) I take said input, format it into a date that VBA & excel understand. 2) Input this value into a cell. What does happen. 1) I take said input, format it into a date. VBA seems to know what I mean. 2) I go to put this value into a cell. It is currently formatted dd-mm-yy. 3) When put into the cell it converts it to mm-dd-yy. 4) When later referencing this cell it thinks it is formatted dd-mm-yy but keeps the values as if it were mm-dd-yy. I'll see if I can isolate it and pop up an example book. 2010 version if this matters. I generally know what I'm doing, so I feel stupidly retarded over this. A1 should show 01/03/13 and understand this is dd/mm/yy. Cast_No_Shadow fucked around with this message at 15:37 on Sep 16, 2013 |
# ? Sep 16, 2013 15:23 |
|
Cast_No_Shadow posted:What should happen. Can we see the code you are using at step 4, when the cell is later referenced? Perhaps that code is what is messing up and not the initial conversion.
|
# ? Sep 16, 2013 15:27 |
|
Old James posted:Can we see the code you are using at step 4, when the cell is later referenced? Perhaps that code is what is messing up and not the initial conversion. Popped an example up without any of the later stuff, same problem happening. I'm probably doing something really dumb. [Edit] Uploading that sheet seems to be all hosed. Basically code:
Cast_No_Shadow fucked around with this message at 15:37 on Sep 16, 2013 |
# ? Sep 16, 2013 15:30 |
|
Cast_No_Shadow posted:Popped an example up without any of the later stuff, same problem happening. This is the only code I found in that example code:
Could you just post the code here or somewhere else? I don't like running xlsms I didn't code myself.
|
# ? Sep 16, 2013 15:37 |
|
Sorry about that; I've popped the code above. A1 needs to show dd-mm-yy. Cannot seem to stop it getting confused. Cast_No_Shadow fucked around with this message at 15:44 on Sep 16, 2013 |
# ? Sep 16, 2013 15:39 |
|
This looks to be the problem sDate = Format(DateSerial(sYear, sMonth, sYear), "DD-MM-YY") change it to sDate = Format(DateSerial(sYear, sMonth, sDay), "DD-MM-YY")
|
# ? Sep 16, 2013 15:46 |
|
|
# ? May 13, 2024 11:08 |
|
This is not a VBA problem, it's an Excel problem. It automatically parses everything you put in a cell, which is often wrong. You cannot control this parsing from VBA. You may be able to prevent it from parsing incorrectly by writing it as an explicit date number or a string starting with a single quote (won't parse at all).
|
# ? Sep 16, 2013 15:47 |