|
fosborb posted:Honestly a Google form is a great idea. We used it for diaper tracking with our newborn. Sounds like essentially the same requirements. Yeah, it worked really well until it threw a splash screen that was like "drive is experiencing difficulties, try again later or refresh" and wouldn't budge. I'd never seen that before, but it sucked. That point of failure is just too much to risk in our main sign in, you've got like 45 semi desperate people lined up waiting to access our service. It'd also be best if it displayed something like "Mohamed has been here for 2.15 hours/since 9:42am" when Mohamed signs out, and there was no way for Forms to make that happen that I could see.
|
#
?
Apr 6, 2016 14:17
|
|
|
|
| # ? May 23, 2024 20:01 |
|
|
fosborb posted:Honestly a Google form is a great idea. We used it for diaper tracking with our newborn. Sounds like essentially the same requirements. I suppose you could probably make an excel spreadsheet with all users' names in a column, beside radio buttons and a "SIGN IN/OUT" button tied to a macro to find whatever name is selected on a protected sheet (or in another file, depending on how secure you want.) Then just have it pop up a dialogue box echoing the information whenever someone clicks. Why can't you lobby for a copy of Access though? Excel is kind of rinkydink for something like this, imho. coyo7e fucked around with this message at 17:50 on Apr 6, 2016 |
|
#
?
Apr 6, 2016 17:46
|
|
|
coyo7e posted:Yeah I agree on the google form thing, you could even just buy an iPad and bolt it to something, making it essentially a sign in appliance. Oh poo poo, turns out we do have ms access but I had to log in to our office account to check because nobody here has heard of it before! Well, I'll set that up now. Thanks, thread.
|
|
#
?
Apr 6, 2016 17:57
|
|
|
tuyop posted:Oh poo poo, turns out we do have ms access but I had to log in to our office account to check because nobody here has heard of it before! Well, I'll set that up now. Thanks, thread. I still think a single locked sheet that pushes data into an access db might be the most bulletproof (largely depewnding on how users will be accessing the log in/out application), and also if you're not versed in Access it may be simpler to keep most of the gears in a workbook and most of the data in a db which nobody even really needs to know exists except in the vague notion that their clocking in and out must be getting stored somewhere. That way only HR/mgmt would be able to touch the actual information no matter what they do.. Just push the Name+time into a cell that's the same color text as the background, and then have it clear the cell after it echoes the pop-up dialogue for the user when they click the button. Depending on the number of employees it might be easier to either use the list of names and radio buttons like I mentioned above, or simply use data validation to ensure that they didn't typo their name - just having it error out "employee name not found!" or something if they typo - which is also likely. You also wouldn't need radio buttons for sign in/out if you make your macro button variable use, and it would reduce the chance of users clicking the wrong one and logging in multiple times (which is, frankly, likely as heck.)  The main hangup at that point is the question of whether users will be opening this sheet themselves from their own workstations (in which case, pulling their exchange info would be simpler as mentioned by someone else), or if you want to have a dedicated kiosk for people to log in and out when they come into the building. If users are accessing the sheet directly from their own computers it may cause problems with them never closing the sheet when they are finished with it etc, and at that point there would definitely be other, more elegant ways to do this - like push a clickable icon directly on their user desktop, that links to a script that does all this itself. Or hell, you just insert it into a login script that runs when they log in/out of their machines.. But people don't like to log out of their computers in my experience, and they DO like magic desktop icons that do poo poo for them. coyo7e fucked around with this message at 18:48 on Apr 6, 2016 |
|
#
?
Apr 6, 2016 18:42
|
|
|
I've got a bunch of columns with the header in row 1. Underneath in each column, its either a blank cell or has a number value. For each row, I want to concatenate all the headers in each column, as long as the cell in that row has a number greater than 0. The slow way would be something like: =if(A2>0,A$1,"")&if(B2>0,B$1,"")&if(C2>0,C$1,"")&if(D2>0,D$1,"").... The problem with this is that I've got about 20 columns so it'll take a while to type things out.
|
|
#
?
Apr 7, 2016 00:37
|
|
|
Yep, sure will! Or you could use the autofill feature to automatically make a sequence of formulae and then point them all to where you need to, in sequence. But yeah, long-rear end nested ifs are still a thing.
|
|
#
?
Apr 7, 2016 02:06
|
|
|
coyo7e posted:Yeah it seemed odd to own Excel but not Access, more likely that it's just not installed on every machine. This is awesome, thanks. But it's actually just a laptop on a desk by the door random members of the public sign into instead of a paper sheet in a binder that does the same thing. I didn't have the time to work on it that I'd like, but what I picked up on was basically that I need to export a form and fullscreen it somehow so that the laptop only displays that form, and a "signing out" entry must display total time in for the current day, even if a particular person signs in and out three or four times that day. What are the commonly recommended Access tutorials? Wikihow has a good primer on relationships and queries, but it kind of leaves out a lot of stuff.
|
|
#
?
Apr 7, 2016 05:25
|
|
|
The templates you can download in Access are pretty functional if you don't mind renaming a few fields. If you're in Access 2013 or later (I'm looking at 2013, but I assume that 2016 has the same functionalities), then when you open it you should have the option to try out a couple pre-built things. The "Desktop event management" one looks like it would do about 90% of what you're looking for right out of the box?
|
|
#
?
Apr 7, 2016 13:11
|
|
|
Just remember that Access Forms are totally different from MS Office Forms. It's possible to import MSO Forms into Access, but it's a hacky solution, and you shouldn't do it. You can still put VBA code into Access Forms, but they are designed to built through the Access front end, and are usually based on underlying queries.
|
|
#
?
Apr 7, 2016 14:24
|
|
|
tuyop posted:This is awesome, thanks. But it's actually just a laptop on a desk by the door random members of the public sign into instead of a paper sheet in a binder that does the same thing. Go to Barnes & Noble for an hour or two and just grab every Access book you can find and pile them on a table and thumb through them all, to figure out which one you like the writing style and difficulty level versus how much stuff you can reference is in it. I have about $300 of Excel reference guides and tutorial books because management realized that having a library of them made it ten times easier for me to make spreadsheets, and I could also lend them out to the HR/finance/etc people when they got a wild hair and wanted to make or streamline a spreadsheet as well.. Sometimes you can't figure out the term or the context to do a google or Office Help search, and having a book handy helps a ton.
|
|
#
?
Apr 7, 2016 14:41
|
|
|
Speaking of, does anyone have a better book than Writing Excel Macros with VBA, 2nd Edition? It's an absolutely perfect reference in every way, except even I have to admit "newly updated for 2002!" is maybe a little silly at this point. Though I do still believe you have to be way down the rabbit hole if you're loving about with Ribbon collections in VBA, and really what else has changed since 2002?
|
|
#
?
Apr 7, 2016 14:57
|
|
|
fosborb posted:Speaking of, does anyone have a better book than Writing Excel Macros with VBA, 2nd Edition? ADO/Recordsets/etc. VBA hasn't changed much but the data mangling keeps getting updated every few years.
|
|
#
?
Apr 7, 2016 15:29
|
|
|
So I'm the dude in the office who knows the most about Excel. By "most", I mean "knows more than putting numbers in a square." I've tasked myself with a project that is really starting to annoy me and I will do my best to give all the details that I can remember: Right now we all share a workbook hosted on a SharePoint page. In it we have a log of daily events (I work in an emergency coordination center) that gets updated a few times an hour. We have a nice TV mounted on the wall that is hooked up to a laptop that is networked and can access our SharePoint site. What I want to do is create a Powerpoint that cycles between two frames with linked summaries of our log/other information AND auto-refreshes every time it loops. I've looked everywhere online and I think I have it figured out except that the PP will only update once in presentation mode. The other option is to create my own linked version of the log and just keep it open in the background. The issue with this is that I can turn it into a shared workbook that auto refreshes every 5 minutes (the minimum) but that never seems to trigger. When I go to "Refresh All" in the data section, "Connection Properties" is always greyed out. Even on a brand-new work book it is unavailable, even when I've gone through the Trust Center and opened up every option. (Sorry, this is getting long in the tooth) We have very odd restrictions placed on what we can and cannot access since I work for the government and I'm starting to think that this just might not be possible.
|
|
#
?
Apr 7, 2016 16:20
|
|
|
Anyone ever taken an excel test for a job interview? I know pivot tables and vlookups and I'm generally pretty competent in excel outside of VBA, but I'm not really sure what to expect. Any particular things to study up on?
|
|
#
?
Apr 7, 2016 18:19
|
|
|
Xandu posted:Anyone ever taken an excel test for a job interview? I know pivot tables and vlookups and I'm generally pretty competent in excel outside of VBA, but I'm not really sure what to expect. Any particular things to study up on? Hard to say without more context, but these are often pretty important in an analysis position: Autofilter and sorting Absolute vs. relative references SUMIF() and COUNTIF() The basics of SUMPRODUCT() and array formulas Named ranges Goal seek (If it's financial) A lot of the financial functions, particularly PMT(), PV(), FV(), IRR(), NPV() Also formatting - if they want an example work product instead of just an answer make all your results clean and readable If I were performing the test I would also be watching you to see if you utilize keyboard shortcuts or navigate with the mouse (more shortcuts, less mouse is better).
|
|
#
?
Apr 7, 2016 18:43
|
|
|
Thanks! It's a financial services company, but I'm not sure if they're expecting financial modeling or just general excel skills, so I'll probably study up just in case.
|
|
#
?
Apr 7, 2016 18:49
|
|
|
I would argue that there are VERY few things that you can do with Array Formulas that you can't do with CountIf(s), SumIf(s), and the like. That said, knowing how to use Array Formulas is still impressive. I would also be impressed with someone who still uses the old school (pre-ribbon) shortcut keys.
|
|
#
?
Apr 7, 2016 19:30
|
|
|
Squashy Nipples posted:I would argue that there are VERY few things that you can do with Array Formulas that you can't do with CountIf(s), SumIf(s), and the like. That said, knowing how to use Array Formulas is still impressive. TRANSPOSE() is super useful and MMULT() sometimes is a time saver. The really useful thing that array formulas can do is a custom operation on the subset of numbers using custom criteria - such as finding the median, max, or min, or using the LARGE() and SMALL() functions. Array formulas are also easier to update and maintain than COUNTIFS() and SUMIFS() since array formulas can use regular IF() statement logic instead of being forced to use kludgy text based formulas like =SUMIF(A:A, ">"& C5, B:B) When you could use {=SUM( IF(A:A>C5, B:B, 0) )}
|
|
#
?
Apr 7, 2016 20:08
|
|
|
esquilax posted:TRANSPOSE() is super useful and MMULT() sometimes is a time saver. OK, I overspoke when I said "VERY". Yes, those can be handy. esquilax posted:Array formulas are also easier to update and maintain than COUNTIFS() and SUMIFS() since array formulas can use regular IF() statement logic instead of being forced to use kludgy text based formulas like Agree that the text-string criteria is kludgy, but it's powerful, and Count/Sum/Average If(s) are pretty much always faster then array formulas, particularly for large data sets.
|
|
#
?
Apr 7, 2016 20:42
|
|
|
Inzombiac posted:I've tasked myself with a project that is really starting to annoy me and I will do my best to give all the details that I can remember: In SharePoint, see if you have an Excel Services web part, or see if your SharePoint farm admin will enable it for your site. Might solve your problem right there http://tavislovell.com/the-dark-art-of-the-excel-services-workbook-data-refresh/
|
|
#
?
Apr 7, 2016 23:25
|
|
|
Speaking of good uses for Array Formulas, I was just digging around in an old report I made, and found this:code:Does anyone else do the huge number row thing? I suppose using dynamic ranges would be a slicker way to do that.
|
|
#
?
Apr 8, 2016 03:25
|
|
|
Dynamic ranges hurt performance more I think.
|
|
#
?
Apr 8, 2016 03:32
|
|
|
I use format as table and reference the column. East to reference; haven't noticed a performance impact. But I still hold to my maxim: if you're worried about Excel formula speed on a regular basis, you are likely being asked to do a job without management being willing to invest in the right tools for the job... and so you should start your job search.
|
|
#
?
Apr 8, 2016 05:59
|
|
|
fosborb posted:In SharePoint, see if you have an Excel Services web part, or see if your SharePoint farm admin will enable it for your site. Might solve your problem right there Haha, I am the admin despite having literally zero training. I'll give this a look.
|
|
#
?
Apr 8, 2016 07:26
|
|
|
As deep as my Excel experience is, it mostly on the data crunching/VBA automation/reporting side. I don't have much experience with the more complicated visualization stuff. I want to make a chart that shows the competitve landscape for a specific segement of a certain business. I've got my axes figured out, but I also want to do some cutesy poo poo like make the dot that appears scale based on the size of the company, and change the shape, too (if they are only focused on this segment, they get a circle, if they serve other segments as well they get a square). Any advice on this? I'm in process of creating the data, just not sure how to chart it. I might just fake it all by hand in PowerPoint, but that will be manually intensive. fosborb posted:I use format as table and reference the column. East to reference; haven't noticed a performance impact. I tend to avoid using Tables, as it just seems simpler to do it the old way; I have not yet come across a circumstance where I couldn't do what I wanted without resorting to tables. Maybe I'm just old and stuck in my ways?
|
|
#
?
Apr 8, 2016 15:41
|
|
|
Xandu posted:Anyone ever taken an excel test for a job interview? I know pivot tables and vlookups and I'm generally pretty competent in excel outside of VBA, but I'm not really sure what to expect. Any particular things to study up on? So, turns out they just wanted some pivot tables and basic multiplication. I blew the guys mind with an array formula, though, so thanks for mentioning that. 
|
|
#
?
Apr 8, 2016 16:04
|
|
|
Squashy Nipples posted:I want to make a chart that shows the competitve landscape for a specific segement of a certain business. I've got my axes figured out, but I also want to do some cutesy poo poo like make the dot that appears scale based on the size of the company, and change the shape, too (if they are only focused on this segment, they get a circle, if they serve other segments as well they get a square). Bubble chart, each shape is a different series Insert a shape wherever on the sheet, copy it, select your series and paste. Boom. Custom series markers, different sizes. Cutesy as gently caress, though. You are right. You should embed this chart in a PowerPoint with animations and sounds for stylistic consistency. quote:I tend to avoid using Tables, as it just seems simpler to do it the old way; I have not yet come across a circumstance where I couldn't do what I wanted without resorting to tables. Maybe I'm just old and stuck in my ways? Yes, you are, grandpa. Big thing in VBA with tables is not futzing around with used ranges constantly. Easier to maintain hard coded range/column references in code.
|
|
#
?
Apr 8, 2016 16:20
|
|
|
Xandu posted:Anyone ever taken an excel test for a job interview? I know pivot tables and vlookups and I'm generally pretty competent in excel outside of VBA, but I'm not really sure what to expect. Any particular things to study up on? A little late, but just to chime in -- at my company, the "excel test" was some esoteric online thing that really tested the limits of my knowledge on print areas, print margins, and other printing poo poo. But on the flipside, I VBA'd my morning updates so I bullshit and have coffee while the other analysts are slaving away.
|
|
#
?
Apr 8, 2016 17:35
|
|
|
Squashy Nipples posted:I tend to avoid using Tables, as it just seems simpler to do it the old way; I have not yet come across a circumstance where I couldn't do what I wanted without resorting to tables. Maybe I'm just old and stuck in my ways? I also avoid tables generally, but the Fuzzy Lookup add-in asks for them, so I use them for that (I have a fair amount of slightly-different data matching that Fuzzy Lookup is a lifesaver for).
|
|
#
?
Apr 8, 2016 17:36
|
|
|
fosborb posted:Bubble chart, each shape is a different series Awesome, thank you! fosborb posted:Cutesy as gently caress, though. You are right. You should embed this chart in a PowerPoint with animations and sounds for stylistic consistency. Oh, bite my shiny metal rear end.  Sometimes you have to give the client what they want, even if it's a bunch of fluffy BS. fosborb posted:Yes, you are, grandpa. Ah, OK, I never thought of it in terms of VBA. That makes a lot of sense, I'm just used to keeping Long variables around to store ending rows. This also obviates the need for dynamic ranges.
|
|
#
?
Apr 8, 2016 17:48
|
|
|
fosborb posted:Insert a shape wherever on the sheet, copy it, select your series and paste. Boom. Custom series markers, different sizes. I couldn't make this part work, but at the end of the day, one extra axis was too much info. Better off without it. That said, googling around, I found this guy, how is putting whole loving pie charts in the bubbles: http://www.andypope.info/charts/piedatamarkers.htm I can't think of a good application for it, but it's pretty cool.
|
|
#
?
Apr 8, 2016 19:58
|
|
|
fosborb posted:Speaking of, does anyone have a better book than Writing Excel Macros with VBA, 2nd Edition?  fosborb posted:Bubble chart, each shape is a different series  That pdf was the first result I found on google however, it's a little more effective if you go purchase a copy of the booklet. I'll remove the link if it's :files: Squashy Nipples posted:I couldn't make this part work, but at the end of the day, one extra axis was too much info. Better off without it. Xandu posted:So, turns out they just wanted some pivot tables and basic multiplication. I blew the guys mind with an array formula, though, so thanks for mentioning that.  
coyo7e fucked around with this message at 01:22 on Apr 9, 2016 |
|
#
?
Apr 9, 2016 01:01
|
|
|
So let me rephrase my earlier question: In Excel 2010, what reasons would there be for the "Connection Properties" option to be greyed out all the time? (In the data section in the drop-down in "Refresh All". It has the options I need but I cannot access it within any type of book I open. Yes, I have the not-recommended options on in the Trust Center.)
|
|
#
?
Apr 11, 2016 16:39
|
|
|
Do you have the sheet protected, or multiple sheets selected?
|
|
#
?
Apr 12, 2016 00:29
|
|
|
I have no clue but I must have shaken some gears loose over the weekend and now the whole project is working. There is a point where I have to walk away for a day and I never know when that is.
|
|
#
?
Apr 12, 2016 03:15
|
|
|
coyo7e posted:Anybody who likes to make charts should probably read this (Edward Tufte's Visual And Statistical Thinking: Displays of Evidence for Making Decisions) a couple times. It doesn't go into ethical dilemmas of massaging how yo u present data however, it's pretty easy to extrapolate how you can commit some pretty intense fuckery by simply changing intervals or aggregating data via different methods. Spoiler: Tufte actually commits a bit of emotional blackmail in the Challenger explosion example, in order to solidify the importance of his premise. Yuuuup. I actually keep Visual Explanations at work for inspiration. Stephen Few's Information Dashboard Design is also invaluable, though not currently at my desk because I'm trying to teach my team some god damned design principles. (also lending out Robin Williams' The Non-Designer's Design Book) Here's the thing. In the face of the unrelenting demand for 3D bar charts, bar-line combo charts, and *shutter* gauges, I'm actually willing to compromise on doughnut charts if the only other option is straight pie chart bullshit.
|
|
#
?
Apr 13, 2016 08:55
|
|
|
Is this the place to ask for excel (VB) scripting?
|
|
#
?
Apr 13, 2016 15:54
|
|
|
I've got a PowerPivot issue that I feel like I should have figured out by now but it's been bugging me for months that I can't get it working (Well, not continuously but everytime I think about it I can't figure it out) On a pivot I would like to have comments I make about a vendor show up in the values area. To simplify the scenario, I have a 'purchasing details' table with vendor and dollar amount as fields. Another table called "Comments" that has vendor and comment as the fields. This is what I want:  I've got a many-to-many relationship between the vendors so I shouldn't have any issues. I realize powerpivot probably wasn't made for this since I'm not actually aggregating anything since it's just text but I wish there was a way to do do this in powerpivot. Anyone have any ideas?
|
|
#
?
Apr 13, 2016 21:04
|
|
|
PRADA SLUT posted:Is this the place to ask for excel (VB) scripting? I'm halfway competent in VB. Ask your question and if you're lucky someone infinitely more qualified will pick it up. 
|
|
#
?
Apr 13, 2016 21:23
|
|
|
|
| # ? May 23, 2024 20:01 |
|
|
I'm looking for a VB script that will search a single column for a entry or entries (by name), and if it finds it, apply some format to the cell (font color or highlight it). So if I wanted it to search for butt, it would highlight butt, thebutt, and butt 2k16.
|
|
#
?
Apr 13, 2016 22:23
|
|