|
I'm trying to match strings in one column and show a corresponding row result from a different column. It works fine if the cell contains only that string, but I want to return matches even if additional information is in the cell. Example 1: Typing in the Animal name returns the corresponding character names. Example 2: Same thing, but if there's more information in the cell than just the specified string, then nothing is returned. I want the result to be returned anyway. Here's the array formula used to return the results (all one line): {=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"", INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))} Help me Obi Wan Kenobi.
|
# ? Oct 22, 2016 17:21 |
|
|
# ? May 27, 2024 02:28 |
|
Found the answer on case someone wants to know - =IFERROR(INDEX(B:B,SMALL(IF(ISNUMBER(SEARCH($E$1,$A$1:$A$8)),ROW($A$1:$A$8)),ROWS($E$1:E1))),"") Enter with Ctrl+Shift+Enter.
|
# ? Oct 23, 2016 06:22 |
|
Can anyone see what I'm doing wrong in the following formula? In this spreadsheet suddenly any time I try to do a subtotal or sum it comes up with ref!. I've never had this happen before. I'm on excel 2016 and use the same file on my iPad and computer if that makes a difference.
|
# ? Oct 24, 2016 22:03 |
|
Sri.Theo posted:Can anyone see what I'm doing wrong in the following formula? In this spreadsheet suddenly any time I try to do a subtotal or sum it comes up with ref!. I've never had this happen before. Try switching to relative references on that sheet. Does it give the same result if you use =SUM(R[-3]C:R[-1]C)? When you step through the calculation, what step is underlined and erroring out?
|
# ? Oct 24, 2016 23:31 |
|
SymmetryrtemmyS posted:Try switching to relative references on that sheet. Does it give the same result if you use =SUM(R[-3]C:R[-1]C)? Hmm not really sure my excel skills are up to snuff here, aren't they already relative references? When I put $ in it doesn't make a difference. Here's what I get using that formula (which I completely don't understand!) And here's what I get using the evaluate tool: It's just a basic budget spreadsheet but it has a years worth of data in it so I would really like to keep tracking my spending and savings now I've started.
|
# ? Oct 25, 2016 19:33 |
|
https://excelmate.wordpress.com/2013/04/22/excel-r1c1-reference-style-vs-a1/ That's what that RC business is all about. Anyway, it looks like you are somehow causing Excel to get confused and start looking at a sheet called Summary. Did you by chance define some cells to be named Sum? Although I just tried that and Excel automatically fixed it to use SUM the function.
|
# ? Oct 25, 2016 19:59 |
|
Sri.Theo posted:Hmm not really sure my excel skills are up to snuff here, aren't they already relative references? When I put $ in it doesn't make a difference. You'll have to go into settings to change to relative references. It changes the columns from ABCD to 1234, and you specify row/column offsets with RC notation. Like so: https://excelmate.wordpress.com/2013/04/22/excel-r1c1-reference-style-vs-a1/ Anyway, it might be as simple as capitalizing SUM. I am not familiar with iOS or OS X Excel (how do you get by without Pivot Charts or Power Pivot?), but all of my formulae have always been capitalized on Windows Excel. That's the only difference I can see between yours and what works for me.
|
# ? Oct 25, 2016 19:59 |
|
totalnewbie posted:https://excelmate.wordpress.com/2013/04/22/excel-r1c1-reference-style-vs-a1/ Scratch that, I checked the name manager and the very first table was called Sum, changing that has solved it! However that has been called Sum for 11 months and has never caused an issue before, so I don't feel too stupid. quote:You'll have to go into settings to change to relative references. It changes the columns from ABCD to 1234, and you specify row/column offsets with RC notation. Like so: https://excelmate.wordpress.com/201...ce-style-vs-a1/ Thanks very much for helping out and I am very glad it's solved Sri.Theo fucked around with this message at 20:33 on Oct 25, 2016 |
# ? Oct 25, 2016 20:19 |
|
totalnewbie posted:https://excelmate.wordpress.com/2013/04/22/excel-r1c1-reference-style-vs-a1/ Good catch! That's what I was going to suggest if it worked R1C1 style references, for which I used the wrong terminology.
|
# ? Oct 25, 2016 21:05 |
|
SymmetryrtemmyS posted:How do I turn this: This won't get you to the format you requested (which is awful), but it will be 1000% more manageable: http://dailydoseofexcel.com/archives/2013/11/19/unpivot-via-sql/
|
# ? Oct 27, 2016 07:22 |
|
Random piece of code a I wrote, very handy for certain data-debugging situations. This is one of those things where I did it manually 4 times, and then decided to write a macro on the 5th. http://pastebin.com/EM3JUcuJ
|
# ? Oct 27, 2016 17:10 |
|
Pretty esoteric question, just discovered this pain in the butt and hoping someone can direct me to the right resource. Sooo at work I use VBA/Excel to send emails (basically I take a team report and turn it into 35 individual employee reports) -- I just upgraded to Office 2016 and Outlook gets all righteous now and pops up a box "A program is trying to send an email on your behalf, do you want this to happen? Allow/Deny" for EVERY EMAIL. Does anyone know where I can disable that? nvm: found it in the trust center. Turkeybone fucked around with this message at 15:33 on Oct 28, 2016 |
# ? Oct 28, 2016 15:30 |
|
It seems like that poo poo changes a little bit with every version of Office... It can be a real nightmare if you have different versions of Outlook and Excel.
|
# ? Oct 28, 2016 15:57 |
|
schmagekie posted:This won't get you to the format you requested (which is awful), but it will be 1000% more manageable: http://dailydoseofexcel.com/archives/2013/11/19/unpivot-via-sql/ That is very helpful actually, thank you! It looks like that'll get me a step closer to where I need to be. Another question: How do I copy (but not cut) a table and then paste it - but without the formulae referring to the table it was copied from? I keep an inventory ordering sheet for each vendor we do business with, and when we get a new vendor on board I like to add them as a new entry to the sheet. However, I can't figure out how to paste, for instance: =SUM(PRODUCT([@[Price Retail]],[@[Units Retail]]),PRODUCT([@[Price Wholesale]],[@[Units Wholesale]]),-[@[Cost All Units]]) instead of =SUM(PRODUCT(SKE[@[Price Retail]],SKE[@[Units Retail]]),PRODUCT(SKE[@[Price Wholesale]],SKE[@[Units Wholesale]]),-SKE[@[Cost All Units]]) It's not a huge thing. It would just cut 5 minutes out of my day every time I add a new sheet. So far I've just been find/replacing.
|
# ? Oct 31, 2016 18:48 |
|
Bah, Tables are newfangled crap. I actually think that Replace is the way to go in most cases. When I need to update a worksheet, instead of overwriting the data I: -copy in new worksheet with different name. -click on old sheet name, CTRL C, append "OLD" to end of name, hit enter. -click on new sheet, CTRL V to paste the worksheet name over it. -now do your Replace of "SheetNameOLD" to SheetName" from FORMULAS. Make sure you click global (not just local to the worksheet). -Delete the old WorkSheet "SheetNameOLD" it's really fast if you use keyboard shortcuts. You can also eliminate External References from Formulas this way. EDIT: If you are really adding poo poo every day, and you are using Excel Tables, why not do it the right way and do it in Access? You could write some VBA to pull the new data and append it to your Table (a real Table in Access). Squashy Nipples fucked around with this message at 19:47 on Oct 31, 2016 |
# ? Oct 31, 2016 19:39 |
|
Squashy Nipples posted:Bah, Tables are newfangled crap. I really need to familiarize myself with Access. I really don't know jack about how it works and I only have a loose idea of what it does. I have the full Office 2016 suite including Access, but it seems like it'll take a lot of time which I just can't spare right now. Eventually I'd like to migrate all of the sales data to Access as well so I can more easily and more quickly make reports, but again, time constraints. I don't add new sheets to the ordering book every day, but it's usually at least once a week. I guess find/replace will do fine in the meantime, though.
|
# ? Oct 31, 2016 19:49 |
|
I have a bunch of spreadsheets with numbers of customers and staff during specific hours of the day, and I'd like to count the number of times a month that there are 8 or more customers per member of staff other than the manager. I've used conditional formatting =B2>=MAX(1,((B12-1)*8)) to highlight them in orange but can't find a way to count them other than - after googling around - with third party addons that count the number of cells that are a particular colour, and I'm sure there must be an easier way to do it with a formula/script: Jack the Lad fucked around with this message at 13:26 on Nov 1, 2016 |
# ? Nov 1, 2016 13:20 |
|
Jack the Lad posted:I have a bunch of spreadsheets with numbers of customers and staff during specific hours of the day, and I'd like to count the number of times a month that there are 8 or more customers per member of staff other than the manager. How about using IF()? code:
code:
Squashy Nipples fucked around with this message at 15:38 on Nov 1, 2016 |
# ? Nov 1, 2016 15:35 |
|
SymmetryrtemmyS posted:I really need to familiarize myself with Access. I really don't know jack about how it works and I only have a loose idea of what it does. I have the full Office 2016 suite including Access, but it seems like it'll take a lot of time which I just can't spare right now. Eventually I'd like to migrate all of the sales data to Access as well so I can more easily and more quickly make reports, but again, time constraints. If you are using the Database functionality in Excel, then you are already half way there. Just open up Access and fool around, most of it should be familiar. If you are familiar with SQL, you can use Excel as a front end for an Access DB, and you never have to open the Access application (the Access DataBase format is entirely file-based). This is how I learned to use Access: Bill Jelen's book has a chapter on a simple inventory system that uses Excel as a front end for an Access DB. Seriously, learn SQL, you can do TONS of stuff with it in Excel.
|
# ? Nov 1, 2016 15:38 |
|
Jack the Lad posted:I have a bunch of spreadsheets with numbers of customers and staff during specific hours of the day, and I'd like to count the number of times a month that there are 8 or more customers per member of staff other than the manager. =SUM(IF(B2:AF10/B12:AF20>8,1,0)) and then do CTRL+SHIFT+Enter to make it an array formula It will put curly bracket around your formula to indicate it's an array formula. FWIW I don't think (could be wrong) COUNTIF supports a formula in the criteria. totalnewbie fucked around with this message at 16:16 on Nov 1, 2016 |
# ? Nov 1, 2016 16:12 |
|
Any chance anyone here uses Power BI? Would love not to have to use their community forum.
|
# ? Nov 1, 2016 19:37 |
|
Squashy Nipples posted:How about using IF()? That's how I'm doing it at the moment, duplicating the grids on helper sheets and summing the range, but I'd like to get rid of them because I have hundreds of these sheets and it's getting really clunky/fiddly. I can't find a way to countif with a formula in the criteria, either, if there is one. totalnewbie posted:=SUM(IF(B2:AF10/B12:AF20>8,1,0)) and then do CTRL+SHIFT+Enter to make it an array formula Hm, thanks, this is really helpful - it's gotten me almost but not quite there. On this test snippet, conditional formatting and a regular 1/0 formula =IF(A1/MAX(1,(A5-1))>=8,1,0) work correctly, but the array formula {=SUM(IF(A1:C3/MAX(1,(A5:C7-1))>=8,1,0))} returns 1. Which is weird, because doing the same exact SUM(IF thing but checking if the values added together are greater than 10 works fine, correctly returning 2 for the array formula {=SUM(IF(F1:H3+F5:H7>10,1,0))} Jack the Lad fucked around with this message at 13:04 on Nov 2, 2016 |
# ? Nov 2, 2016 12:26 |
|
Could anyone tell me why ALL() is used in the following function? The description in my book makes zero sense.code:
|
# ? Nov 2, 2016 15:06 |
|
huhu posted:Any chance anyone here uses Power BI? Would love not to have to use their community forum. We've started using powerbi extensively in my organization. I'm not a power user yet but would love to have a place here to discuss it. It's been like crack to management and it's clear I'm going to have to get up to speed very quickly
|
# ? Nov 2, 2016 15:21 |
|
MojoAZ posted:We've started using powerbi extensively in my organization. I'm not a power user yet but would love to have a place here to discuss it. It's been like crack to management and it's clear I'm going to have to get up to speed very quickly after googling I realized it's all "on your iphone/ipad!" presentation bullshit wankery, which means yeah, it's going to be a headache. i mean, their images of the "user" and the "cloud" are still line drawings of identical laptops, except they have different power buzzwords next to them. Hot poo poo! Sales and management would love to have realtime data at their and everybopdy else's fingertips (no they don't want that, they'll get caught in lies by wankers on smartphones during business meetings)
|
# ? Nov 3, 2016 04:03 |
|
Jack the Lad posted:That's how I'm doing it at the moment, duplicating the grids on helper sheets and summing the range, but I'd like to get rid of them because I have hundreds of these sheets and it's getting really clunky/fiddly. When you use the Evaluate Formula function in Excel (under the FORMULAS tab), you get: So, the reason MAX isn't working is because it returns the largest value between 1 and the values in the array (A5:C7-1). Thus, it returns just plain old 2. Each value in the array divided by 2 is less than 8, except for the day when you had 16 customers. You're using MAX to avoid a DIV/0 error. You can get around this a couple of ways: =SUM(IF(A1:C3/(IF(A5:C7-1=0,1,A5:C7-1))>=8,1,0)) This puts a logical IF inside your formula to check if the quotient is 0 (replace with 1 in that case) and then use the calculated value if it's not 0. Or, =SUM(IF(IF(A5:C7-1=0,A1:C3,A1:C3/(A5:C7-1))>=8,1,0)) Where you check first if the quotient is a 0 and if true, just use the A1:C3 value (aka divide by 1), and if false carry on with your math and use that for your >=8 check. Whichever way is easier for your mind to process.
|
# ? Nov 3, 2016 18:48 |
|
Jack the Lad posted:That's how I'm doing it at the moment, duplicating the grids on helper sheets and summing the range, but I'd like to get rid of them because I have hundreds of these sheets and it's getting really clunky/fiddly. As much as I love Matrix Formulas, very few people understand them, and they are easy to break. In my mind, the ultimate solution is a User-Defined Formula. Searching for filled cells is easy, you can just check for "CellReference.Interior.ColorIndex <> xlNone" However, Conditional Formatting is NOT the same; a cell colored by Conditional Formatting is totally different from a fill color. Ever worse, you can't check for it, as if the Conditional coloring is invisible. This issue has pissed me off before, so I went a little psycho with a solution. BUT... you can evaluate the Conditional Criteria to see if it SHOULD be colored. Close enough. Googling around, I found this code: (which I cleaned up a little) code:
code:
|
# ? Nov 3, 2016 18:53 |
|
Those are some pretty extreme measures
|
# ? Nov 3, 2016 19:02 |
|
I used to know how to do this, but I've been stuck in customer facing land for too long lately. I've been given a longish sheet of products/services, that have quantities, and prices:code:
In database parlance it'd be pretty easy, something like: code:
|
# ? Nov 3, 2016 20:00 |
|
totalnewbie posted:Those are some pretty extreme measures Yeah, I kind of nuked the site from orbit. But that's what UDFs are for! Scaramouche posted:I used to know how to do this, but I've been stuck in customer facing land for too long lately. I've been given a longish sheet of products/services, that have quantities, and prices: COUNTIF, COUNTIFS, SUMIF, SUMIFS can do a lot of SQL type calculations. Using the columns in your example: =SUMIF(A2:A4,"=Anvil",B2:B4) =SUMIF(A2:A4,"=Anvil",C2:C4) You can also use cell references for the Criteria piece (if you have a range of header cells with all of the unique names in it.)
|
# ? Nov 3, 2016 20:11 |
|
Hey thanks for that quick reply. Unfortunately I was too optimistic about the integrity of the data, and it's going into a temp database after all because crap is all over the place on this one (for example I was guaranteed that Price wouldn't change over time. Guess what??? It changes over time.)
|
# ? Nov 3, 2016 20:44 |
|
Scaramouche posted:I used to know how to do this, but I've been stuck in customer facing land for too long lately. I've been given a longish sheet of products/services, that have quantities, and prices: Pivot Table?
|
# ? Nov 4, 2016 02:01 |
|
Speaking of Pivot Tables they are one facet that I seem to let run in one ear and out the other every time I start to try to learn or work with them, does anybody have any recommends on a good website/youtube/book/whatever which would be helpful to grasping just how these things operate? I always mess up placing everything initially and then it goes haywire, or at least I'll get my horizontal and verticals mixed together and up up backward.
|
# ? Nov 6, 2016 15:17 |
|
Is it possible to create a form that can export data and add the data to an excel file? I've got a bunch of info on different cases I need to get into one excel file but so far I've had to manually copy and paste everything. Speaking as a person who is already ripping his hair out using excel for basic things, please tell me there's an easier way and don't have to learn macros/excel wizadry
|
# ? Nov 13, 2016 07:10 |
|
The Biggest Jerk posted:Is it possible to create a form that can export data and add the data to an excel file? I've got a bunch of info on different cases I need to get into one excel file but so far I've had to manually copy and paste everything. Speaking as a person who is already ripping his hair out using excel for basic things, please tell me there's an easier way and don't have to learn macros/excel wizadry It depends on what format the data is in at the moment, but best case scenario (it's in a standard format in another Excel file) it may be as easy creating your columns in a new workbook and then setting one row to reference the other workbook and copying that row down. If you post an example of the current and desired layout, it'll be easier to help with. If you want to gather data using a form and drop it into a spreadsheet going forwards, Google Forms is pretty good and I've used it for that in my job with some success. Squashy Nipples posted:This issue has pissed me off before, so I went a little psycho with a solution. totalnewbie posted:Whichever way is easier for your mind to process. Jack the Lad fucked around with this message at 10:43 on Nov 13, 2016 |
# ? Nov 13, 2016 10:40 |
|
The Biggest Jerk posted:Is it possible to create a form that can export data and add the data to an excel file? I've got a bunch of info on different cases I need to get into one excel file but so far I've had to manually copy and paste everything. Speaking as a person who is already ripping his hair out using excel for basic things, please tell me there's an easier way and don't have to learn macros/excel wizadry If you're working with something like products in different categories, you could use a series of pull-down menus depending on what you're already got entered, and what metrics you're using to choose.. It's be pretty simple to do something like "Products>Shoes>Nike>Womens'>Running>Model # xkghjhg" for instance, or maybe "Product type> starting date>ending date". We would really want to look at what kind of poo poo you're working with first though, because it's really hard to make a square spreadsheet fit a round solution
|
# ? Nov 16, 2016 01:30 |
|
You guys have any recommendations for learning formulas for beginners? My GF just got promoted into management and now needs to become Part of the Problem by adding to the impenetrable nest of Excel that her company runs on, but I don't use it regularly enough to confidently teach someone else because I usually just munge CSVs in Python
|
# ? Nov 16, 2016 16:44 |
|
I need to learn Python, I still fire up Excel to process CSVs. It's pretty old, but I've always recommended Bill Jelen's Guerilla Data Analysis: https://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336/ Squashy Nipples fucked around with this message at 17:27 on Nov 16, 2016 |
# ? Nov 16, 2016 17:23 |
|
I'm dumb and bad, and need help. I have a VBA project in Excel that has a national map. Each state gets color coded red, yellow or green depending on a result when you run a macro. There are also text boxes that dynamically update from a field I have concatenating from the result with some text to list the state name. I have three different types of users. I don't want to have to recreate this map three times. My goal would be to have a vba script that allows me to choose one of the three user types, which would then have a pivot table change to that user type listed by state, grab those results, color code the states and have the text boxes update. Is there a potentially easy way to do this?
|
# ? Nov 16, 2016 18:14 |
|
|
# ? May 27, 2024 02:28 |
|
Why not just have it make all three, and the users can look at the one they want? I'm not seeing why you would want the user to dynamically generate it. And yes, pivot tables have their own Object Model, and can be manipulated with VBA code. However, I hate using pivot tables, so I can't advise you directly. Try using the macro recorder to get an idea.
|
# ? Nov 17, 2016 16:38 |