Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

fosborb: Dec 15, 2006; Chronic Good Poster

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.

The whole idea gets worse the more you think about it though. VBA isn’t secure at all because you can just replace the password with the hash of a known password and gain access, then view everything. Or if you’re properly malicious you could edit the code to log all the usernames and passwords that are typed in - because you’re doing all of this in Excel.

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

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 18:26

Literally Lewis Hamilton: Feb 22, 2005

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.

A password on each worksheet wouldn't work because there is 100+ sales reps.

Is PowerBi similar to a Power Bottom?

yes I know what Power BI is

# ? Jun 4, 2018 21:47

BonoMan: Feb 20, 2002; Jade Ear Joe

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

ulmont: Sep 15, 2010; IF I EVER MISS VOTING IN AN ELECTION (EVEN AMERICAN IDOL) ,OR HAVE UNPAID PARKING TICKETS, PLEASE TAKE AWAY MY FRANCHISE

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

BonoMan: Feb 20, 2002; Jade Ear Joe

ulmont posted:

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

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

double nine: Aug 8, 2013

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:

{=PERCENTILE(IF(Table1[graduationyear]=F$1,Table1[annualincome],""),$E2/100)}

F1 is the year(eg 2011), e2 is the percentile (eg 70th percentile)

e: So I'm confused, since everything I read, the percentile function should deliver exactly what my boss asked for, but she explicitly said that percentiles are something else entirely :confused:

. I've sent her a mail for clarification. In the mean time, are there any flaws in this formula?

double nine fucked around with this message at 10:11 on Jun 20, 2018

# ? Jun 20, 2018 08:18

fosborb: Dec 15, 2006; Chronic Good Poster

double nine posted:

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:
{=PERCENTILE(IF(Table1[graduationyear]=F$1,Table1[annualincome],""),$E2/100)}

F1 is the year(eg 2011), e2 is the percentile (eg 70th percentile)
e: So I'm confused, since everything I read, the percentile function should deliver exactly what my boss asked for, but she explicitly said that percentiles are something else entirely . I've sent her a mail for clarification. In the mean time, are there any flaws in this formula?

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

mobby_6kl: Aug 9, 2009; by Fluffdaddy

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

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

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

Sri.Theo: Apr 16, 2008

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

Richard Noggin: Jun 6, 2005; Redneck By Default

Sri.Theo posted:

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?

Sounds like a perfect use case for a PivotTable.

# ? Jul 20, 2018 14:35

docbeard: Jul 19, 2011

Sri.Theo posted:

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?

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

Sri.Theo: Apr 16, 2008

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:

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.

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

Hughmoris: Apr 21, 2007; Let's go to the abyss!

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.

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!

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

Busy Bee: Jul 13, 2004

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

Bruegels Fuckbooks: Sep 14, 2004; Now, listen - I know the two of you are very different from each other in a lot of ways, but you have to understand that as far as Grandpa's concerned, you're both pieces of shit! Yeah. I can prove it mathematically.

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?

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?

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

mystes: May 31, 2006

I might be wrong but I thought Busy Bee meant geolocation.

# ? Jul 24, 2018 16:10

Bruegels Fuckbooks: Sep 14, 2004; Now, listen - I know the two of you are very different from each other in a lot of ways, but you have to understand that as far as Grandpa's concerned, you're both pieces of shit! Yeah. I can prove it mathematically.

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

The Macaroni: Dec 20, 2002; ...it does nothing.

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

khazar sansculotte: May 14, 2004

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:

=CALCULATE(sum(Table[Amount]),Table[BvA]="Actual")-CALCULATE(sum(Table[Amount]),Table[BvA]="Budgeted")

However, when I try to display the variance in a pivot table, I can't figure out how to get it to display three columns ([budgeted], [actual], [variance]) instead of four columns ([budgeted], [variance], [actual], [variance]). I never had this problem when using calculated items in a regular pivot table; creating the calculated item ([actual] - [budgeted]) would just insert a third column. But a PowerPivot measure seems determined to give me two copies of it. I've tried moving around the measure between the "columns" and "values" areas of the pivot table with no luck. Am I just a moron, or is there some sort of stupid trick for this?

# ? Aug 22, 2018 02:18

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

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:

(empty newline)
<table>
 <thead>
  <tr>
  etc etc

Excel 2016/365 can open these (after a warning about file extension not matching file type), but you upload it to Google Docs, and it makes a sheet, but the sheet just shows the raw HTML as above and not the formatted data.

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

mystes: May 31, 2006

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:
code:
(empty newline)
<table>
 <thead>
  <tr>
  etc etc
Excel 2016/365 can open these (after a warning about file extension not matching file type), but you upload it to Google Docs, and it makes a sheet, but the sheet just shows the raw HTML as above and not the formatted data.

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?

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.

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

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

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.

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.

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:

<table class="ReportResultsTable">
                                <thead>
                                    <tr class="ReportHeaderRow">
                                        <th>Product</th>
                                        <th>SKU</th>
                                        <th>Description</th>
                                        <span id="CategoryResultsHeader"><span>
                                                <th>
                                                    Warehouse Name
                                                </th>
                                            </span></span>
                                        <th>Total</th>
                                    </tr>
                                </thead>
                                <tbody>

Frustrating. I can't easily control the output parameters for the "xls" file that is created so I'm looking at trying to fix it on the client/Google Sheets side, but it's looking like that's not possible.

# ? Oct 4, 2018 20:34

mystes: May 31, 2006

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

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

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.

If you really need this to work, you might unfortunately have to start thinking about something stupid like injecting javascript code into the page.

Hah ha! Weird thing I figured out by testing. If I put this line at the beginning:

code:

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">

And this line obviously at the end:

code:

</html>

Google Sheets parses it properly. I'm not even replacing an existing <html> tag because there just never was one there in the first place.

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:

<html>
(everything else)
</html>

All it needed was an a valid enclosing <html> block. Ima get on the phone with someone I think. :dogbutton:

# ? Oct 4, 2018 21:35

Harminoff: Oct 24, 2005; ð½

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

mystes: May 31, 2006

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?

Interacting with Excel stuff through the object model is slow so you want to minimize the number of times you do it.

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

Harminoff: Oct 24, 2005; ð½

mystes posted:

Interacting with Excel stuff through the object model is slow so you want to minimize the number of times you do it.

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.

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

DRINK ME: Jul 31, 2006; i cant fix avs like this because idk the bbcode - HTML IS BS MAN

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:

Application.Calculation = xlCalculationManual
Insert formulas
Application.Calculation = xlCalculationAutomatic

If you�re using a recorded macro it may help to adjust the code, as I see a lot of:

code:

Range(��).select
Activecell.formula blah

You can change this to a single line and make things quicker:

code:

Range(��).formula blah

One further thought, it you have Worksheet level SelectionChange type stuff, it�s worth disabling events while your code runs. Otherwise every time your code enters a value the SelectionChange event will fire.

code:

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Insert formulas
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

Edit based on your above comment. If your formula is moving by the same amount, like where you�d just normally paste something down and it would work you can still make that work with just
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

mystes: May 31, 2006

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.

If the formulas are referencing cells in the same relative position, will they become identical if you use R1C1 notation with FormulaR1C1?

# ? Oct 23, 2018 02:46

Harminoff: Oct 24, 2005; ð½

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

Harminoff: Oct 24, 2005; ð½

Back again. Is there really no way to have a cell be truly blank when using a formula? For example

quote:

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:

With ActiveSheet
.AutoFilterMode = False
With .UsedRange
.AutoFilter
For Col = 1 To .Columns.Count
.AutoFilter Field:=Col, Criteria1:=""
.Offset(1).Columns(Col).ClearContents
.AutoFilter Field:=Col
Next Col
End With
.AutoFilterMode = False
End With

# ? Oct 25, 2018 04:18

Fingerless Gloves: May 21, 2011; ... aaand also go away and don't come back

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

Doc Fission: Sep 11, 2011

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:

=IFERROR(VLOOKUP($A1357,$V$2:$Z$1262,COLUMN()-COLUMN($V1357)+1,0),"")

I think I can rely on this until we finally switch out the database, but it bothers me that I basically have no idea what it means. Can someone explain it to me like I'm five so that I can figure out how to use it practically instead of just tinkering with the numbers until it works?

# ? Nov 8, 2018 17:06

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

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.
code:
=IFERROR(VLOOKUP($A1357,$V$2:$Z$1262,COLUMN()-COLUMN($V1357)+1,0),"")
I think I can rely on this until we finally switch out the database, but it bothers me that I basically have no idea what it means. Can someone explain it to me like I'm five so that I can figure out how to use it practically instead of just tinkering with the numbers until it works?

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

Harminoff: Oct 24, 2005; ð½

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

mystes: May 31, 2006

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

Harminoff: Oct 24, 2005; ð½

mystes posted:

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.

So like this? Seems to work!

quote:

Sub test2()

Dim datetest As Date

Dim holidays As Range

datetest = Format(Date, "mm") & "/15/" & Format(Date, "yy")

Set holidays = Range("A1:A2")

If Date = WorksheetFunction.WorkDay(datetest - 1, 1, holidays) Then

MsgBox ("test")

End If

End Sub

# ? Nov 15, 2018 19:30

Coco13: Jun 6, 2004; My advice to you is to start drinking heavily.

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

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 18:26

AzureSkys: Apr 27, 2003

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:

=IF(SUM(IF(Schedule!$S:$S=$C4,Schedule!X:X))=0,"",MIN(IF(Schedule!$S:$S=$C4,IF(ISNUMBER(Schedule!$X:$X),Schedule!$X:$X))))
=IF(SUM(IF(Schedule!$S:$S=$C4,Schedule!X:X))=0,"",MAX(IF(Schedule!$S:$S=$C4,IF(ISNUMBER(Schedule!$X:$X),Schedule!$X:$X))))

In the end it looks like this, but when I update the next days schedule changes anything that's not today's date and on will be blank:

code:

Product #	Start		Finish		Location
1053		11/21/2018	11/26/2018	North
1054		11/22/2018	11/25/2018	South
1055		11/26/2018	11/29/2018	South
1056		11/27/2018	11/30/2018	North
1057		11/30/2018	12/03/2018	North

I don't need it all in one formula, though that would be nice. I keep failing with trying different IF statements when I break up the different steps since I'm a bit of an excel newbie. What I've been doing is just finding the info on a separate sheet by first sorting the start times on the assignment sheet and then grabbing the product numbers of ones with today's date and on to then use for the on the schedule lookup. I have to then paste in the date/location changes back to the assignment sheet which can potentially be miss aligned. It works, but before I pass this on to others to do I want to simplify it as much as possible.

# ? Nov 28, 2018 12:54

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »