|
DRINK ME posted:I inherited some similarly stupid poo poo spreadsheet which allows people access certain areas and bounces authentication off the ActiveDirectory server but ultimately having the data in Excel means there is a risk someone will just break it and have access to everything. It’s kind of a trade off between - we made it kind of secure and we made a functional spreadsheet to provide the data they need. Jesus that's a lot of workaround for poo poo that probably doesn't need to be that secure in the first place. It's a hard sell but it's an argument worth having. 1. Do you really need this data, even for front line performance metrics, to be secure? 2. If yes, using the wrong tools will actually make it less secure than just giving everyone free access. Are you willing to pay for the right tools? I heard a story about a huge company with tens of thousands of agents. They purchased a performance management suite that was best in class, all web based, combined a dozen data sources every night with full IT and vendor support and never went down anyway. HUGE push back from front line management and leadership to giving front line staff access to the site. Yes, it was all locked down to give just the metrics to the right person, and yes it would have saved every manager 5+ hours a week, but that wasn't the proven method of spreadsheets they had been using for years. They wanted the tool to only be a manager and up reporting tool. So meetings were held and task forces were formed and IT and Workforce Management ventured out into the world to find out why their new fancy tool wasn't meeting needs that Excel apparently could. And this is what they found: Nearly all managers had their own unique set of measures, some outright ignoring the top line measures dictated by senior leadership. Half were just emailing out a combined, unlocked spreadsheet to their entire team anyway. Several managers had added pages with actually secure info like salaries and bonuses and formal write ups that were going out to the team. Adoption improved quite a bit, I hear, when the findings were presented to execs.
|
# ? Jun 4, 2018 21:42 |
|
|
# ? May 13, 2024 10:02 |
|
kiwid posted:Thanks for all the suggestions. I'm currently looking into PowerBi and row-level security which might solve the entire problem. The macro idea exporting it to individual files would probably be my next choice. I'm hoping I can get out of building a web app. Is PowerBi similar to a Power Bottom? yes I know what Power BI is
|
# ? Jun 4, 2018 21:47 |
|
Abstract: I am looking for a way to create a wizard that I can host on a website to auto-populate a google spreadsheet. I work for a production company and we make budgets every day for commercial and film production. It's a great spreadsheet made by our production manager but it's loving HUGE. SO much horizontal scrolling. You're going through all of your crew, clients, freelancers, pre-production days, production days, post production costs, equipment costs, etc. And then each thing essentially has two versions (Tier 1 and 2 depending on production value/budget size of client). The rates and formulas, etc are all in there. It's essentially just going to each person or catagory and putting the number in there. So for Gaffer I just put in the amount of days he or she is working and it auto-populates the cost. But then I have to go find the meals part and put their meal number in. Then go find the travel section and put number of hotel days, etc. You get the idea. I'd like to create a simple wizard/form that would essentially let me enter a lot of data on the front end and it autopopulates the spreadsheet. My OCD production manager doesn't care about this, he loves scrolling though hundreds of spreadsheet cells and manually entering poo poo. Nobody else does. Is their a program or something that would help me with this? I'd like to be able to host it on our website and have it autopopulate a google sheet. But that's not mission critical. Just doing it on my desktop is fine too.
|
# ? Jun 13, 2018 20:25 |
|
BonoMan posted:Abstract: I am looking for a way to create a wizard that I can host on a website to auto-populate a google spreadsheet. You want a Google form linked to a Google spreadsheet. https://www.google.com/forms/about/ https://support.google.com/docs/answer/2917686?hl=en
|
# ? Jun 14, 2018 16:40 |
|
ulmont posted:You want a Google form linked to a Google spreadsheet. Oh, duh yeah that might work! One odd bit of functionality is that we generally work off of a template. Aka I got into his budget form and save a copy elsewhere. Then edit that one. I wonder if there's a way to get the form to save a copy as before editing it.
|
# ? Jun 14, 2018 23:06 |
|
Kinda could use some help with the following: I have a sheet that contains the responses to a survey, in it people reported their monthly gross salary, their gross salary last year and their year of graduation (and a bunch of other info that's not relevant to my problem). The assignment I have been given is to create an excel table with an overview per year of the 10% median, 30% median, 50% median, 70% median and 90% median. Is there a way in excel to automate these calculations? I found the following array formula which almost does what I want it to do, but it uses percentiles, not medians (or whatever the correct mathematical term is): code:
double nine fucked around with this message at 10:11 on Jun 20, 2018 |
# ? Jun 20, 2018 08:18 |
|
double nine posted:Kinda could use some help with the following: Your boss doesn't know what a median is, or is using a very strange meaning of the word. You definitely should check with her to see what the hell she wants. If she says something like, "median 70% means 70% of values will be less than or equal to that value" then your formula is correct and you can just label it median 70% or whatever and be done with it.
|
# ? Jun 21, 2018 02:55 |
|
This is pretty straightforward but I was having difficulty explaining this to google. With a pivot table (and I assume that would work with power pivot, SSAS which is what I really need), is there a way to break down only some measures in one table, like so: It makes no sense (at least in this example) to split number of stores or employees by the product type, but it does for revenue. Of course when I add product type to columns, it splits all measures, not just sales.
|
# ? Jun 28, 2018 13:15 |
|
I have a strange question about using COUNTIF in a data validation formula to prevent duplicates. Here's my scenario: I have a custom QA sheet built for contact center evaluations. Each tab at the bottom is for an individual call, with a cell for the Call ID in G2. I want to ensure our QA folks don't accidentally grade the same call twice. There's a hidden tab named 'Analyst Summary' that pulls in all the data from the call grade sheets, including the CallID input by the grader which goes into Column M. Thus, in each of the individual call sheets, I have data validation in place on the CallID field with the formula: =COUNTIF('Analyst Summary'!$M:$M,G2) <= 1 I can test this out and it works just fine: I put a call id in the contact 1 sheet, go over to the contact 2 sheet and place the same call id, and as soon as I hit enter the validation is triggered. Hooray. HOWEVER, if I instead go to the contact 2 sheet, place the same call id, and instead of hitting Enter I just click out of the cell, the data validation does not trigger. It will let me put in a duplicate without making a fuss. If I do this and then go to the contact 3 sheet and put in the same call id, then regardless of my cell exit method the validation rule kicks in and prevents the duplicate. I'm assuming this has something to do with the order of operations in Excel behind the scenes - but I'm at a loss as to how to deal with this particular quirk. Anyone have any fun ideas? e: this also doesn't work if they copy a cell and paste. it DOES work if they copy the contents of the cell, go to another sheet, double click in the target cell, paste the text, and hit enter good god this is stupid kumba fucked around with this message at 15:07 on Jul 12, 2018 |
# ? Jul 12, 2018 14:24 |
|
Can I get some help with COUNTIFS? I have a table with a list of dates, project titles and a status, (submitted, ongoing, cancelled). I want to make a summary table which shows me how many of each status happened in each year. If I type =COUNTIFS(Table[status], submitted, Table[Deadline],”<01/01/2017> that will give all the things that happened before that date. But I want to separate out each year if anyone can help me?
|
# ? Jul 20, 2018 11:01 |
|
Sri.Theo posted:Can I get some help with COUNTIFS? Sounds like a perfect use case for a PivotTable.
|
# ? Jul 20, 2018 14:35 |
|
Sri.Theo posted:Can I get some help with COUNTIFS? If you don't want to use a PivotTable for some reason (which is by far the simplest solution), a fairly easy way to do this is: Add a column to your table (let's say it's called Year) where the formula is =YEAR([@[Deadline]]) Then use =COUNTIFS(Table[status], submitted, Table[Year],2017) or whatever.
|
# ? Jul 20, 2018 16:07 |
|
docbeard posted:If you don't want to use a PivotTable for some reason (which is by far the simplest solution), a fairly easy way to do this is: Thanks! I might do that as I can’t get the pivot table to show what I want. If I drag Deadline into the ‘Row’ box and removing months and quarters it won’t let me put ‘status’ in the column box to show it the way I want. I admit I don’t really understand pivot tables, sometimes I click the button and it does what I want so I leave it at that!
|
# ? Jul 20, 2018 18:32 |
|
Sri.Theo posted:Thanks! I might do that as I can’t get the pivot table to show what I want. If I drag Deadline into the ‘Row’ box and removing months and quarters it won’t let me put ‘status’ in the column box to show it the way I want. If you do a decent amount of work in Excel then it would pay to become more familiar with pivot tables. This guy has a Excel series that covers all sorts of topics but here is one specific to pivot tables: https://www.youtube.com/watch?v=e-yuYNgsHAk
|
# ? Jul 20, 2018 18:59 |
|
I have an Excel document with about 1500 rows of different IP addresses. How can I take the IP addresses and output the location of the IP in the next column over? I have been manually copying and pasting it into a bulk IP lookup site (Which can only do a max of 100) so its quite a pain in the rear end. But I know there's an easier way out there. What should I do?
|
# ? Jul 23, 2018 19:14 |
|
Busy Bee posted:I have an Excel document with about 1500 rows of different IP addresses. How can I take the IP addresses and output the location of the IP in the next column over? You can do this with VBA in excel. https://www.codeproject.com/articles/712335/followup-dns-lookup-and-ping-in-excel All you need to do is: 1. Copy that code. 2. Press "alt+F11" to enter the macro editor. 3. Paste all that code into 'Module1'. 4. In your actual excel spreadsheet, you can invoke that macro by doing: =GetHostName([cellreference, e.g. B2]) in the cell. (I followed the example and got this working because I was interested in how easy it was to do, there are security issues with running macros so you'll have to save the worksheet as macro enabled)
|
# ? Jul 24, 2018 15:27 |
|
I might be wrong but I thought Busy Bee meant geolocation.
|
# ? Jul 24, 2018 16:10 |
|
mystes posted:I might be wrong but I thought Busy Bee meant geolocation. https://officetricks.com/find-ip-address-location-geoip-lookup/ Oh wait, that's deprecated. Looks like there used to be a lot of apis where you could geolocate using an ajax call.
|
# ? Jul 24, 2018 16:52 |
|
Edit: NM, figured out a Vlookup solution.
The Macaroni fucked around with this message at 14:36 on Jul 26, 2018 |
# ? Jul 25, 2018 21:24 |
|
I have a question about measures in PowerPivot that I'm not even sure how to google up an answer to. I have a giant table of budget data, where I want to compare original budgeted figures vs. what was actually spent. I have successfully written a DAX formula to do this, something like: code:
|
# ? Aug 22, 2018 02:18 |
|
Not sure where else to put this but thought I'd pick some brains here. We have a web app that generates XLS files; however the resulting file actually looks like HTML like so:code:
Doing some reading it seems this is an XHTML format from Excel 2003 onward; is there a way to make Google Sheets parse it properly?
|
# ? Oct 4, 2018 19:18 |
|
Scaramouche posted:Not sure where else to put this but thought I'd pick some brains here. We have a web app that generates XLS files; however the resulting file actually looks like HTML like so: If they use any excel specific features, you will probably need to use Excel to convert them to real xlsx files. That said, the part you have included looks like fairly normal html so you could try changing the extension to html; if Google Docs supports reading normal html files it might work (however anything that uses Excel-specific attributes/styles starting with "mso-" will presumably not work), or you could try opening the files in a web browser and copying the table into google docs. mystes fucked around with this message at 19:41 on Oct 4, 2018 |
# ? Oct 4, 2018 19:35 |
|
mystes posted:Office supports its own weird/poorly documented html format, and it will open these files even if they are incorrectly labelled as being normal office files. As a result, a lot of websites generate documents in this format simply because it's easier then generating real ooxml files, but it's unlikely that other programs that expect real ooxml files will be able to read them because it's a completely different format. In reviewing it further, there's no actual Excel specific functionality present. You are correct in that these are server-generated and don't ever properly touch Excel except maybe via a DLL during the export process. My guess is that the developer is literally taking HTML output, streaming it out, and putting .XLS on the end of the file. Renaming it to HTML is proper useless; Google Docs treats it as a Word equivalent file instead of a spreadsheet for some reason. The weird thing is, the functionality is built right into Sheets via the ImportHTML/ImportXML function, but Google refuses to do it on upload/conversion. I haven't done it but I'm 99% sure if I made an empty Google Sheet and put "=IMPORTHTML("path_to_html_file",table,1)" it would actually work, but these files are disseminated to clients as is, and the majority of them don't even use Excel and are going straight to Google Sheets. There is some CSS info on the HTML, but it's not weird mso- specific stuff, just things like: code:
|
# ? Oct 4, 2018 20:34 |
|
Yeah I don't know what you can do if you have no control over the web application, Google Docs won't open the files without extra work, and you need it to be possible to open the files as-is in Google Docs. If you really need this to work, you might unfortunately have to start thinking about something stupid like injecting javascript code into the page.
|
# ? Oct 4, 2018 20:43 |
|
mystes posted:Yeah I don't know what you can do if you have no control over the web application, Google Docs won't open the files without extra work, and you need it to be possible to open the files as-is in Google Docs. Hah ha! Weird thing I figured out by testing. If I put this line at the beginning: code:
code:
But then I'm thinking; wow Google went out of their way to respect that MS schema? That seems weird... I wonder if... Turns out this will make Google Sheets read it as well: code:
|
# ? Oct 4, 2018 21:35 |
|
Just a general question but I'm just starting to learn VBA so that I can automate some tasks that take me 30+ minutes to complete each day. One portion I need to add formulas to 5 columns, about 1k lines and it takes a few minutes to complete. I do hide what's happening to speed it up a bit. Is VBA just really slow with pasting a lot if data into cells?
|
# ? Oct 23, 2018 02:07 |
|
Harminoff posted:Just a general question but I'm just starting to learn VBA so that I can automate some tasks that take me 30+ minutes to complete each day. One portion I need to add formulas to 5 columns, about 1k lines and it takes a few minutes to complete. I do hide what's happening to speed it up a bit. Is VBA just really slow with pasting a lot if data into cells? If you are inserting the exact same formula in lots of cells you can just set it as the formula for all the cells as a single range at the same time. Otherwise you can first create an array with all the different formulas and set it at once but this is slightly more annoying.
|
# ? Oct 23, 2018 02:24 |
|
mystes posted:Interacting with Excel stuff through the object model is slow so you want to minimize the number of times you do it. Hmm the base of the formula is the same, but it changes to reference a different cell each time. I'll look into arrays and see if that'll help at all as there are a few times I'll need to be doing this with slightly different formulas. It wouldn't be terrible if it just did it all in the background but it seems to lock up any other workbook I'm using so it makes my machine worthless during the run time.
|
# ? Oct 23, 2018 02:31 |
|
Shouldn’t be that long, depending on the complexity of the calculation of course, but no longer than pasting them in. This may help or may just shift where it takes time. code:
code:
code:
code:
Range(“a1:a1000”).formulaR1C1 = “RC[-1]*10” But I’m not sure if the formula you’re describing is far more complex than my thinking / not something that can be reproduced so simply. DRINK ME fucked around with this message at 02:47 on Oct 23, 2018 |
# ? Oct 23, 2018 02:41 |
|
Harminoff posted:Hmm the base of the formula is the same, but it changes to reference a different cell each time. I'll look into arrays and see if that'll help at all as there are a few times I'll need to be doing this with slightly different formulas. It wouldn't be terrible if it just did it all in the background but it seems to lock up any other workbook I'm using so it makes my machine worthless during the run time.
|
# ? Oct 23, 2018 02:46 |
|
Thanks all! Setting it as a range formula did the trick. I had a loop that would paste it to each cell, don't the range formula makes it almost instant. Thanks again!
|
# ? Oct 23, 2018 15:50 |
|
Back again. Is there really no way to have a cell be truly blank when using a formula? For examplequote:Range("B1:B" & lRow).Formula = "=IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE),"""")" Will still return a blank value if nothing is found in column 2. I did find this, and it does work, it just seems like a bit much to a simple problem. quote:
|
# ? Oct 25, 2018 04:18 |
|
At this point, it might be worth jumping straight into an array and cycling through it using direct VBA commands instead of excel formula. This point of working through is extremely useful though. It's so good to look back at your first vba scripts and compare them to now. Some real satisfying stuff.
|
# ? Oct 27, 2018 17:33 |
|
Hi Excel thread. At work I use a database that constantly requires me to smash two data sets together in ways that so far have been awkward and terrible. I think I've finally discovered the magic formula thanks to Google. code:
|
# ? Nov 8, 2018 17:06 |
|
Doc Fission posted:Hi Excel thread. At work I use a database that constantly requires me to smash two data sets together in ways that so far have been awkward and terrible. I think I've finally discovered the magic formula thanks to Google. Easiest to explain starting from inside the thing and working outwards: COLUMN() returns an integer corresponding to the column of that cell it's in; e.g. if you put =COLUMN() in cell B2 it will return 2, because B is the 2nd column. =COLUMN($V1357), because the reference to the column is absolute, is always going to return 22, because it's returning the column value of the cell inside it, in this case column V. So, COLUMN()-COLUMN($V1357) is going to be whatever column that formula is in minus 22. This argument is used within the VLOOKUP to figure out which column of data to grab from your lookup range $V$2:$Z$1262. So, if this formula is in say column Z, then it's effectively IFERROR(VLOOKUP($A1357,$V$2:$Z$1262,(26 - 22)+1,0),""), which is IFERROR(VLOOKUP($A1357,$V$2:$Z$1262,5,0),"") VLOOKUP is looking at the value in $A1357 and attempting to find a match in the first column of your lookup range, so in this case it's looking for that value in column V. If it finds a match, it will return the value in the 5th column in your lookup range, since the col_index_num argument is 5. So, if for example the value of $A1357 is "Ham Sandwich" and cell V100 also contains "Ham Sandwich", the formula will return the value in Z100. If it does not find a match, the VLOOKUP will return an error. The IFERROR(formula,"") part is simply replacing any error returned with an empty string.
|
# ? Nov 8, 2018 17:54 |
|
So I have part of a macro that I need to run on the 15th each month, or next working day. Is there an easy way to do this? Right now I have it check if today is the 15th then run, or if it's either the 16th or 17th and a Monday then run. However doing it this way wouldn't count for holidays, and I'd have to add a bunch of checks to work around holidays. I'm thinking I should be able to use the workday function, but can't seem to figure out how to apply it to this scenario.
|
# ? Nov 15, 2018 17:54 |
|
Basically you just want to run your thing on the date workday(dateofthe15th-1,1) right? It looks like you will also need to have a list of holidays in your worksheet and use that as a parameter as well, though. mystes fucked around with this message at 18:44 on Nov 15, 2018 |
# ? Nov 15, 2018 18:32 |
|
mystes posted:Basically you just want to run your thing on the date workday(dateofthe15th-1,1) right? So like this? Seems to work! quote:Sub test2()
|
# ? Nov 15, 2018 19:30 |
|
I just used a slicer to filter three pivot tables simultaneously so the main one matches the info in two hidden ones connected to visible pie charts that summarize the info in different ways. Also the pie chart titles update based on the slicer automatically.
|
# ? Nov 21, 2018 16:41 |
|
|
# ? May 13, 2024 10:02 |
|
I have a spreadsheet my group uses to keep track of a product number, the date it starts, the date it finishes, and the location it’s made at. We use this to keep track of who’s assigned which product number at which location. There are multiple sources for the start and finish dates along with location and we’ve been looking at those (usually a PDF) and manually putting those dates and names in our spreadsheet, which gets tedious and easy to mistype. The dates and location can change daily or get assigned for the 100 or so product numbers that are needed on the assignment sheet. I’ve recently found a data source that I can give me a csv extract. It lists every date that the product is in work, 3 to 5 days, but I only need the start and finish dates. I’ve figured out a vlookup and sum formula to get the start and end date OK as well as the location for each product number, but there’s a twist. The schedule only lists things that are today and on. I still need to keep the date for things that have passed since my assignment sheet needs to show them. I cannot figure out how to have a formula look up the product numbers start and end date as well as leave the dates unchanged if prior to today after I update the cvs extract. The location part is easy enough to use with vlookup, so that's not an issue. This is my formula I found somewhere for getting the start and finish dates. Schedule is the sheet with the CSV extract, column C on my assignment sheet is the product number which is unique to each product and is also column S on Schedule, and Schedule column X is the dates: code:
code:
|
# ? Nov 28, 2018 12:54 |