User Profile
RandomPanda
Brass Contributor
Joined 4 years ago
User Widgets
Recent Discussions
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.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 withhttps://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 fordifferent 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?
