|
Old James posted:This looks to be the problem Thats just me being dumb writing it out quickly on here the actual code just has the functions to the pull the data rather than "sYear" etc and I double checked that. ShimaTetsuo I was thinking I was being just plain dumb for a day. Figures its excel being retarded. I guess I'll just have to do the old work out days since 1st jan 1900 routine and let excel parse it however it wants. Thanks for the help guys.
|
# ? Sep 16, 2013 15:52 |
|
|
# ? May 26, 2024 19:13 |
|
I've got a table with a bunch of columns of numbers (ascending order, if that matters), and a separate number above each table. I am picking a number from each column to multiply that number above the column, and putting the result below the table. I am then adding up all those products. Something like this:code:
|
# ? Sep 16, 2013 15:57 |
|
Ronald McReagan posted:What I'd like to do is quickly change the A5, B6, C6, and D7 without having to manually change every formula. Something like bolding or italicizing the cells I actually want A2, B2, C2, and D2 to be multiplied by. Choosing the first cell within a column greater than X would also be helpful. Is this possible without writing a crazy macro? If you want it to multiply by the largest value in the column use =A2*large(A4:A8,1) For second largest =A2*large(A4:A8,2) For the smallest value =A2*small(A4:A8,1)
|
# ? Sep 16, 2013 16:55 |
|
Ronald McReagan posted:I've got a table with a bunch of columns of numbers (ascending order, if that matters), and a separate number above each table. I am picking a number from each column to multiply that number above the column, and putting the result below the table. I am then adding up all those products. Something like this: I don't think it's possible for Excel to detect whether a cell is bolded or italicized or whatever without using VBA, no. If you want to designate an arbitrary cell to multiply each value by, you could do something like: code:
=A2 * INDIRECT(A10) Then you could change the references in row 10 to whatever you like. If you specifically want to select the first cell in a column greater than X, you can use the MATCH function. You'd need to put your values in descending order, though, so: code:
=OFFSET(A3,MATCH(A10,A4:A8,-1),0) Match would return the position of the cell that's greater than or equal to the value in A10 (in this case 3), and the offset function looks for a cell that's that many rows down from A3.
|
# ? Sep 16, 2013 17:28 |
|
I think using INDIRECT should speed things up pretty nicely here without being too complicated. Thanks to both of you for the variety of ideas!
|
# ? Sep 17, 2013 22:47 |
|
Ronald McReagan posted:I think using INDIRECT should speed things up pretty nicely here without being too complicated. Thanks to both of you for the variety of ideas! INDIRECT() and OFFSET() are volatile functions, meaning they need to be recalculated anytime something in the workbook changes even if it is unrelated to that function. So extensive use of them will slow down your spreadsheet. http://www.decisionmodels.com/calcsecretsi.htm With the increased horsepower of PCs it may not be much of an issue, but be aware in case you start experiencing a noticeable slowdown.
|
# ? Sep 18, 2013 00:15 |
|
celestial teapot posted:
I dig them. It's also neat because I've implemented UDFs pretty similar to some of the ones you're posting so it's interesting to compare notes.
|
# ? Sep 18, 2013 04:40 |
|
I'd like to see what have too. Maybe I'll just drop all my stuff into a github repo. Probably need to anyhow, based on past experience.
|
# ? Sep 18, 2013 20:35 |
|
If i have a giant spreadsheet with 2 columns, employee Name, and Pet's name for instance. Is there a way to script inserting each employees Pet's name into a thus far blank <pets name> filed in Active directory without importing them all as new users/wiping all the other data? Assuming the names match up of course?
|
# ? Sep 23, 2013 20:06 |
|
AcidRonin posted:If i have a giant spreadsheet with 2 columns, employee Name, and Pet's name for instance. Is there a way to script inserting each employees Pet's name into a thus far blank <pets name> filed in Active directory without importing them all as new users/wiping all the other data? Assuming the names match up of course? I am not sure I understand what you need. You want a macro to insert the pet_name into... <pets name> You mean another spreadsheet? celestial teapot fucked around with this message at 18:11 on Sep 24, 2013 |
# ? Sep 24, 2013 18:08 |
|
There's probably an easier way to do this but I just knocked this out so that Excel would stop helpfully loving up my date values before exporting them to my real RDBMS:code:
celestial teapot fucked around with this message at 18:13 on Sep 24, 2013 |
# ? Sep 24, 2013 18:10 |
|
celestial teapot posted:There's probably an easier way to do this but I just knocked this out so that Excel would stop helpfully loving up my date values before exporting them to my real RDBMS: code:
|
# ? Sep 24, 2013 18:49 |
|
Old James posted:It threw an error because you are trying to change the value of another cell, you can't do that in a Function but can in a Sub. Are you sure? It works fine for me when called from another procedure. Make sure you specify the worksheet too, eg: Call date2text(ActiveSheet.Range("D5:D17")) I guess there's no reason for it to be a function though since it doesn't return anything.
|
# ? Sep 24, 2013 19:10 |
|
Sooooo, I'm trying to figure out how to figure out, given a series of tickets with start and end times, how many were active on a given hour (in a way that I could graph and report programatically). For instance, I can pull ticket data like such: code:
code:
edit: actually, as written I know how to do that, I think I'm just trying to figure out a way to do it that doesn't make a huge table of static dates. Falcon2001 fucked around with this message at 21:10 on Sep 24, 2013 |
# ? Sep 24, 2013 21:04 |
|
celestial teapot posted:Are you sure? It works fine for me when called from another procedure. Make sure you specify the worksheet too, eg: Call date2text(ActiveSheet.Range("D5:D17")) It complained at "c.Value = Format(c.Value, newformat)", the changes above got it working for me. Maybe it is something to do with different versions of Excel since it works for you.
|
# ? Sep 24, 2013 21:31 |
|
Falcon2001 posted:Sooooo, I'm trying to figure out how to figure out, given a series of tickets with start and end times, how many were active on a given hour (in a way that I could graph and report programatically). First off, in your example there should only be 1 active ticket at 9/1/13 0:00 right? Because the second ticket starts at 0:01. For readability, I'm assuming you name your ranges from the first table StartTimeRange and EndTimeRange. Then create a list of all the hours you want to report against, let's say in Column F, first value in row 2. Then a formula like =COUNTIFS(StartTimeRange, "<=F2", EndTimeRange, ">=F2") Should give you the number of tickets where F2 falls in between those times. vvv You'll want to normalize the ticket start and end times to just the closest hour then. ZerodotJander fucked around with this message at 22:19 on Sep 24, 2013 |
# ? Sep 24, 2013 21:54 |
|
ZerodotJander posted:First off, in your example there should only be 1 active ticket at 9/1/13 0:00 right? Because the second ticket starts at 0:01. Err, meant grouped by hour, rather than by minute. Thanks! I'll check it out.
|
# ? Sep 24, 2013 22:04 |
|
Is there a way to have charts with relative referencing?
|
# ? Sep 24, 2013 22:44 |
|
Falcon2001 posted:Err, meant grouped by hour, rather than by minute. Thanks! I'll check it out. I think that Excel is choking on the date portion of this. If I use the following: =COUNTIF(table[columnname], ">A2") (where A2 is 1/1/2013), I get zero results. But if I do: =COUNTIF(table[columnname], ">1/1/2013") I get the results I want. Any idea wtf? EDIT: =COUNTIF(Table[Column], ">"&A2) works, although if I want to do a datetime by combining a given day and time, I have no idea how to do that. DOUBLE EDIT: HA HA I figured it out, I was typo'ing that. Thanks! Falcon2001 fucked around with this message at 22:50 on Sep 24, 2013 |
# ? Sep 24, 2013 22:44 |
|
celestial teapot posted:I am not sure I understand what you need. You want a macro to insert the pet_name into... <pets name> You mean another spreadsheet? No into Active directory. Basically my IT department finds it hard to belive that you would have to hand-jam 2k some entires into AD for a new data field on the users, so they asked me if i could "I dont know, code something?" but i know next to nothing about AD/Excel.
|
# ? Sep 25, 2013 12:30 |
|
AcidRonin posted:No into Active directory. Basically my IT department finds it hard to belive that you would have to hand-jam 2k some entires into AD for a new data field on the users, so they asked me if i could "I dont know, code something?" but i know next to nothing about AD/Excel. We could probably help with formatting text in Excel, but it's the process of moving it from there to ActiveDirectory that is probably outside the realms of this thread. But, a google search found this post about adding ActiveDirectory entries based off of data in a CSV file. So save your Excel as CSV and then try adapting this code. http://gallery.technet.microsoft.com/office/AD-and-mailbox-from-CSV-96a4713f
|
# ? Sep 25, 2013 15:24 |
|
Old James posted:We could probably help with formatting text in Excel, but it's the process of moving it from there to ActiveDirectory that is probably outside the realms of this thread. Yeah, this. I don't know jack poo poo about Active Directory. But if you have ugly user-entered data you need to get into a particular format, someone in here will be able to walk you through that part for sure. Post up a few sample rows of what it looks like vs what you want it to look like and we'll help you get it done in Excel.
|
# ? Sep 25, 2013 17:21 |
|
celestial teapot posted:Yeah, this. I don't know jack poo poo about Active Directory. But if you have ugly user-entered data you need to get into a particular format, someone in here will be able to walk you through that part for sure. Post up a few sample rows of what it looks like vs what you want it to look like and we'll help you get it done in Excel. Nah the spreadsheet is fine, but i appreciate the direction, I should really take the time to learn excel at somepoint.
|
# ? Sep 25, 2013 19:38 |
|
There are Powershell functions for both Excel and AD, so you could probably write the thing in Powershell. I just don't know what those functions are.
|
# ? Sep 25, 2013 20:59 |
|
My colleague has somehow managed to get his copy of Excel 03 to insert a ^ at the start of every cell in any spreadsheet he opens. What has he done and can it be switched off?
|
# ? Sep 26, 2013 10:32 |
|
Hey everyone! When using Excel 2010, I'm looking to do a specific data entry that won't make me want to kill myself after: (I'm new to the program so please don't hate me if this is common knowledge ) - I have a column of zip codes in one data sheet as part of a 300-respondent survey - I want to add columns next to this column of zip codes that references the USDA's 2013 Rural-Urban continuum codes from the spreadsheet listed on this page Basically, I want the USDA's data, based on whatever zip code respondents entered, to be put alongside their response. In other words, if somebody says they're "01001" in my data sheet, next to their answer is AL, Autauga County, 54,571, 2, and Metro (all in their own respective columns) Is this possible to do? Thanks in advance!
|
# ? Sep 30, 2013 23:13 |
|
On my phone but: use vlookup or the more flexible index and match to match the value from one column to data from another.
|
# ? Sep 30, 2013 23:23 |
|
I'm kind of an Excel idiot. One spreadsheet has FIPS, county, and state. Another spreadsheet has ZIP, city, county, and state. I wish to create a column for ZIP in the FIPS spreadsheet that lists all the ZIP codes located within each county. Also, the issue here is that there are similarly named counties in various states, so I need it to match state to county as in its search as well. I hope this makes sense
|
# ? Oct 1, 2013 02:03 |
|
Xovaan posted:I'm kind of an Excel idiot. To make things simple we'll stick with the VLookup() function. This function has four parameters: The value you want to match, the array (rectangle of cells) you are comparing it against, the column from the array you want to return, and finally if you want it to return values when it finds an exact match to your search or a close match. VLookup() always looks for a match in the first column of the array you ask it to search. So on both worksheets insert a new column before column A. This blank column will now be column A and everything else shifted over to the right. Assuming City and State are now in columns C & D, we will add the following formula in column A to concatenate (join) them into a single cell. code:
code:
http://www.techonthenet.com/excel/formulas/vlookup.php
|
# ? Oct 1, 2013 02:59 |
|
Worked like a charm! Thanks! I assume I just copy the worksheets into my respondent worksheet then copy over the formulas to report on pop/density once respondents give me their zip codes? Man I'll be an Excel master at this rate.
|
# ? Oct 1, 2013 18:34 |
|
For some reason my work computer is not showing any forum buttons or images so I cannot find a way to edit. Apologies on the double post here. Next thing I need to do is split some names that are conjoined. They look like RootWalla or MaynardKeynes and I need them to be Root Walla/Maynard Keynes. I found a post here that explains exactly what I need to do. Problem? I've never, ever used VBA or macros before. They give a code to use, but what's the process to get it to run in the workbook for, say, column A? Thanks in advance! This is all very helpful.
|
# ? Oct 1, 2013 22:35 |
|
Xovaan posted:For some reason my work computer is not showing any forum buttons or images so I cannot find a way to edit. Apologies on the double post here. It would be better to separate firstname and surname into separate columns. It's always easier to join data back together than to break it apart, as you're discovering here. Your URL is broken though: it just links back to this thread. Something like this might work for you, assuming there are only two uppercase characters in every cell: code:
celestial teapot fucked around with this message at 01:36 on Oct 2, 2013 |
# ? Oct 2, 2013 01:33 |
|
celestial teapot posted:It would be better to separate firstname and surname into separate columns. It's always easier to join data back together than to break it apart, as you're discovering here. Your URL is broken though: it just links back to this thread. Something like this might work for you, assuming there are only two uppercase characters in every cell: Nice, thanks for this. I ended up figuring out how to do it via a stack overflow macro I found but I'll try this one out as well. Yeah, sorry about the link; my work computer doesn't load any graphics from websites for some reason so I couldn't actually get to the edit button to link the macro I was trying to in the first place.
|
# ? Oct 2, 2013 04:12 |
Total Meatlove posted:My colleague has somehow managed to get his copy of Excel 03 to insert a ^ at the start of every cell in any spreadsheet he opens. What has he done and can it be switched off? He's not saving them as Lotus 1-2-3 files or something like that is he?
|
|
# ? Oct 4, 2013 20:28 |
|
I am working with a spreadsheet that has irregular data in a column like this.code:
so get: code:
is there anyway to do this? borodino fucked around with this message at 09:22 on Oct 7, 2013 |
# ? Oct 7, 2013 09:07 |
|
Put the formula below in column C, starting in row 2. =IF(RIGHT(B2,2)="ct",TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",99)),99)),"") This will get you what you wrote in the Excel file picture. It will not get the count for the entries (Laser Pop, Lemonhead) with blanks in column B. The formula also assumes that column B will always have never have non-space characters before the count and that entries you want to keep will end in 'ct' or 'CT'.
|
# ? Oct 7, 2013 13:37 |
|
The regexreplace functions posted in the last couple pages are also worth looking at.
|
# ? Oct 7, 2013 19:11 |
|
Perfect thanks.
|
# ? Oct 7, 2013 23:42 |
|
A few months ago, I wrote a bunch of javascript bookmarklets to read the DOM of an IE form page and manipulate elements. Currently, I use a bookmarklet to parse the DOM, export it as text to Excel, assign my data to the element IDs, then create another bookmarklet that applies that data (for repeatedly entering identical data). I want to cut out the middleman and have Excel interact directly with the IE page. I'm trying to translate my javascript code to VBA, but it's not as straightforward as I had hoped, and my fluency in VBA is currently not up to par. Anyway, as a starting example, how would I properly translate this javascript code to mark the first option in a series of radio buttons? code:
code:
|
# ? Oct 8, 2013 01:53 |
|
|
# ? May 26, 2024 19:13 |
|
I'm completely new to VBA, and I've been tasked with updating a set of macros we use at my company. In a nutshell, we get an output report as a csv file, then start a macro that processes it into something we send out. The macros are within a separate file (we'll call this "formatter"). The goal is to make the output multilingual, based on a userform that comes up. My thought is to have an array in the "formatter" sheet, with languages in cells e2:j2, then from e3:e37 etc will be translated strings that will be put in the output report. I would like to have a listbox/combobox in the userform that contains the languages (keep in mind, the csv file is the ActiveWorkbook), so that selection of the language will correspond to the column that contains the translated strings. My issue is I can't get the RowSource property of the listbox/combobox to correctly reference the "formatter" workbook and sheet, and I'm not sure how to then define an array (e3 through j37) containing the strings so that the text output can be referenced in the rest of the code. Sorry if that's not clear, I've powered about halfway through an excel 2007 macro programming book in a day, and I'm still getting used to it.
|
# ? Oct 8, 2013 17:35 |