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

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

Is there an easy way in VBA to set the value of a cell to the value of VLOOKUP([@column], [table in another workbook], #, FALSE)? I'm looping through a list of tables and want to get rates from a summary table in a completely different workbook without having to do more loops to find the right row. I guess in theory I could just put in the VLOOKUP function I need and then copy/paste as values, but that seems... inelegant.

# ¿ Jul 17, 2020 04:28

Adbot: ADBOT LOVES YOU

# ¿ May 3, 2024 10:18

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

At some point one of my pivot tables seems to have turned into a power pivot table, which means now all my GETPIVOTDATA functions that used that table are borked. :1 Basically I had a cell that looked like this:

code:

=GETPIVOTDATA("Reg Hours",Pivot!A3,"EMP #",B5,"FUND",101,"ACCOUNT #",D5)

But now for it to actually show the number I want it has to look like this:

code:

=GETPIVOTDATA("[Measures].[Sum of REG]",Pivot!$A$3,"[Board].[EMP #]","[Board].[EMP #].&[0000000]","[Board].[FUND]","[Board].[FUND].&[000]","[Board].[ACCOUNT #]","[Board].[ACCOUNT #].&[000000000]")

Should I just regenerate the pivot table and give it the same name so I don't have to redo my functions, or is keeping it a power pivot table better for some reason?

Also I forgot to post and say so but application.vlookup totally worked in solving my previous problem. :3:

# ¿ Jul 27, 2020 22:00

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

If you pre-format the column as a text column and paste as values/match destination formatting, does that prevent it from happening?

My question:

So I've got this one file called history.xlsm that contains sheets for different employees with the history of their raises, etc, and it's got a table called summaryTable that uses this bit of code (the AllSheets one, not the plugin whatever) to basically create a table of contents of every sheet in the workbook and also whatever everyone's current rate of pay is.

I also have another file called whatever.xlsm that uses a table called wageInput of everyone's current rate of pay to calculate a whole bunch of other poo poo in other sheets.

Right now the fastest way to make sure whatever.xlsm is up to date is to open up history.xlsm, copy the contents of summaryTable, and then paste those contents into wageInput. But I'm LAZY and I just wanna push a BUTTON that's shaped like a DUCK for some reason.

In general, I know how to open another workbook, copy some data from it, and then close it, but I am running into problems first of all with the AllSheets named function I'm using, which sometimes borks and pulls info from whatever.xlsm instead of history.xlsm (bad), and second of all with copy-pasting specifically from summaryTable into wageInput as named ranges.

# ¿ Dec 30, 2020 23:19

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

As long as you keep A N Other as the final column of the range and insert any other columns you want before that, you can just do [@MS]:[@A N Other]

# ¿ Jan 2, 2021 17:17

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

Harminoff posted:

Should be able to import with power query and than refresh that to get updated data. No need to copy/paste

I never use power query so I spent like an hour today overthinking how to do this without breaking anything and now that I have it figured out I feel dumb as hell. There's... a lot of things I should have been using power query for. :cripes:

# ¿ Jan 4, 2021 21:52

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

I poked at the idea yesterday because I use a ton of dropdowns and dependent vlookup stuff anyway, but gave up because it turns out that making a second dropdown dependent on the first is actually a huge pain in the rear end and not elegant at all. So, maybe not what you're looking for.

# ¿ Feb 23, 2021 17:12

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

Is getting PowerQuery to source from relative file locations as difficult as the internet is making it look? I just want to be able to copy a folder containing the file and the subfolder to Wherever and still have it work. Having to dedicate a cell to my worksheet's current file location seems like a bonkers solution versus just using something like ./ but maybe I'm the weird one for thinking so.

# ¿ Dec 8, 2021 17:28

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

I gave up on the whole endeavor when I discovered that an Excel file in a OneDrive folder will default to returning its location as being a web address rather than the local folder. Maybe turning off using Office applications to sync would have fixed it? But by the time I was looking at bigass functions in StackOverflow I decided there was no way it would actually save me any time or energy. :argh:

# ¿ Jan 12, 2022 02:29

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

I've got a Power Query question! I'm using it to import a list of receipts with dates attached so that I can create PivotTables from it. But in order for summarizing by date and cross-referencing with other tables to work properly, I need to fill in all the dates that are missing (i.e. even if there aren't any receipts for the last week of January I still need all those days in my PivotTable). My current solution is that after I update my query, I scroll to the bottom of the table and type the first day of the month and drag it down until I have every day in that column (the rest of the columns can be blank, it's fine).

Is there an easy way to make Power Query fill in those missing dates on its own? It feels like the hard part would be getting it to recognize that I specifically want to fill out the month, and not just fill in the dates between the ones I already have.

# ¿ Feb 10, 2022 16:55

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

Let's say that in A1 I have "February 14 9:00:00" and in B1 I have "February 15 9:00:00". Then in C1 it just has "February 15" with no time attached. Is there an easy way to determine how many of the hours between A1 and B1 are in the date given in C1? The end goal would be a formula that in this case would spit out '9.00'.

I feel like the answer will be "I can do it, but it will involve a lot of suffering and helper columns and nested formulas", but hope springs eternal.

# ¿ Feb 28, 2022 19:10

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

Oh that's way simpler than what I was thinking, I might be able to make that work in conjunction with a bizarre series of IF statements. That's exactly the nudge I needed, I've been staring at it so long I've overcomplicated everything in my head.

I'm trying to do something clever with timesheets for a department whose current timesheets are done in excel and are. An absolute nightmare. Absolutely everything gets calc'd out manually and usually wrong. I briefly thought I had managed to automate all of the obtuse rules around pay calculations before I remembered that the 24 hour shifts don't actually line up to midnight-midnight which is how additional holiday pay is determined... :argh:

So if someone has a 24 hour shift from 9-9 on the 20th I need to correctly allocate 9 of those hours to holiday, but if they work 9-9 on the 21st I need to make sure that 9 of those hours aren't included. Which I think I can do with simple subtraction like you have, I just have to determine what happens based on whether the date the shift starts or ends corresponds to one of the dates in my table of holidays.

# ¿ Feb 28, 2022 20:57

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

DRINK ME posted:

Just chiming in - the reason that works is Excel dates all include a time component, even if it�s not visible. So where you have 15th Feb 2022 Excel actually sees that as 00:00 15/02/2022 (or 02/15/2022 if you use American format). Then under the covers Excel stores it as 44607, which is the number of days since 01/01/1900. With the time component it is 44607.00 - the .00 represents the fraction of the day, or 24 hours.

Yeah, that's why I'm now running into the problem with matching to my list of holidays where Excel goes, "Oh, the shift ends at February 21st at 9:00 am? That's not February 21st at midnight! No holidays detected."

Somehow this was not the part that I thought would give me problems.

# ¿ Mar 3, 2022 16:05

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

Can someone explain to me how this is supposed to work like I'm five? I've got an excel file that's moving to OneDrive/Sharepoint and all I want is an easy replacement for ThisWorkbook.path in my VBA scripts that won't give me a URL instead of a local filepath. This seems like it should be easy-peasy (just import and go) which is making me feel real goddamn stupid about it.

# ¿ Mar 18, 2024 18:54

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

They also exist on my local file system, and when I have a script set to save and then open another file, I would like for it to do that the way it normally would for local files instead of opening a browser window and asking me to log in to access the web version of the text file I just generated. :argh:

# ¿ Mar 18, 2024 19:37

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

I've got a base sheet full of tables with names like Water, Sewer, Power, Gas, etc. I'm working on a macro that will copy that sheet, rename it to the month and year, then rename every table to WaterApr, SewerApr, PowerApr, etc etc etc. I've mostly got it, except that when the sheet gets copied it renames all the tables to Water11 Sewer_32 or whatever, so I end up with Water11Apr (not useful). In theory I could work around this by stripping everything except alphanumeric characters, but I don't know the easiest way to do that.

# ¿ Apr 9, 2024 15:58

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

You probably don't need the whole thing and I hate posting it because I'm lazy and terrible at code, but:

code:

Sub newMonth()

    Dim wbYear
    Dim wbMonth As String
    Dim shortMonth As String
    Dim shortYear As String
    Dim tbl As ListObject
    
    wbYear = Application.InputBox("Enter the year of the workbook.")
    wbMonth = Application.InputBox("Enter the month of the workbook.")
    shortMonth = Left(wbMonth, 3)
    shortYear = Right(wbYear, 2)
    
    Sheets("Base").Copy Before:=Sheets(Sheets.Count - 1)
    Sheets("Base (2)").Select
    Sheets("Base (2)").Name = LCase(shortMonth) & "." & shortYear
    Range("J1").FormulaR1C1 = wbYear
    Range("J2").FormulaR1C1 = wbMonth
    
    
    For Each tbl In ActiveSheet.ListObjects
        tbl.Name = tbl.Name & shortMonth
    Next tbl

    'this bit's messed up but i'll fix it later
    'Range("I57").Select
    'ActiveWorkbook.Names.Add Name:="aprWages", RefersToR1C1:="=apr.24!R57C9"

End Sub

# ¿ Apr 10, 2024 00:41

Adbot: ADBOT LOVES YOU

# ¿ May 3, 2024 10:18

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

Ninja.Bob posted:

It's probably easiest to use a regular expression replace. You'll need to add a reference to 'Microsoft VBScript Regular Expressions 5.5' in the vba editor menu Tools > References.

That worked! I only modified the expression a little:

code:

reg.Pattern = "[^a-z]+"

Unfortunately the tables sometimes end up moving around if they change in size so cell references wouldn't have worked, this seems to work perfect though. :3:

This thread is such a lifesaver.

# ¿ Apr 10, 2024 23:12

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