|
OK so I have yet another question, and if I find this one minutes after posting I'll be kinda angry because I've been at this for almost 2 hours. The long and short of it is Excel takes all this data and information then turns it into paragraphs. After that it opens up outlook, throws in some email addresses, drafts a body with the paragraphs and sends it. my problem is that there's no way I can find to keep the formatting when I do that, all of the text is just plain. I know I can use send keys and I know I can copy a cell and it will keep. I also know I can turn off grids etc. and just copy all the words like a picture into the email--but for specific reasons, I need all of the words to end up compiling into one cell that looks like: =A4&B4&C4&D4&E4&G4&H4 and then move as text into the email. I've tried string searches to format certain bits, but Excel literally will not format anything past the 256th character. Any ideas on how to either format a huge range's words individually or have excel throw words (just the value of a cell really) into an email while keeping the bold/color etc? EDIT: At this point I am just going to leave my posts in preview mode for an hour or two instead of cluttering a thread up. Anyway, in case anyone is wondering, I just modified my code to include HTML body below and snuck in HTML tags in between the &'s. so here was the code I was using, all I did was change .body to .html body and put the code in the formula as a string. code:
FAN OF NICKELBACK fucked around with this message at 07:59 on Feb 8, 2014 |
# ? Feb 8, 2014 01:36 |
|
|
# ? May 23, 2024 15:26 |
|
Hey guys! I'd also like to chime in and offer my gratitude for the selflessness our Excel experts have granted in order to help us all with our issues. We'd be lost without it-- no doubt about it.
Knot My President! fucked around with this message at 00:18 on Feb 11, 2014 |
# ? Feb 10, 2014 23:47 |
|
Ok, so generally I am comfortable with Excel and VBA, but not coding for outlook. Does anyone know of good resource? Basically, at my new job what ends up happening is that I have to send 35 personalized emails pretty often, and my predecessor would click new email 35 times, write in the 35 names (and the appropriate cc), then paste the contents of one of 35 excel spreadsheets into said emails. So while this obviously seems like something to be automated, I've just never coded for outlook before, and it's a Bad Thing if people get the wrong emails (sales reporting, more or less).
|
# ? Feb 11, 2014 13:04 |
|
Turkeybone posted:Ok, so generally I am comfortable with Excel and VBA, but not coding for outlook. Does anyone know of good resource? You can do this without VBA, it's called a "mail merge". You create a template and use the mail merge tool to fill in the blanks from your Excel or Access database. Details here: http://office.microsoft.com/en-us/word-help/use-mail-merge-to-send-personalized-e-mail-messages-to-your-e-mail-address-list-HA010109162.aspx
|
# ? Feb 11, 2014 15:18 |
|
I'm having some trouble getting a stacked bar chart to behave the way I want it to. This is what I keep getting, but it isn't what I want: I want: The Dates on the X-Axis $ dollar amounts on the Y-Axis The series labels to be shown in $ dollars ... and the stacked bars to vary in height according to their total dollar amount. How the heck do I get this chart to behave the way I want it to? melon cat fucked around with this message at 21:42 on Feb 4, 2024 |
# ? Feb 11, 2014 22:01 |
|
melon cat posted:I'm having some trouble getting a stacked bar chart to behave the way I want it to. This is what I keep getting, but it isn't what I want: Do you have an example of your data? Easier to just mess with that. Generally, you're going to want your dates going down the rows, your series labels going across, and values in all the other cells. Example:
|
# ? Feb 11, 2014 22:40 |
|
melon cat posted:I'm having some trouble getting a stacked bar chart to behave the way I want it to. This is what I keep getting, but it isn't what I want: There are a few types of stacked bar charts. You picked one that always adds to 100%. Right click -> Change Chart Type -> Stacked Column
|
# ? Feb 11, 2014 23:04 |
|
Good morning! Trying to consolidate data into a pretty, functional fashion for some charts. Here's an example of the data I need to combine/sum/whatever: Zabba 1/29/2014 3343 Cobol 1/29/2014 838 Zabba 1/29/2014 714 Cobol 1/29/2014 560 Zabba 1/29/2014 134 Cobol 1/29/2014 100 The list goes on and on and on and on. As you can see, I've got multiple data values for the same date and client name. Further on in the table, the dates change. There are also many many different client names. Is there an easy way to automatically combine the values based on client name and date? So, like, if the client is Citi and the date is 1/29/2014, I'd want to sum all associated values. SUMIFS...might work, but I think it's a bit too complicated for my particular situation. I'm hoping for an easier solution that won't require as much manual work. I want to keep this as simple as possible before I hand the workbooks off to someone who's much less tech savvy. Excel is not really my area of expertise, either, but I can at least look at and understand the formulas. This lady can't.
|
# ? Feb 12, 2014 15:13 |
|
Have you tried using pivot charts yet? Most complex sumifs are solved by that.
|
# ? Feb 12, 2014 16:36 |
|
Veskit posted:Have you tried using pivot charts yet? Most complex sumifs are solved by that. Thank you. This'll do the trick.
|
# ? Feb 12, 2014 17:08 |
|
DukAmok posted:Do you have an example of your data? Easier to just mess with that. Generally, you're going to want your dates going down the rows, your series labels going across, and values in all the other cells. And even if I could get the bars to vary in height, the question remains- how do I get the stacked bars to show the %, but the vertical axis to show the dollar amount? esquilax posted:There are a few types of stacked bar charts. You picked one that always adds to 100%. melon cat fucked around with this message at 21:25 on Feb 12, 2014 |
# ? Feb 12, 2014 21:18 |
|
melon cat posted:I tried that, but now the charted data looks even more bizarre. The bars are super-skinny, it repeats the X-Axis categories dates (Jul-13, Jul-13, Jul-13...), and the bars won't line up with X-Axis categories. There are three types of column charts (whether 2D or 3D): Grouped, Stacked, 100% Stacked. Your initial chart was using 100% Stacked which is why each column was exactly the same height since they all equalled 100%. Your second one with the thin bars was Grouped as each value was an independent column over the label in the x-axis. What you are asking for is the normal Stacked column (left image in the screenshots below). That will put each $ bar on top of each other but then keep them to scale with the following months. HOWEVER, I do believe the only label options are series name or value, not percentage.
|
# ? Feb 12, 2014 21:36 |
|
OK, so I'm taking baby steps back into VBA, but this is a VBA issue in Access. I want a pop up to happen when I add a record using a form. I just did the wizard, and it created the macro for me. here's what I got. Which i know is baby vba and this weird 2010 environment I've never worked with EITHER WAY. Is the macroerror<>0 only for when this clock action goes unsuccessfully? Do i then do an "add else", and if that's what do I do what Do i look to add in next to have a pop up window that says that the click went through successfully?
|
# ? Feb 12, 2014 23:52 |
Has anyone ran into auto calculate not working properly after setting it to manual in a macro? It says it's automatic under options, but won't actually calculate anything until you type something into a cell or press the backspace. Pressing delete does nothing.
|
|
# ? Feb 13, 2014 00:19 |
|
I want to make a spreadsheet where I have three columns: WEEKLY, MONTHLY, and YEARLY. I want to be able to enter in a number into say a MONTHLY row, and then have Excel be smart enough to automatically calculate =VALUE*12 and put it into YEARLY and =VALUE*12/52 and put that into WEEKLY. I also want it to be flexible/smart enough so that if I typed in VALUE into WEEKLY then it would automatically do =VALUE*52 and =VALUE*52/12 and put them into the appropriate columns in the row. Same goes if I typed in something into YEARLY. Can excel do that? e: The motivation is e.g., Costco membership is $55/year whereas Tonx Coffee is $6/week, so I just want to enter in my values into the right column and have it auto calculate the other values.
|
# ? Feb 13, 2014 02:21 |
|
I have a dropwdown-list with several options. I want to grab different variables from another sheet depending on what option I choose. Can someone point me in the right direction? Also I want Excel to make several worksheets depending on a date range I enter in two different cells, eg. 01.01.01 - 10.01.01, giving me 10 similar sheets. Sorry if my explanation suck.
|
# ? Feb 13, 2014 17:09 |
|
Boris Galerkin posted:I want to make a spreadsheet where I have three columns: WEEKLY, MONTHLY, and YEARLY. I want to be able to enter in a number into say a MONTHLY row, and then have Excel be smart enough to automatically calculate =VALUE*12 and put it into YEARLY and =VALUE*12/52 and put that into WEEKLY. I also want it to be flexible/smart enough so that if I typed in VALUE into WEEKLY then it would automatically do =VALUE*52 and =VALUE*52/12 and put them into the appropriate columns in the row. Same goes if I typed in something into YEARLY. You could do it with VBA which triggers when a value on the sheet changes, but I do not recommend that. Instead create enter a fourth column where you enter values like "1.73d", "10w", or "50.25y". Then in the other columns use Daily =if(right(upper(trim($D2)),1)="D",left(trim($D2),len(trim($D2))-1)+0,if(right(upper(trim($D2)),1)="W",left(trim($D2),len(trim($D2))-1)/7,if(right(upper(trim($D2)),1)="Y",left(trim($D2),len(trim($D2))-1)/365,""))) Weekly =if(right(upper(trim($D2)),1)="D",left(trim($D2),len(trim($D2))-1)*7,if(right(upper(trim($D2)),1)="W",left(trim($D2),len(trim($D2))-1)+0,if(right(upper(trim($D2)),1)="Y",left(trim($D2),len(trim($D2))-1)/52,""))) Yearly =if(right(upper(trim($D2)),1)="D",left(trim($D2),len(trim($D2))-1)*365,if(right(upper(trim($D2)),1)="W",left(trim($D2),len(trim($D2))-1)*52,if(right(upper(trim($D2)),1)="Y",left(trim($D2),len(trim($D2))-1)+0,"")))
|
# ? Feb 14, 2014 00:04 |
|
Hi, I'm back! As per my old question re: mailmerge.. the outlook 2013 mail merge sucks, so I found/modified some code to break things up and email the way I wanted. This question comes from a friend of mine.. I feel like there's a way I can do it using now() and some data validation or something, but I'd like another opinion. "Is there a macro I can apply to excel, where it will highlight a cell that has been recently edited but will change it back to unhighlighted if it hasn't been touched in like, a week?" Like.. mentally I'm thinking there's an "onchange" aspect to each cell, so I could do "onchange, put now() into this cell, and then data validate off of that," but I'm not sure if that actually exists. EDIT: Oh it looks like Worksheet_Change is the event I'm looking for, I'll mess with that.. Turkeybone fucked around with this message at 00:43 on Feb 25, 2014 |
# ? Feb 24, 2014 23:28 |
|
My employer has requested that I (after already making it perfectly clear that I don't know squat about Excel aside from making graphs) make an attempt at updating a bit of Excel VB scripting that a previous employee had written. The Excel script basically turns this: Into this: The code in question can be found here: http://pastebin.com/pxTMDPLm Can anyone please please tell me which part of that script I need to remove to make it NOT parse the second (phone #) column?
|
# ? Mar 3, 2014 23:14 |
|
TheEffect posted:My employer has requested that I (after already making it perfectly clear that I don't know squat about Excel aside from making graphs) make an attempt at updating a bit of Excel VB scripting that a previous employee had written. The Excel script basically turns this: The script isn't parsing the second column, it's jut grabbing that whole block and dumping it into your spreadsheet. It's probably easiest for you to just delete B5:B1million after the rest of the data is dumped in there, moving the column to the left.
|
# ? Mar 3, 2014 23:19 |
|
EAT THE EGGS RICOLA posted:The script isn't parsing the second column, it's jut grabbing that whole block and dumping it into your spreadsheet. I really appreciate your help and direction with this. After fiddling around for hours failing to use "offset" I finally ended up with this- ActiveSheet.Range("C5", "C65536").Copy ActiveSheet.Range("B5", "B65536").PasteSpecial ActiveSheet.Range("C5", "C65536").ClearContents And it works great! Thanks again for your help.
|
# ? Mar 4, 2014 16:07 |
|
A question about creating charts using number data. I'm using Excel 2010. Every time I create a chart (Select Data > F11 to create pie chart in new worksheet) Excel keeps creating a pie chart with a lot of white space. But I don't want that white space, and it won't let me resize the area outside of the bounding box. Why is it doing this, and how can I eliminate that white space?
|
# ? Mar 5, 2014 21:34 |
|
melon cat posted:A question about creating charts using number data. When you create a chart in a separate sheet, the size of the sheet is dependent on the page size. So it's 8.5x11 by default. You can change the size of the whitespace by changing the page dimensions. Not sure how to stop that though, or if it's possible to create a square page. Maybe adjusting the margins would work. edit: Yeah, adjusting the margins works. Just set the right margin to 3.3" or whatever and it becomes a square esquilax fucked around with this message at 22:06 on Mar 5, 2014 |
# ? Mar 5, 2014 21:58 |
|
esquilax posted:When you create a chart in a separate sheet, the size of the sheet is dependent on the page size. So it's 8.5x11 by default. You can change the size of the whitespace by changing the page dimensions. I tried to centre-align the chart so it doesn't look too terrible, but the heck do you Centre-align charts, any way? All of my alignment tools are grayed out.
|
# ? Mar 5, 2014 22:43 |
|
What are you intending to do with this chart? Print it? Embed it in another document? Just show it to someone in Excel?
|
# ? Mar 5, 2014 22:48 |
|
melon cat posted:Unfortunately that solution isn't working for me. It looks exactly the same. Not sure why it's not working for you, I just did it again in excel 2010. You're going Page Layout->Margins->Custom Margins->Right=3.3" correct? If you're really concerned just embed it into another worksheet instead of making it a sheet by itself, it should be more workable.
|
# ? Mar 5, 2014 22:54 |
|
I have a growing sheet whence I need to report. In it there is a range of about 8000 rows by 200 columns. I need an ordered list of all values from that range, along with a count and the row number of the occurrences. (values "Value" are not repeating on rows) Could solve it by simply iterating over the range and copy all non-blank values to a columns in a sheet, and then use "remove duplicates" on that, and then again run some other some other macros or countif on that. B-B-But that seems less elegant than a fedora. This is something I've wanted to learn for a long time, and my pseudo code is something like for i from firstRow to lastRow for j from firstColumn to lastColumn if cells(i,j).value <> "" then if cells(i,j).value IS NOT IN RANGE/LIST/ARRAY CALLED UNIQUE VALUES then UNIQUECOUNT = UNIQUECOUNT + 1 RANGE/LIST/ARRAY CALLED UNIQUE VALUES(UNIQUECOUNT)=(Cells(i,j).value) OCCURENCES(VALUE) =OCCURENCES(VALUE) + 1 VALUEOCCURRENCESADDRESS = i else OCCURENCES(VALUE) =OCCURENCES(VALUE) + 1 VALUEOCCURRENCESADDRESS = i endif next next finally Sort or something in that vein. Howto? sofokles fucked around with this message at 18:58 on Mar 6, 2014 |
# ? Mar 6, 2014 18:42 |
|
I say this all the time, but why wouldn't a Pivot table work in this situation?
|
# ? Mar 6, 2014 18:58 |
|
because i hate them. click click click. It is not I who need the results. What I need is an enjoyable way of getting them. Pivot tables are boring.
|
# ? Mar 6, 2014 19:33 |
|
what about P-p-p-p-p-p-p-pooweerrr Pivot?
|
# ? Mar 6, 2014 20:20 |
|
see now you re saying unknown words. all of a sudden I feel curious
|
# ? Mar 6, 2014 21:19 |
|
Bro do yourself a favor and learn power pivot. You can either install it onto your excel 2010, or it comes in the newer versions. It's semi complicated, has its own programming language, and is loving powerful. It'll do everything you want and more! THE POSSIBILITIES GO FORTH.
|
# ? Mar 6, 2014 21:28 |
|
"Don't just crunch numbers. CRUSH THEM." Ok i'm in for the ride. Wanna be overlord. Thanks for protip.
|
# ? Mar 6, 2014 21:31 |
|
Veskit posted:Bro do yourself a favor and learn power pivot. You can either install it onto your excel 2010, or it comes in the newer versions. It's semi complicated, has its own programming language, and is loving powerful. It'll do everything you want and more! THE POSSIBILITIES GO FORTH. MS did the absolutely stupid thing of removing power pivot from the 13 versions of Excel unless you stump up for the enterprise or stand alone versions.
|
# ? Mar 7, 2014 04:19 |
|
sofokles posted:
code:
FAN OF NICKELBACK fucked around with this message at 05:52 on Mar 7, 2014 |
# ? Mar 7, 2014 05:47 |
|
FAN OF NICKELBACK posted:
I'll invite chicks to marvel at my new collection object, that's for sure.
|
# ? Mar 7, 2014 19:20 |
|
For reasons beyond my control, I have a 600Mb CSV file I need to wrangle with for work, and in particular I need to sort and search within it. Obviously, this is way too bloated for Excel to even look at, and even if it weren't, the number of columns (and also possibly rows) is above the document limit and will cause Excel to crap out anyway. I've found a program called Delimiter that will allow me to view the file, but because it only accesses parts at a time, I still can't search it, edit it, or filter it down into something usable. Does anyone know a good solution for this, bearing in mind that I am not a coder for a living, do not normally have to deal with this, and got a C+ in my intro to Java course in college?
|
# ? Mar 19, 2014 01:11 |
|
Brain In A Jar posted:For reasons beyond my control, I have a 600Mb CSV file I need to wrangle with for work, and in particular I need to sort and search within it. Obviously, this is way too bloated for Excel to even look at, and even if it weren't, the number of columns (and also possibly rows) is above the document limit and will cause Excel to crap out anyway. Do you have MS Access? You could import it into there with the CSV import tool, take out what data you don't need, then export it back into excel.
|
# ? Mar 19, 2014 01:18 |
|
Smithersnz posted:Do you have MS Access? You could import it into there with the CSV import tool, take out what data you don't need, then export it back into excel. Access also can't open it because the column count exceeds 255.
|
# ? Mar 19, 2014 01:28 |
|
|
# ? May 23, 2024 15:26 |
|
Brain In A Jar posted:For reasons beyond my control, I have a 600Mb CSV file I need to wrangle with for work, and in particular I need to sort and search within it. Obviously, this is way too bloated for Excel to even look at, and even if it weren't, the number of columns (and also possibly rows) is above the document limit and will cause Excel to crap out anyway. Try Qlikview (they have a free version which you can download from their site). You can use the built in wizard to load the data instead of coding with their twist on SQL. Once the data is loaded, choose the fields you want to filter on and create a table object for the fields you want to export. The table object will have an option to export back to Excel based on whatever matches your filters. If the result is more than 65535 rows, it automatically changes the output to CSV.
|
# ? Mar 19, 2014 02:13 |