User Profile
RandomPanda
Brass Contributor
Joined Jul 12, 2021
User Widgets
Recent Discussions
Re: I need a formula or pivot table or something to pull info based on several factors
Thank you, Rodrigo. In looking at this, it doesn't appear to be dynamic...meaning, I need to be able to add data each day and have the output update. Additionally, I have several other pivot tables on separate sheets (for different supervisors) that use the same employee data. Since it is timesheet data (pulled from our punch clocks system) two things gum up the issues, here. First, missing punches (In/out/lunch, etc.) are fixed by the supervisor during the week. So, each day, I am pulling this data for Monday through today to catch anything that was missing previously. Second, obviously, the data is always the same format when I download. Right now, I just paste special over the data and my table updates all the pivot tables. The four rightmost columns are pulling from another table of a kind of "legend" for what the hours are and how they're paid. If I add columns or concatenate columns, a lot of other data could be corrupted...well, invalid. So, I'd like to put all this on a separate worksheet, referencing the other sheets. Could this just be a giant If/then, v/xlookup formula? I don't see it being that "easy".I need a formula or pivot table or something to pull info based on several factors
Ok, I thought I could just do this via vlookup, but I'm having trouble figuring out all the logic and syntax to it. Can I create a pivot table based on multiple tables? Automatic Data Analysis (that's something I haven't looked into, yet, and don't know much about)? Here's what I have: Two tables - 1. Employee data in columns A:R (showing alpha column and column reference for vlookup) Date in col A (column ref 1) Name in col C (column ref 3) Emp ID in col D (column ref 4) Cost Center in col F (column ref 6) Work Center in col G (column ref 7) - may be blank or no matching value found Hours in col M (column ref 13) Hours type (Regular Hours, Overtime, etc.) in col O (column ref 15) 2. Work center and gang times (standard hours for day) Row 7 column C through column H has dates of the week (i.e. 8/5/24, 8/6/24, etc.) starting in Row 8 through row 50 column A has a Work center columns C through H has the daily gang time for that work center What I need to compare and data I need at the end: Basically, I need to check if an employee has more regular hours than what the gang time shows. I'll need to return the Date, Employee Name, Emp ID, Cost Center, Work Center and regular hours worked plus the static gang time for that work center for the day in order to visually show the variance. So, output should just be a table of those employees whose timesheet has more hours than the standard for that day. no blank lines - not wanting to just look at the same row each time. Here's an example of the output I've attached a sample doc for your visualization of the input data. Thank you, in advance, for any help you can provide.Re: Worksheets("xx").Activate - breaks macro
Found this that worked on that thread. THANK YOU! -------------------------------------- CO_Data_Wrangler replied to RBinNC Feb 15 2024 10:58 AM RBinNC I encountered the same issue this morning. Try this: open the VBA Developer window for the workbook and go to: Tools --> References and be sure the "AccessibilityCplAdmin 1.0 Type Library" is checked. For whatever reason, that seemed to clear up the error for me. Hope it works for y'all, too. -----------------------Re: Worksheets("xx").Activate - breaks macro
NikolinoDE Thank you for the suggestions. I ended up having to copy the data to a file I saved the previous day (Friday) and everything worked ok. I saved over the file that had the issues. But, I opened up the other file that was corrupted (somehow) and tried a couple of your suggestions. First, I made sure macros were enabled. That didn't change anything. Second, I inspected the workbook. It showed "macros or vba code found". But, again, I pulled up "Show macros" and nothing was there. I tried editing in VBA editor and found nothing as well. Lastly, I just tried to save/close and I get this error: "Errors were detected while saving "<filename>". Microsoft Excel may be able to save the file by removing or repairing some features. To make repairs in a new file, click Continue. To cancel saving the file, click Cancel" When I click continue, I try to save as a different filename - that doesn't work. Then it doesn't want to close (clicking the "x"). When I finally can close it (I couldn't repeat the error today) it says something like - "a version was saved to Apps/Roaming/Excel....(generic default folder)." But, even that file doesn't include any of the changes I made (data added during the session) and has removed pretty much all formulas. I guess, I was able to get around the issues by recreating the files from an earlier version. But, I would love to be able to figure out what caused it to crash like that - so I can avoid it in the future. And, I didn't click on any random links from a Nigerian prince, so I'm pretty sure that's not it. 🙂 The only saving grace is that I have a template for this other file since it is a weekly file. I created the new week file from the template without issues.Worksheets("xx").Activate - breaks macro
I have a file with a well-established macro that I wrote years ago. It's a file I reset (with macro button) and then SAVE AS for the new date. I use it daily. For some reason, today the macro throws a run-time error - Automation error, invalid forward reference, or reference to uncompiled type. When I click on debug, it's highlighting [ Worksheets("Tally_Summary").Activate ]. I never change the sheet names, so it's not that. It's not already on that sheet, so it's not that. I've tried closing the file, restarting my computer, opening an older version (previous date) and nothing seems to work. Quick update: just noticed something that happened yesterday as well. Even though it is a macro-enabled workbook, clicking on the macro dialog box shows no available macros. So, THAT is probably the main issue. I have another file that did this yesterday and I couldn't save the file at all.Two file questions: SAVE and Link
I have two odd things happening that I'm trying to figure out. They are really just annoyances, but I'm a perfectionist and would like to clear these up. 1. A file I'm opening shows [Linking: "Prod0629.xls"] in the bottom as Excel is trying to open. But, I can't figure out where Prod 0629 is being referenced in the file. Is there a way to see that so I can remove it? Working from home (over VPN) takes forever to open the file since it's linked to an external file (plus all the other formulas and valid linkages that are in the file). 2. Another file I have is constantly asking me to Save when I close it, even though I haven't changed anything. It's the only file doing this that I use on a daily basis. It is a Macro Enabled Worksheet (just a reset) and I copy/paste special (123) some info in there. I just can't figure out why, after 3 seconds of going to another file and coming back to it to close, it asks if I want to save. It does not have any linked fields to outside data. Thanks for the help.Pivot Table Default Filter
Found an old thread with https://support.office.com/en-us/article/Set-PivotTable-default-layout-options-efd8569c-f07a-43c1-9db2-4f2912a0f94e showing setting defaults for Pivot Tables, but after a short time reviewing it looks to be more layout defaults rather than filter defaults. So, this is probably not an option - logically, absent data can't be filtered. But, is there a way to set filters to a specific value that I know will be in the data once I paste it into the table? For example - a labor file is blank until I download and manipulate the data (clearing out blanks and adjusting any "N/A" fields). Once in the correct format and free of invalid data, I copy/paste into the pivot tables data set. I know that "Regular Worked Hours" will be in the data set and I have several pivot tables for different departments. Is there a way to set the default for each of these tables to only include Regular Worked Hours so all I need to do is refresh once the data is copied over to the table?
