|
I've been messing around with PowerView on Excel 2013 and I was wondering if there was a way that I can have all my PowerView sheets in presentable format. All the tutorials online talk about SQL Server and Sharepoint, I dont have access to either. celestial teapot posted:It would be easier to say for sure if I could see the data source instead of the pivot table, but a pivot table should be able to do this for you: you would add a total row for PO Number and change it from a sum to a count. Edit: Okay, it sounds like you said you tried that, which is weird. Pivot tables are designed to handle data normalized in the way that you described. Can you post the workbook? Thank you for your help!
|
#
?
May 20, 2013 18:12
|
|
|
|
| # ? May 26, 2024 02:00 |
|
|
Busy Bee posted:I've been messing around with PowerView on Excel 2013 and I was wondering if there was a way that I can have all my PowerView sheets in presentable format. All the tutorials online talk about SQL Server and Sharepoint, I dont have access to either. Maybe this: http://office.microsoft.com/en-us/excel-help/create-a-power-view-sheet-connected-to-an-external-data-model-in-excel-HA103230578.aspx
|
|
#
?
May 20, 2013 19:32
|
|
|
There's something I want to do in Excel, but I'm not sure how to go about doing it. I want to create an Excel spread that allows the user to paste in a bunch of information (let's say a list of names) onto one WorkSheet. Then, there's a second worksheet with another list of names. If there are any matches among the names, partial or full, between the two WorkSheets the user gets some sort of notification. What functions would I use for creating such an application? I hope my question made sense...
|
|
#
?
May 21, 2013 00:49
|
|
|
There are two types of people who work at my company - contracted employees (c- & t-) and full time employees. I currently have a list like so with the data on the left, and what I want to accomplish on the right:  I have the "Team" and "Employee Name" in Pivot Tables. How would I go about this using Excel 2013? I'm thinking I could do something with a formula and have a column on my list of employees with "Employee Type" (Contract & Full Time) so then I can just easily play around with the "Count of Employee Type" in Pivot Tables. celestial teapot posted:Maybe this: http://office.microsoft.com/en-us/excel-help/create-a-power-view-sheet-connected-to-an-external-data-model-in-excel-HA103230578.aspx Thanks for that. Apparently my company has an offsite Sharepoint site that I had no idea about. Busy Bee fucked around with this message at 02:33 on May 21, 2013 |
|
#
?
May 21, 2013 02:30
|
|
|
Busy Bee posted:Stuff =COUNTIF() supports wildcards since Excel 2007. In this case since you are checking against more than 1 criteria use =COUNTIFS() code:
|
|
#
?
May 21, 2013 04:22
|
|
|
I've got a large excel sheet with about 50 tabs, each with a similar filtered list. Is there any way to globally set these filters? Playing around with record I've come up with the below to do the active sheet. Is there an easy way to make it do all sheets, iterate through sheets without having to name each, or all filtered lists in the document? ActiveSheet.Range("$A$1:$M$500").AutoFilter Field:=4, Criteria1:="Type A"
|
|
#
?
May 21, 2013 04:52
|
|
|
melon cat posted:There's something I want to do in Excel, but I'm not sure how to go about doing it. =MATCH() would do it, as would any of the lookup functions Lookup() Vlookup() etc.
|
|
#
?
May 21, 2013 14:28
|
|
|
Xenoborg posted:I've got a large excel sheet with about 50 tabs, each with a similar filtered list. Is there any way to globally set these filters? Whatever it is you are doing, you need this to be done with a database, not excel. You are in for some seriously crufty VBA programming otherwise.
|
|
#
?
May 21, 2013 14:29
|
|
|
If Column A in Excel has numbers and text in it, how would you write a rule to move only the text across and up one cell?code:
|
|
#
?
May 21, 2013 15:09
|
|
|
Xenoborg posted:I've got a large excel sheet with about 50 tabs, each with a similar filtered list. Is there any way to globally set these filters? Probably something like this (I don't use auto filter so I'm just basing this off your code) code:
|
|
#
?
May 21, 2013 16:25
|
|
|
Total Meatlove posted:If Column A in Excel has numbers and text in it, how would you write a rule to move only the text across and up one cell? In B2, =IF(A3>100000,A3,"") Any word is larger than any number.
|
|
#
?
May 21, 2013 18:00
|
|
|
ZerodotJander posted:In B2, =IF(A3>100000,A3,"") =if(isnumber(A3),"",A3) =if(istext(A3),A3,"")
|
|
#
?
May 21, 2013 18:30
|
|
|
Harry posted:Probably something like this (I don't use auto filter so I'm just basing this off your code) Thank you, this worked just as I had imagined.
|
|
#
?
May 21, 2013 18:50
|
|
|
I've been racking my brain at this for a while: I have two files. I need the red in column 1(pretend the numbers are the same, it's in the list somewhere), the green in column 2, and the blue in column 3. The hard part is that bottom file. I don't know how to get that green number in the same row as its associated red number. I tried delimiting it and had it all in a column, and tried to grab every 7th row but the spacing isn't that nice so that didn't work. If I can at least get that done so the red and green are nicely lined up that'd be great. The next step would be associating the correct blue number. I have no idea. Any help would be awesome. I've googled a lot but I've never really used visual basic or lookup tables until trying to attempt this. I noticed the the red terms have the 000 marker and the green have the 200. Otherwise it's all mish-mashed and different as you go.
|
|
#
?
May 21, 2013 23:31
|
|
|
That is a job for regular expressions. Edit: Added you on AIM.
|
|
#
?
May 21, 2013 23:39
|
|
|
MythObstacleIV posted:I have two files. I need the red in column 1(pretend the numbers are the same, it's in the list somewhere), the green in column 2, and the blue in column 3. Someone else will probably post a more elegant solution, but what I would do is: 1. Open the 2nd file in an advanced Text Editor of your choice - Textpad, Emacs, Vim, whatever 2. Replace \n> (new line then >) with some sort of placeholder, e.g. @@ 3. Replace "<TD width=13%>" with Tab 4. Replace all remaining newlines with Tab 5. Replace your placeholder with newline 6. Paste into Excel This should turn each block starting with a > prompt into one line, with tabs between strings, and all of the values you need should be lined up into the same column.
|
|
#
?
May 21, 2013 23:46
|
|
|
Anyway you can post the files?
|
|
#
?
May 22, 2013 00:45
|
|
|
ZerodotJander posted:Someone else will probably post a more elegant solution, but what I would do is: If I attempt that I get this:  The formatting of the file is weird and not at all consistent. The TD width is sometimes a T D or there's no ">". celestial teapot is currently doing crazy stuff to hammer it out. EDIT: Task accomplished! Thank you! Thanks for the help! I would prefer not to directly post the entire file. I have a lot of new stuff I need to learn now. 
MythObstacleIV fucked around with this message at 02:03 on May 22, 2013 |
|
#
?
May 22, 2013 01:27
|
|
|
sed and regular expressions: the multitool in every nerd's toolbelt
|
|
#
?
May 22, 2013 02:02
|
|
|
Is there a generally agreed upon best site for learning VBA? I'm about to start a big project that I'm told will require "minimal" VBA. I would say I know "minimal" VBA already, but my knowledge has been scrapped together as it was required rather than formally learned.
|
|
#
?
May 23, 2013 18:24
|
|
|
Hoops posted:Is there a generally agreed upon best site for learning VBA? I'm about to start a big project that I'm told will require "minimal" VBA. I would say I know "minimal" VBA already, but my knowledge has been scrapped together as it was required rather than formally learned. That's how I learned as well. I would run into a problem and google "VBA Excel [Insert problem here]" and almost always found someone who had already asked and answered my question. Most common answers came from vbforums.com, ozgrid.com, and stackoverflow.com but Microsoft also put the VBA object model online for a FULL run through of each object and function http://msdn.microsoft.com/en-us/library/office/aa272268(v=office.11).aspx However, although that site is thorough it may not be as friendly to novice programmers.
|
|
#
?
May 24, 2013 00:06
|
|
|
Yup, Google will solve everything you can think of for awhile. But ozgrid has been a great resource for me if I need to find answers to more advanced questions that can't be answered with a Google search. The hardcore sorcerer-level VBA gurus mostly hang out there in my experience. Learn by doing! For the most part I learned by piecing things together until it worked well enough to deploy in the office. But Chip Pearson's website is a really good place to see how VBA looks when it's written 'right': http://www.cpearson.com/excel/mainpage.aspx
|
|
#
?
May 24, 2013 00:45
|
|
|
celestial teapot posted:=MATCH() would do it, as would any of the lookup functions Lookup() Vlookup() etc.
|
|
#
?
May 24, 2013 04:24
|
|
|
I'm writing a small vba function that takes input from two cells and returns one string. Its a bit more complicated than the example but the idea is: Function example(cell1, cell2) example = cell1 & cell2 End Function The problem is, if one of the inputs is a date (e.g "Mar-13"), then it will return 1/03/2013. Is there a way to force it to text of Mar-13 rather than a number?
|
|
#
?
May 27, 2013 02:00
|
|
|
In the sheet, it's =TEXT(). In VBA, it's =FORMAT().
|
|
#
?
May 27, 2013 03:47
|
|
|
This is technically a google sheets question, but I'm pretty sure all the formulas are the same as in Excel. I'm trying to make it so whenever cell A contains a certain word "egg", B cell to the right of it produces "omelette". I know how to do it if A cell contains only "egg" (=if(A1="egg", "omelette", "")) but I can't figure out how to do it if there are other words in addition to "egg" in cell A. Am I supposed to use wildcards or something?
|
|
#
?
May 27, 2013 05:23
|
|
|
You can use find() to return the starting position of egg in a string. If egg doesn't exist, it'll just return an error.
|
|
#
?
May 27, 2013 06:16
|
|
|
Well I need something that will just return a blank cell if the word doesn't exist.
|
|
#
?
May 29, 2013 20:39
|
|
|
Ron Don Volante posted:Well I need something that will just return a blank cell if the word doesn't exist. =iferror(search("egg",A1),"") If you are running an older version of Excel (I think pre 2007) use this instead =if(iserror(search("egg",A1)),"",search("egg",A1))
|
|
#
?
May 29, 2013 21:08
|
|
|
Yeah IFERROR was added in Office 2007.
|
|
#
?
May 31, 2013 20:48
|
|
|
My knowledge of excel is basically nil so hopefully I manage to ask this question while making some semblance of sense. What I'm trying to do is make a little spreadsheet for an RPG I'm playing. I'm doing this just as kind of a fun little exercise. What I have so far looks something like this:  The 'abilities' column has the name of a bunch of different magical properties. The cell to the right of each shows the stat bonuses for that magic property. I've also got a dropdown box on the lift (under Affix) with the same list of magical properties attached. Basically what I want is to be able to select a property from the Affix dropdown box, and its stat bonus to appear on the column to the right. In other words, I want to when I select L4 from the dropdown box, I want whatever's in M4 to appear right next to it. I've managed to do this very crudely so far: =IF(D2=L2;M2;IF(D2=L3;M3;IF(D2=L4;M4;IF(D2=L5;M5;IF(D2=L6;M6;IF(D2=L7;M7.... and so on and so on But I imagine there's a much simpler way of doing this rather than writing one endless that calls every single row on those columns. Any ideas?
|
|
#
?
Jun 4, 2013 06:14
|
|
|
blue font posted:My knowledge of excel is basically nil so hopefully I manage to ask this question while making some semblance of sense. What I'm trying to do is make a little spreadsheet for an RPG I'm playing. I'm doing this just as kind of a fun little exercise. What I have so far looks something like this: In Cell E2 =OFFSET($L$1,MATCH($D$2,$L2:$L8,0),1) In Cell F2 =OFFSET($L$1,MATCH($D$2,$L2:$L8,0),2) And so on where $L2:$L8 is your list of values under abilities. MATCH returns the position in the list starting from your first cell and OFFSET works by looking at the cell that is 2nd parameter down and 3rd parameter across from the cell defined in the 1st parameter, which is why it starts at L1 instead of L2. iamnotcreative fucked around with this message at 16:46 on Jun 4, 2013 |
|
#
?
Jun 4, 2013 16:43
|
|
|
I'm trying to create a foreign currency converter, and I've hit a snag: I want to set up a spreadsheet that allows me to type in a dollar amount in Column B, the exchange rate in Column C. I want the excel spreadsheet to multiply Column B * Column C if the FX rate in Column C is above 1.00 (ie. 1.0324) and divide if it's below 1.00 (ie. 0.457). In other words, I want the spreadsheet to "detect" whether the value in Column C is above or below 1.00, and multiply or divide depending on the value. I've been at this for days with no luck. 
|
|
#
?
Jun 4, 2013 19:32
|
|
|
melon cat posted:I'm trying to create a foreign currency converter, and I've hit a snag: =if(C2>1,B2*C2,B2/C2)
|
|
#
?
Jun 4, 2013 19:41
|
|
|
Raven31 posted:=if(C2>1,B2*C2,B2/C2) \/ I understand, now. Thanks again. melon cat fucked around with this message at 20:31 on Jun 4, 2013 |
|
#
?
Jun 4, 2013 20:08
|
|
|
melon cat posted:Whoh! Quick response. Thanks! That did the trick. Hope you wouldn't mind me asking (I just want to make sure I completely understand the logic), but how did Excel know to divide if C2 <1? I thought I needed an IF or AND statement to make it work the way you just did... =if(C2>1,B2*C2,B2/C2) is saying the following: If the contents of C2 are greater than 1 multiply cells B2 and C2. Otherwise (i.e. if C2 is NOT greater than 1) divide cell B2 by C2. The if statement occurs at the beginning of the formula. If you wanted to cover more categories than >1 or <1 you would need a nested if statement or another approach. For example, if you wanted to take B2*C2 for C2>1, B2 for C2=1, and B2/C2 for C2<1 you could do the following: =if(C2>1,B2*C2,if(C2=1,B2,B2/C2)) It gets ugly fast and there are usually better approaches but it works. You can also add in OR(), AND(), and other criteria to if statements but none are needed in the scenario you've outlined.
|
|
#
?
Jun 4, 2013 20:23
|
|
|
blue font posted:Basically what I want is to be able to select a property from the Affix dropdown box, and its stat bonus to appear on the column to the right...But I imagine there's a much simpler way of doing this rather than writing one endless that calls every single row on those columns. Any ideas? Whatever value this spreadsheet has for you would be achieved much more easily with an AutoFilter, I'd think.
|
|
#
?
Jun 4, 2013 21:29
|
|
|
blue font posted:My knowledge of excel is basically nil so hopefully I manage to ask this question while making some semblance of sense. What I'm trying to do is make a little spreadsheet for an RPG I'm playing. I'm doing this just as kind of a fun little exercise. What I have so far looks something like this: I know iamnotcreative gave an answer, but also try =vlookup(D2,$L$2:$M$7,2,false) =vlookup(D2,$L$2:$N$7,3,false) Old James fucked around with this message at 22:55 on Jun 4, 2013 |
|
#
?
Jun 4, 2013 22:53
|
|
|
At work I'm sort of the fixer of everything, Point of Sale, hardware, moving boxes, inventory management, and this time it's working with mountains of excel workbooks to pull out data. I'm trying to create an Excel macro which would open a file browser and enable selection of multiple workbooks titled "Daily Report for (The Date)". Then it will look for a sheet in each of the books called "Deposit Sheet" and take the numerical value in cells A1, B2, and C3 in each sheet across the workbooks and create a sheet which returns the sum of the A1 cells across multiple workbooks, the sum of the B2 cells, and the sum of the C3 cells as well. I've seen a similar program on an excel help forum, but it seemed to only work for the original author and everyone else had troubles implementing it. I'd appreciate any help, if one person knows how or a few people contribute to something I can copy, paste, and run I'd be happy to donate a few bucks to a charity or two in someone's name.
|
|
#
?
Jun 5, 2013 19:11
|
|
|
|
| # ? May 26, 2024 02:00 |
|
|
I want to create a Yes/No checkbox for a specific question (ie. D(o you live in Canada?"), whereas the text colour for that text turns red if 'No' is selected. How do I go about doing this? Also- let's say I have two worksheets in the same workbook (let's call them Sheet 1 + Sheet 2). I want whatever information that's typed up in Sheet 1 Cell A1 to automatically appear in Cell B1 in Sheet 2. How do I get this to happen? Sorry for this onslught of questions... still trying to getting better at Excel.
melon cat fucked around with this message at 19:10 on Jun 6, 2013 |
|
#
?
Jun 5, 2013 21:09
|
|