|
jusion posted:Is Google Docs an option?
|
# ? May 18, 2012 14:35 |
|
|
# ? May 12, 2024 18:37 |
|
Sonic H posted:My brain has given up on this one. Hopefully a genius here can help. You can make the criteria of the count another offset. code:
|
# ? May 18, 2012 14:54 |
|
Mr. Apollo posted:Not really. The people who will be using it only have Excel. You could use Access to enter the data, then have queries that pull out the data you need for each spreadsheet with the formulas. Then each time you add new data to the main table you run the queries and export as Excel for the recipients.
|
# ? May 18, 2012 14:57 |
|
I am trying to compile a list of people in the company, and the dates of when their various certifications expire. When I enter the dates I want to have an entry made in the outlook calendar for a reminder to have them renew their certs. I have some code I found on that will add the date I list in column A and the person in column B. The problem is that whenever I run the macro, it duplicates the calendar entries. Here is the code that I am trying to use.code:
Dial M for MURDER fucked around with this message at 20:52 on May 18, 2012 |
# ? May 18, 2012 16:10 |
|
Excel is starting to make me really angry. My wife has a spreadsheet she is using to track shelf-life testing she is performing on some samples. She marks the name & the date they started testing. The samples are tested monthly for a year. To complicate things, they want to pull samples every Monday morning - so if a sample went into testing May 1, it would be pulled the week of May 28 - June 1. All I want to do with Excel is to display the samples that should be tested this week: code:
|
# ? May 18, 2012 19:03 |
|
Kim Jong III posted:stuff... In your example, May 28th starts a 7 day period where the majority of that week is in May. You need to find out if the company counted that week as May because the majority of it was in May or just because the first of the week was May. So, would July 30th - August 5th be counted as the last week of July or first week of August? Assuming it would be counted as the last week of July, the following formula would work to flag samples to be pulled this week... code:
code:
|
# ? May 18, 2012 22:24 |
|
Dial M for MURDER posted:Excel -> Outlook I'm sure there's a way to check for an existing meeting, but I don't deal with Outlook much. Instead here's a way to prevent duplicating meetings within Excel using a second sheet. code:
|
# ? May 18, 2012 22:44 |
|
I've got a dumb request from a client that I cant solve. He wants to track a loan repayment, where the repayment amount can vary, as well as the date he pays. Each time a payment is made, he wants to add on the interest for the days since the previous payment. He wants to know exactly how much is left on the loan at any given time. He intends to pay semi-regularly with some large lump payments but also some periods where he pays nothing. I cant figure out how to keep a running total of the balance outstanding. I need to add x days of interest to the balance, and subtract the principal paid each time a payment is made. He intends to update the sheet himself with the date paid and the amount. I've not worked with finance stuff in Excel, and this request sounds pretty stupid, but if someone could take a quick look and where I'm at so far I'd appreciate it. http://dl.dropbox.com/u/58703/runninginterest.xlsx
|
# ? May 22, 2012 09:33 |
|
Swink posted:I've got a dumb request from a client that I cant solve. At a glance, in F6 you want "=F5 - J6" and then fill it down that column. I think you have all the component parts correct. Do you want the top-left to display the balance as of the current date?
|
# ? May 22, 2012 19:31 |
|
Yeah but the top left is unimportant. Thanks - I guess I'd been staring at it too long and I'd lost all my sense.
|
# ? May 23, 2012 01:27 |
|
Old James posted:I'm sure there's a way to check for an existing meeting, but I don't deal with Outlook much. Instead here's a way to prevent duplicating meetings within Excel using a second sheet. Thank you so much, although I am an idiot when it comes to VBA, so how am I supposed to use the sheets now? Dial M for MURDER fucked around with this message at 16:10 on May 23, 2012 |
# ? May 23, 2012 16:02 |
|
Dial M for MURDER posted:Thank you so much, although I am an idiot when it comes to VBA, so how am I supposed to use the sheets now? Sheet1 is the existing list of reminders to create. Sheet2 are reminders already created. When the macro runs it check Sheet2 to see if you had previously created the reminder, if the meeting info is on Sheet2 it doesn't create a new reminder. So unless this would only prevent duplicates going forward (unless you took the time to figure out what meetings were already scheduled) and if the user deletes the reminder this will not recreate that meeting.
|
# ? May 23, 2012 16:30 |
|
I put the code in it's own module and filled in column "A1" with a date/time and "B1" with a name on sheet1. When I go tools -> macro-> run I get and error that sayscode:
code:
|
# ? May 23, 2012 17:16 |
|
Dial M for MURDER posted:I put the code in it's own module and filled in column "A1" with a date/time and "B1" with a name on sheet1. When I go tools -> macro-> run I get and error that says Try changing code:
code:
|
# ? May 23, 2012 17:33 |
|
I have it in there now as code:
|
# ? May 23, 2012 20:27 |
|
Dial M for MURDER posted:I have it in there now as It's basically complaining with the format of the countifs() when it is using a date as a criteria. We may need to include it within "". Try code:
Old James fucked around with this message at 02:05 on May 24, 2012 |
# ? May 23, 2012 20:31 |
|
Still has the same error and highlights that line. Is there an something in I have to reference manually besides the Outlook library?
|
# ? May 23, 2012 21:32 |
|
Are you using 2007? Countifs is only available in 2007 onwards
|
# ? May 24, 2012 07:16 |
|
That's the problem, I have 2003. Sorry I should have stated I have an antique version.
|
# ? May 24, 2012 23:03 |
|
This is more of a 'wouldn't it be nice' kind of thing. I've got a lady I'm helping out who is doing pay per click campaigns. I've pulled a bunch of stuff out of Google Keywords via API into a database, and then given her a spreadsheet. There's a unique ID, a company name, and a keyword. These may be repeated as there could be multiple keywords per uniqueid/keyword. What she does is decide which keyword is 'best' by putting an 'x' into a column. So what she ends up with is a sheet that looks like:code:
a: There is at least one 'x' per uniqueID b: There is not more than one 'x' per uniqueID c: A list of all 'selected' rows, with the NULL selected rows removed This is incredibly easy for me in my SQL database, which I accomplish like so: code:
EDIT-Also, she can't connect to my DB directly via ODBC or anything like that. She's using Excel 2010.
|
# ? May 25, 2012 01:55 |
|
I would make a default pivot table off of the data (select the columns with your data, Insert, Pivot Table, OK) Put the ID (or Name if it's also unique) as your Row Label Put Selected as your Column Label Put Selected as your values Sort the column under X either descending - anything with more than 1 is at the top, anything with 0 is at the bottom. Since we selected all of the columns in the first step when she wants to refresh this all she needs to do is paste the data on top of what is there currently and then go to the pivot table, right click on it, and select refresh. You could also do something with countifs (2007 or 2010) or an array formula (2003), but it will end up with more complicated steps for her to update it each time meaning it's easier for her to make a mistake and need your support again.
|
# ? May 25, 2012 16:00 |
|
Thanks, I can see how that would work. I'd been stuck in the 2003 world so long the only thing I could think of would have been tonnes of countifs and named ranges and so on, and just didn't want to go down that road.
|
# ? May 28, 2012 01:03 |
|
I'll preface this with the fact that I dont know the first thing about databases and SQL, or if this will help you at all, but I bookmarked this post because it seemed useful at the time - http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/ From what I can tell its a very basic instruction on setting up your sheet to accept SQL statements (via VBA).
|
# ? May 28, 2012 04:54 |
|
Scaramouche posted:Thanks, I can see how that would work. I'd been stuck in the 2003 world so long the only thing I could think of would have been tonnes of countifs and named ranges and so on, and just didn't want to go down that road. How automated do you want the double check to be? Shouldn't be to hard to write a VBA function that can do this.
|
# ? May 31, 2012 01:30 |
|
This person is pretty... not smart with Excel (though great at online marketing) so I didn't want to complexify things with allow scripts to run/popups etc. I like the elegance of the pivot as well, it worked out fine.
|
# ? May 31, 2012 02:44 |
|
Scaramouche posted:This person is pretty... not smart with Excel (though great at online marketing) so I didn't want to complexify things with allow scripts to run/popups etc. I like the elegance of the pivot as well, it worked out fine. Good thing you replied quickly I was like 3/4's the way done with a macro to do it. With dynamic arrays and countif's =).
|
# ? May 31, 2012 02:49 |
|
I have a question regarding Google Spreadsheet. Not sure if there's a thread for that, but if there is, I haven't seen it. I'm trying to create a faux hyperlink that takes you to another sheet. For instance, I have a main sheet with a list of names. If I click the cell with the name "Bill" in the cell, I wanna be taken to the sheet titled "Bill" Is this possible in Excel? I'd rather do it in Google docs, but I'd be willing to do it in Excel if Google Docs isn't an option. ZanderZ fucked around with this message at 21:53 on Jun 5, 2012 |
# ? Jun 5, 2012 21:45 |
|
ZanderZ posted:I have a question regarding Google Spreadsheet. Not sure if there's a thread for that, but if there is, I haven't seen it. In Excel when you create a hyperlink you can have it take you to a cell on another spreadsheet. Not sure about Googledocs.
|
# ? Jun 6, 2012 05:56 |
|
Old James posted:In Excel when you create a hyperlink you can have it take you to a cell on another spreadsheet. Not sure about Googledocs. Ok, I'm using Excel then. So I've got the following in Sheet "Master" Cell B2 which displays foobar. =CONCATENATE('Jon'!B2&'Jon'!C2) Sheet "Jon" cell B2 has "foo" written in it, and cell C2 has "bar" Is there any way to have it do a line break, so I get... foo bar or better yet, double spaced... foo bar instead of foobar? \/ Thanks! Turns out it's CHAR(13) for Excel Mac 2011 ZanderZ fucked around with this message at 19:19 on Jun 6, 2012 |
# ? Jun 6, 2012 14:28 |
|
When typeing in a cell alt+enter puts a line break in the text or you can use char(10) so either of the two below would workcode:
code:
EDIT: When using the CONCATENATE formula you separate the values with commas. Using the ampersand symbol is an alternate way of combining strings without using the CONCATENATE formula so it is redundant in your examples. Old James fucked around with this message at 23:48 on Jun 6, 2012 |
# ? Jun 6, 2012 17:26 |
|
Ok, so this concatenate formula is getting pretty long. How can I use something that's better for scripting than the formula bar? I wanna be able to space all this poo poo out like an actual script as opposed to a formula. I wanna be able to have something like... =CONCATENATE ( 'Joe' !B2 &CHAR(13) &CHAR(13) & 'Joe' !C2 &CHAR(13) &CHAR(13) & 'Joe' !D2 &CHAR(13) &CHAR(13) & 'Joe' !E2 &CHAR(13) &CHAR(13) &CHAR(13) & 'Joe' !F2 &CHAR(13) &CHAR(13) ) As opposed to one big line of text. Is this possible? This is my first time doing anything in excel that isn't simple algebra. Right now, with the one line formula bar, it takes longer to copy and paste with a trackpad than it does to just type it out. ZanderZ fucked around with this message at 22:40 on Jun 6, 2012 |
# ? Jun 6, 2012 22:35 |
|
Check out my edit above, you were misusing the '&' within CONCATENATE. Your code should look like this.code:
Also, if you are dealing with a long formula you can expand the size of the formula bar by dragging the bottom edge down.
|
# ? Jun 6, 2012 23:53 |
|
Old James posted:I am not sure why you want those last two char(13)'s since there is no text after them. I figured out the extra char(13)'s and removed them from the formula. I know about expanding the formula bar by dragging the bottom edge down. What I'm referring to is how difficult it is to make multiple edits to a big, long, line of text. Right now, I'm writing my formulas in Text Wrangler so I can go back and quickly edit, line by line. Then I have to go back, remove all the spaces and line breaks and coppy/paste it into the formula bar.
|
# ? Jun 7, 2012 19:36 |
|
Does anybody have any experience with fixing compatibility issues? I don't know how to program but I am working on an Excel spreadsheet that will open .pdf files on my company's intranet and take them to a specific page. I have a code that works on most of the firm's computers (Excel 2003 SP3 with Windows XP. code:
|
# ? Jun 11, 2012 01:38 |
|
Wilhelm posted:Does anybody have any experience with fixing compatibility issues? I don't know how to program but I am working on an Excel spreadsheet that will open .pdf files on my company's intranet and take them to a specific page. I have a code that works on most of the firm's computers (Excel 2003 SP3 with Windows XP. When you open the pdf maually within IE on those other machines and go to a page is the URL different? Perhaps the #page= is no longer the correct syntax in later versions. Just a guess.
|
# ? Jun 11, 2012 02:46 |
|
I was asked about helping someone parse a string of text to pull out some numbers for calculations. The complication here, is that the source strings are typed in by hand by any shmuck, so the number (distance) to pull out may be in "0.12mi", "2.34 mi", "20,3km", or "103,47 km".. Or maybe even with a couple spaces instead of just one. I've never hosed with mixed-up commas and periods being used for decimals, and am wondering if there's anything in Excel which can handle mixed up decimals? I'm thinking it's probably most prudent to nip it in the bud by replacing all commas ahead of time? coyo7e fucked around with this message at 18:44 on Jun 21, 2012 |
# ? Jun 21, 2012 18:41 |
|
coyo7e posted:I was asked about helping someone parse a string of text to pull out some numbers for calculations. The complication here, is that the source strings are typed in by hand by any shmuck, so the number (distance) to pull out may be in "0.12mi", "2.34 mi", "20,3km", or "103,47 km".. Or maybe even with a couple spaces instead of just one. I've never hosed with mixed-up commas and periods being used for decimals, and am wondering if there's anything in Excel which can handle mixed up decimals? That's how I'd approach it as well, try and standardize the comma/period formatting first. I'd eyeball to make sure there weren't any weird big number switcheroos though, like 100,000.00 or 100.000,00, before running a find and replace. DukAmok fucked around with this message at 19:00 on Jun 21, 2012 |
# ? Jun 21, 2012 18:58 |
|
DukAmok posted:That's how I'd approach it as well, try and standardize the comma/period formatting first. I'd eyeball to make sure there weren't any weird big number switcheroos though, like 100,000.00 or 100.000,00, before running a find and replace. I'm the one he's referring to. This is the task that I've been given and I'm having a TON of trouble with it. Here's what the data looks like: https://docs.google.com/spreadsheet/ccc?key=0Am5EbI_l2pbcdFZfdFlGNUIxZ01qNDNBak5wTmlTQmc&pli=1#gid=0 The items in purple is what I'm trying to parse out of each individual message. I need the mileage (with kilometers normalized to miles), the pace (also normalized to miles) and any #tag that isn't #tag1. The tricky part is that all of these messages are different, some of them have commas as decimals, and some have no space between the distance and mi/km. It's kind of a nightmare. This is the excel formula that I have to pull the first number out, but it only works on some of them. Any ideas? code:
|
# ? Jun 21, 2012 20:29 |
|
mattdev posted:I'm the one he's referring to. This is the task that I've been given and I'm having a TON of trouble with it. Here's what the data looks like: Couple questions: does distance always come before rate? Does rate always have a slash after it? Is there always only one tag aside from tag1? I can see a simple VBA script to knock most of this out, PM me if you want to take it offline.
|
# ? Jun 21, 2012 21:26 |
|
|
# ? May 12, 2024 18:37 |
|
Yep, distance always comes before rate but rate doesn't always have a /. Most of the time it does, but sometimes it doesn't. Rate also sometimes doesn't exist, but that's rare. I will shoot you a PM.
|
# ? Jun 21, 2012 21:28 |