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".486Views0likes0CommentsI 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.639Views0likes2CommentsVBA to automate field updates
I am looking for some generic VBA code that will allow me to automatically adjust a couple fields based on others for a set number of lines. What I want, in laymen's terms is: 1. Pop-up to enter the number of "lots" (see screenshot) I want to adjust 2. For that many lots, adjust column F to add the amount in G (F is currently not a formula, so manually, I do an "<F2>, +1, <Home>, =, <tab>" - I want it to do this so I can see the new formula versus just adding the fields like in another VBA project I have). 3. For the same amount of lots, I need to adjust the weight in col P by adding the number in G * the average weight in col Q. Again, P is not a formula now, so I manually do "<F2>, +1*219, <Home>, =, <tab> From my limited experience writing these I assume it would look similar to this: X = entered number of lots (array) Diff = col G Avg = col Q For x lots, F = F+Diff For x lots, P = P+Diff*Avg The lot number, which could be utilized to define/compare X is in column D. So, we could use an "If col D is IN the entered lot array" - say I enter 1 as the starting and 9 as the ending lot in the pop-up - THEN ... run the formulas. This way, if I want to adjust, say lots 16-22 or only lot 40, the IF-statement will only look for and adjust those lots. So, the beginning and ending lots should be able to be the same number. And, the search for lot numbers should be D2-D60 with error handling for no entry (I generally have 35-50 lots, but could be more or less). Thank you in advanceSolved901Views0likes3CommentsRe: AVERAGEIFS
Ok, this worked in my original file. But now, corporate has made a bunch of changes to how my payroll file downloads and I needed to create a new file. There are a lot more criteria now. Here's the setup: 3 worksheets: 1. raw data, 2. formula(s) 3. Listing Raw data (wksht 1) contains: Date (col A) / Location (col B) / Work Center (col G) / Pay Code (col L) / Hours (col M) - plus a bunch of other columns, but these are the necessary ones Listing (wksht 3) contains: Work Center (col A) / Cost Center Description (col D) - Formula contains: <formulas start in row 7> VLookup to Work Center number ("Listing col A") - in Column A VLookup to Work Center description ("Listing col B") - in Column B nested IF/VLookup/IFERROR/AverageIfs - in column C (to be copied to columns D-G. These are days of the week) Days of the Week (Date) is in row 7 of column C-G Formula is trying to do several things: a. Based on "Listing / Cost Center Description" use a particular cell on current worksheet b. If "Listing / Cost Center Description" matches a particular description AND matches a particular Work Center number - then I need to get the Average of "regular worked hours, between 3 and 13 for the date (in row 7)" from the Raw Data worksheet. This is incorporating the "averageifs" formula that works like a charm from my previous post. Here is the formula I used - so far I haven't incorporated the part about matching the date because I was just trying to get the average to work. And I don't have all the options in there yet, so "N A F" is "not accounted for" - which means this is getting a LOT longer. But, one step at a time. Sample file attached =IF((VLOOKUP($A7,'WORK CENTERS'!$A$2:$D$72,4)="DES MOINES-ANIMAL FOOD"),0,IF(VLOOKUP($A7,'WORK CENTERS'!$A$2:$D$72,4)="DES MOINES-HARVEST",IF(VLOOKUP($A7,'WORK CENTERS'!$A$2:$D$72,1)="100009",IFERROR(ROUND(AVERAGEIFS('Stamp Download'!$M:$M,'Stamp Download'!$M:$M,"<13",'Stamp Download'!$M:$M,">3",'Stamp Download'!$L:$L,"Regular Worked Hours",'Stamp Download'!$E:$E,$A7),2),0),C$1),IF((VLOOKUP($A7,'WORK CENTERS'!$A$2:$D$72,4)="DES MOINES-CUT"),C$2,"N A F")))912Views0likes0CommentsRe: 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. -----------------------1.8KViews1like0CommentsRe: 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.1.7KViews0likes2CommentsWorksheets("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.2.4KViews0likes6CommentsRe: Pivot Table - 2 criteria but show only one
Thanks, Martin - This was pretty much the answer I was expecting. Even hiding the ID column, if I sort by Name first, it consolidates the duplicate Names. So, I'm back to where I started. For now, I'm waiting to hear from HR and those to whom I send the report if sending only employee ID's or both ID and Name (sorted by EID first) is a viable solution. It sounds like we may need to update the employee records to avoid duplicates in the first place, but I don't know how that will work (I don't maintain that system). Plus, we have over 50k employees company-wide and a lot of possible duplicates. So, that would be a lot of work...again, for someone else.2.9KViews0likes1CommentPivot Table - 2 criteria but show only one
I have a labor file with data source sorted by several criteria including Employee ID and then by Pay Code (Regular Hours, OT hours, PTO, etc.). I have a Pivot Table that pulls from that based on Labor Assignment first - A Shift, B Shift, A Shift 2nd Location, B Shift 2nd Location, ... And then pulls the Employee Name and total regular hours (Pivot Table has a manual filter for that Pay Code). My problem is I have two employees with the same name, but different Employee ID's (actually have a few of these "duplicates") who work under the same Labor Assignment. I want the table to sort them out by Employee ID, but only show the name in the results. for example: A Shift - Employee ID 1234 - John Smith 8 hours A Shift - Employee ID 2345 - John Smith 8.5 hours I want the table to output: A Shift John Smith 8 hours John Smith 8.5 hours Right now it is showing John Smith 16.5 hours or John Smith 1234 8 hours John Smith 2345 8.5 hours Is it possible to sort on a field but not show it in the table? Perhaps, do I need to do something with the source data as far as sorting or adding a column? Thanks in advance. p.s. - don't want / can't use Power Query due to constraints at work.Solved3.1KViews0likes3CommentsTwo 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.472Views0likes0CommentsFor Loop select range to clear contents
I have a FOR loop that is combining values with duplicate identifiers in a few different columns but I'm having trouble clearing contents of just the cells I want to get rid of. I don't want to delete the whole row, just clear the contents of specific cells in that row. I can't figure out the syntax to select Columns A-G for Row(r) - where "r" is the row the FOR loop is on. Can you help? For r = m - 1 To 2 Step -1 If Range("A" & r + 1).Value = Range("A" & r).Value Then Range("C" & r + 1).Value = Range("C" & r).Value + Range("C" & r + 1).Value Range("E" & r + 1).Value = Range("E" & r).Value + Range("E" & r + 1).Value Range("F" & r + 1).Value = Range("F" & r).Value + Range("F" & r + 1).Value Range("G" & r + 1).Value = Range("G" & r).Value + Range("G" & r + 1).Value Range("A"&r:"G"&r).ClearContents End IfSolved1.3KViews0likes2CommentsPivot 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?4.1KViews0likes3CommentsAVERAGEIFS
Hopefully this is easy, but I just can't wrap my head around how long this formula (how many nested formulas) is going to be. Have a table (not named, but could be if easier) on one sheet ("Gang Time") with column D blank and column E with a "sorting code". I need column D to look for the value in E on a different tab ("Total Labor"), column E, also (table it's looking in is A1:M5000). Then it needs to look at column K for "Regular Worked Hours", then in Column L and only return the value if it is greater than 3 and less than 13. The value returned needs to be added up for all matching sorting codes and return the average to 2 decimal places. So, if col E on "Gang Time" = 5, col D should average all "5's" in the table on "Total Labor" that are Regular Worked Hours between 3 and 13. Confusing enough? I'm assuming it's an IF(VLOOKUP,(IF(VLOOKUP,(IFVLOOKUP.......))))))))) Thanks in advance.Solved2.4KViews0likes4CommentsRe: AVERAGEIFS
Figured it out in case anyone else needs to know. I used "AVERAGEIFS" with several criteria and added rounding at the beginning to make my pivot table look better without having 47 decimal places. EXAMPLE: =IFERROR(ROUND(AVERAGEIFS('Total Labor'!L:L,'Total Labor'!L:L,"<13",'Total Labor'!L:L,">3",'Total Labor'!K:K,"Regular Worked Hours",'Total Labor'!E:E,E10),2),0) --added error handling so if a department didn't work on that day (Saturdays, mostly) my Pivot Tables wouldn't show #DIV/0 Where I am averaging all Regular Worked Hours between 3 and 13 (removing outliers) for each department number. Table of information is on sheet "Total Labor", Hours to average are in column L. Column K contains Pay Code (i.e. Overtime, Regular Worked Hours, Vacation, etc.) and the department number is in column E.2.3KViews0likes1Comment- 2.2KViews0likes2Comments
Recent Blog Articles
No content to show