|
Bind the combobox to set the value of the cell underneath it, and then your VBA reads the value of that cell, not the combobox.
|
#
?
Oct 8, 2013 20:19
|
|
|
|
| # ? May 23, 2024 15:29 |
|
|
I have a long list that is something like this: Items 1-10 can have any of properties a-e. 1,a,b 2,a 3,a,b,c 4,b,c 5,a,c 6,a,b 7,a,c,d 8,e 9,a,b 10,d,e Is there a way to switch it to get something like this: Properties a-e and which items 1-10 have them. a,1,2,3,4,5,6,9,10 b,1,3,4,6,9 c,3,4,5,7 d,7,10 e,8,10 I don't need a way to do it automatically, just something that will be better than doing it all by hand.
|
|
#
?
Oct 9, 2013 03:03
|
|
|
Filter your list by "Contains" the letter in question, then copy out the list of cells. It's not worth automating if you only have a handful of properties.
|
|
#
?
Oct 9, 2013 17:19
|
|
|
Good call. The list has 32 properties and 155 items, but that method will do it quickly enough and more importantly be accurate.
|
|
#
?
Oct 9, 2013 17:39
|
|
|
Kind of a weird question. I found a US map sectioned by counties online in Excel from here. (the gray one) I then have a list of population of each county for 2013 that the USDA put out along with corresponding metro codes (which I cannot link due to the site being down from a lapse in budgeting) I'm looking to edit the dropdown menu shown in the US map to include USDA population numbers and metro codes and further color the map accordingly. Is this a pretty big task? I assume I have to link the autoshapes to the population numbers in another sheet, but I'm really at a loss here and it's currently over my head. Any ideas of where to start?
|
|
#
?
Oct 10, 2013 20:08
|
|
|
I need a specific number sequence: 1001 1002 1003 2001 2002 2003 3001 3002 3003 ... Is this possible? What would the formula look like in excel?
|
|
#
?
Oct 11, 2013 17:28
|
|
|
I have a series of addresses I'm attempting to import from a .PDF to Excel. The issue is that there's no delimiting so there's no way to really organize anything. So I guess my question is: what's the cleanest way to import an a list of .PDF addresses that vary slightly, like so: code:But there's addresses both American and foreign and the TEX/ PER can be any combination of letters from a list of 3-digit all-caps acronyms, with some being separated by commas and other times by new lines. It's literally driving me insane because I'm fairly certain this is impossible.  edit: I managed to import it to word as such: Last, First Name DVM Name of Establishment PO Box XYZ City, State ZIP ( (555) 555-5555 Fax: (555) 555-5555 name@email.com ROS RAC, REP, PLE So I guess I need a way for Excel to understand that the first line (bolded text in Word) is Column 1, Name of Establishment is Column 2, address is subsequently adjacent columns, followed by columns for number, fax number if available, email, and acronyms. Issue: Sometimes the name of the establishment is put onto the next line where either PO Box or numerical address should begin so that's an issue with automation too. Man this is a huge task isn't it.  Knot My President! fucked around with this message at 20:03 on Oct 11, 2013 |
|
#
?
Oct 11, 2013 19:15
|
|
|
Oh My Science posted:I need a specific number sequence: Assuming you've got 1001 in cell A1, use this formula in cell A2 and copy downward: =IF(MOD(A1-3,1000)=0,A1+998,A1+1)
|
|
#
?
Oct 11, 2013 19:20
|
|
|
Xovaan posted:Issue: Sometimes the name of the establishment is put onto the next line where either PO Box or numerical address should begin so that's an issue with automation too. Man this is a huge task isn't it. You've landed in the bottom level of Hell in the world of databank engineering. Your best bet, if there is way too much data to clean it up manually, is to export each address into a document type like xml or json and learn regular expressions to strip substrings out into separate fields. This is why ETL engineers get the big bucks...
|
|
#
?
Oct 11, 2013 21:17
|
|
|
docbeard posted:Assuming you've got 1001 in cell A1, use this formula in cell A2 and copy downward: Thanks this is awesome. I also have one more odd sequence if anyone can help me out. 0001 0001 0001 0002 0002 0002 0003 0003 0003 ... Thank in advance. Wrote some ruby and copied it into excel. Oh My Science fucked around with this message at 23:52 on Oct 11, 2013 |
|
#
?
Oct 11, 2013 23:09
|
|
|
Oh My Science posted:Thanks this is awesome. I also have one more odd sequence if anyone can help me out. FYI, it could be done as =TEXT(ROUNDUP(ROW()/3,0)"0000")
|
|
#
?
Oct 12, 2013 02:55
|
|
|
celestial teapot posted:You've landed in the bottom level of Hell in the world of databank engineering. Your best bet, if there is way too much data to clean it up manually, is to export each address into a document type like xml or json and learn regular expressions to strip substrings out into separate fields. This is why ETL engineers get the big bucks... Welp, that's what I figured. Looks like we gotta send some other poor schmuck to the dredges of Hell because it sure ain't gonna be me.  Thanks for the input!
|
|
#
?
Oct 12, 2013 04:05
|
|
|
I have a protected Excel document out on SharePoint and I want employees to be able to edit any cell in just one column, and once they've edited the cell I'd like the document to lock the cell and then protect the sheet again on save. So basically- -Sheet is protected. -One column with highlighted, but otherwise empty, unprotected cells that users can edit. -Once the document gets saved (or a cell is edited?) a script will check to see if any cells, in that column, with data in them are unlocked and/or formatted with highlighting. If so, the script will un-highlight the cell and lock it so that it cannot be edited without protecting the sheet. Not sure that's a "small Excel question" but if someone could point me in the right direction, or tell me if this is even possible, that would be great.
|
|
#
?
Oct 14, 2013 17:41
|
|
|
TheEffect posted:I have a protected Excel document out on SharePoint and I want employees to be able to edit any cell in just one column, and once they've edited the cell I'd like the document to lock the cell and then protect the sheet again on save. So basically- It's a medium question. I don't know of any way to do this without VBA. I would write a procedure and bind it to run with the OnClick event on the worksheet. The procedure would check if the clicked cell is in the editable column and if is blank. If true, then it would ask the user what value they want to enter in the cell, unprotect the sheet, set that value in the cell, reprotect the sheet, and terminate. If false, then nothing. I don't know crap about SharePoint so I don't know if this is any different, but you should know that protection is only good for guiding friendly users to avoid data entry errors and the like. For a hostile user, workbook protection in Excel is trivially easy to break with a simple google search.
|
|
#
?
Oct 14, 2013 21:30
|
|
|
TheEffect posted:I have a protected Excel document out on SharePoint and I want employees to be able to edit any cell in just one column, and once they've edited the cell I'd like the document to lock the cell and then protect the sheet again on save. So basically- So do you want it to save and lock on editing a cell, or save and lock just when they're done with the file?
|
|
#
?
Oct 14, 2013 21:32
|
|
|
celestial teapot posted:It's a medium question. I don't know of any way to do this without VBA. I would write a procedure and bind it to run with the OnClick event on the worksheet. The procedure would check if the clicked cell is in the editable column and if is blank. If true, then it would ask the user what value they want to enter in the cell, unprotect the sheet, set that value in the cell, reprotect the sheet, and terminate. If false, then nothing. I planned on using VBA in one way or another, just wasn't sure where to start. That sounds pretty good though, thank you. Regarding the protection- that's a valid point. The purpose of this would be so employees can sign up for over-time shifts and other employees wouldn't be able to overwrite those cells with their own name. Most everyone is pretty professional here, and if it comes down to it and someone says their slot was taken from them we can just check the version history over SharePoint. Around ~150 employees would be using this and this script would help eliminate the chance of someone "accidentally" (or otherwise) taking someone's slot; but if they do we'll be able to find out. Harry posted:So do you want it to save and lock on editing a cell, or save and lock just when they're done with the file? Save and lock when they are done is the best way to go about this I think. Or maybe just lock and protect, and leave the saving part up to the user.
|
|
#
?
Oct 14, 2013 22:26
|
|
|
I am working with pivot tables and trying to get count data based on column answers. Some people, per column's cell, have "apple", some "orange", some "apple, orange", "apple, orange, bananas, pear", etc. What I want in my pivot table: Count of Apple: 3 Count of Orange: 3 Count of Bananas: 1 Count of Pear: 1 Is this even possible without creating multiple columns for each? There are upwards of 40 possible answers so I would really not like to have to do this. 
|
|
#
?
Oct 15, 2013 22:50
|
|
|
Xovaan posted:I am working with pivot tables and trying to get count data based on column answers. Don't think it will work in a pivot table but in Excel 2007 and on you can use wildcards in countif. So =COUNTIF($A:A,"*apple*") would get you the total people who chose apple.
|
|
#
?
Oct 16, 2013 04:12
|
|
|
Really simple question- I have a row of numbers with the AVERAGE function at the end of it. I want the function to omit the numbers from the averaging calculation if the number in the cell is under a specific amount. Not sure how to set the condition for this. Suggestions?
melon cat fucked around with this message at 13:49 on Oct 16, 2013 |
|
#
?
Oct 16, 2013 13:21
|
|
|
Use the averageifs function. If the average range is A1 to A30, and you want to omit numbers under 10, the syntax is: =averageifs($A$1:$A$30,$A$1:$A$30,">=10")
|
|
#
?
Oct 16, 2013 13:25
|
|
|
Ragingsheep posted:Use the averageifs function. I just tried your suggested function, but now I'm getting a "#NAME?" error. I feel like I'm doing something wrong, but can't quite pin down what I did. Because your suggestion's syntax is definitely correct.  I feel like the error's hiding right beneath my nose, but I'm just not seeing it. Ugh. melon cat fucked around with this message at 14:07 on Oct 16, 2013 |
|
#
?
Oct 16, 2013 13:59
|
|
|
melon cat posted:Thanks for that really quick response! The following functions were added in Excel2007, if you are using an earlier version they will not work. COUNTIFS() SUMIFS() AVERAGEIFS() EDIT: From your screenshot it looks like you used the formula incorrectly. =AVERAGEIFS([Range to be Averaged],[Range to compare against criteria],[criteria],...) So you need to change that formula to =AVERAGEIFS($B$2:$B$32,$B$2:$B$32,"<2") Old James fucked around with this message at 14:23 on Oct 16, 2013 |
|
#
?
Oct 16, 2013 14:21
|
|
|
Old James posted:The following functions were added in Excel2007, if you are using an earlier version they will not work.
|
|
#
?
Oct 16, 2013 14:32
|
|
|
melon cat posted:I'm actually using Excel 2003, so I'm glad that you brought that up. Any acceptable substitute functions that would be helpful? Some online resources make reference to an array formula, but I can't figure out how to get it working with my spreadsheet (nor do I understand the logic behind the sample array formulas that are posted elsewhere). I think =averageif() *notice the lack of an "S"* worked in 2003. It only allows 1 criteria and changes the order of inputs. =AVERAGEIF([Range to compare against criteria],[criteria],[Range to be Averaged]) So in your case it would be =AVERAGEIFS($B$2:$B$32,"<2",$B$2:$B$32) http://www.techonthenet.com/excel/formulas/averageif.php
|
|
#
?
Oct 16, 2013 15:21
|
|
|
Brady posted:I planned on using VBA in one way or another, just wasn't sure where to start. That sounds pretty good though, thank you. Sure. Excel can solve this sort of thing half-way if you don't have time to do a full-on web app with an sql(lite) backend, and if only employees will use it, then I wouldn't be paranoid about security or poo poo like transactional integrity either. Post what you have if you get stuck. Ozgrid forums solve problems like this for people all day too, so that would be a good place to ask for help.
|
|
#
?
Oct 18, 2013 17:53
|
|
|
Is there a way to have multiple partial searches for a vlookup? I'm working with; code:code:=IF(ISNUMBER(SEARCH(OR("3490",A3),("3584",A3),("3494",A3))),B3=VLOOKUP(LEFT(A3,4),$A$10:$B$14,2,FALSE), B3="") Where it searches A3 for 3490 or 3584 or 3494, and if it finds them then VLOOKUPs the first four characters to that table array, and if it finds them writes the code it finds to B3, or otherwise leaves it blank. I'm using the VLOOKUP table so that I don't have to nest multiple IF statements, because there's a good chance that I may have to search for 20-30 different models, and this way I can return a unique code from one table. Can anybody help, or do I need to split that into two formula, one in A6 to check the ISNUMBER(SEARCH(OR........)) and then have the B3 cell with IF(A6=TRUE,VLOOKUP(LEFT(A3,4)......))
|
|
#
?
Oct 22, 2013 14:33
|
|
|
I'm confused by what you are trying to do here. Are you trying to look up the code from table 2 into table 1? For that I would do =IFERROR(VLOOKUP(LEFT(A3,4),$A$10:$B$14,2,FALSE),"")
|
|
#
?
Oct 22, 2013 14:41
|
|
|
I can't use IFERROR because I'm still sending this to people who can't open .xlsx files  And I need to be able to search the string in A3 for those specific codes before I can VLOOKUP using them, there are thousands more that don't have them that should be ignored.
|
|
#
?
Oct 22, 2013 15:00
|
|
|
I have an excel formula question. I'm trying to find the array formula that would sum a column if the number in each cell is greater than the adjacent cell. For instance: A B 1 2 2 4 5 3 6 2 So the formula would only total cell B if the number in each cell is greater than cell A.
|
|
#
?
Oct 22, 2013 17:27
|
|
|
Total Meatlove posted:I can't use IFERROR because I'm still sending this to people who can't open .xlsx files Try =IF(ISERROR(VLOOKUP(LEFT(A3,4),$A$10:$B$14,2,FALSE)),"",VLOOKUP(LEFT(A3,4),$A$10:$B$14,2,FALSE)) for versions of Excel before 2007. What this does it pull the first 4 characters in cell A3 (that's the LEFT() function) and then does a VLOOKUP for a match on that string.
|
|
#
?
Oct 22, 2013 18:51
|
|
|
MoraleHazard posted:I have an excel formula question. I'm trying to find the array formula that would sum a column if the number in each cell is greater than the adjacent cell. For instance: {=sum(if(A:A<B:B,B:B,0))}
|
|
#
?
Oct 22, 2013 18:53
|
|
|
Old James posted:{=sum(if(A:A<B:B,B:B,0))} Thank you very much; a big help.
|
|
#
?
Oct 22, 2013 19:43
|
|
|
Old James posted:Try =IF(ISERROR(VLOOKUP(LEFT(A3,4),$A$10:$B$14,2,FALSE)),"",VLOOKUP(LEFT(A3,4),$A$10:$B$14,2,FALSE)) for versions of Excel before 2007. For some reason, if I used VLOOKUP(LEFT,4) on something that had a - after the four characters (i.e. 4949-405 being the lookup value on a table with 4949) it wouldn't work. But changing that to VLOOKUP(LEFT,5) and having the table be 4949- works a treat.
|
|
#
?
Oct 23, 2013 17:02
|
|
|
Is there a reason IF functions do not accept wild cards?
|
|
#
?
Oct 23, 2013 21:34
|
|
|
borodino posted:Is there a reason IF functions do not accept wild cards? Are you asking about =IF() or =COUNTIF(),=SUMIF(),=AVERAGEIF()? The last three accept wildcards after Excel2007 (or maybe it is the IFS version of the formula which was introduced at that time).
|
|
#
?
Oct 23, 2013 21:59
|
|
|
Old James posted:Are you asking about =IF() or =COUNTIF(),=SUMIF(),=AVERAGEIF()? The last three accept wildcards after Excel2007 (or maybe it is the IFS version of the formula which was introduced at that time). Ok just the IF function I mean. What would be the harm in allowing it? I found a way around it, I'm just just curious if there's a reason it would be a bad thing to allow.
|
|
#
?
Oct 23, 2013 22:29
|
|
|
borodino posted:Ok just the IF function I mean. What would be the harm in allowing it? I found a way around it, I'm just just curious if there's a reason it would be a bad thing to allow. Maybe they'll make a wildcard version and call it =IFFY() 
|
|
#
?
Oct 23, 2013 22:55
|
|
|
My company wants me to find information using a Vlookup by using payments made as the value to find. Problem is payments have duplicates. Is there a way to gather multiple entries from duplicate values without using VBA, or should I just tell people tough titties and have them look up the info they want when those things duplicates. Mind you, this happens like once twice per run (~100) entries so it's not a big deal if I can't do it without using vba. Also speaking of my company I am back in a real job again, so I'll be frequenting here again... a lot.
|
|
#
?
Oct 24, 2013 00:40
|
|
|
Can you give an example?
|
|
#
?
Oct 24, 2013 02:16
|
|
|
|
| # ? May 23, 2024 15:29 |
|
|
Veskit posted:My company wants me to find information using a Vlookup by using payments made as the value to find. Problem is payments have duplicates. Is there a way to gather multiple entries from duplicate values without using VBA, or should I just tell people tough titties and have them look up the info they want when those things duplicates. Mind you, this happens like once twice per run (~100) entries so it's not a big deal if I can't do it without using vba. What about making a pivot table where the first column is the payment and column 2 is whatever you are matching it to, then a count of rows for your measure?
|
|
#
?
Oct 24, 2013 02:27
|
|