Forum Discussion
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'...
RandomPanda
First unpivot the Gang Times Data to have a single column for dates and gang times using Power Query and insert an additional column before gang times column "Date_WorkCenter"
and on Employee Data sheet, a helper column for "Date_WorkCenter", Gang Time, and Variance were also added,
on EE time > gang
I used filter functions to return multiple arrays (I used name range for the ranges of your employee data)
here's the sample data > employee time.xlsx
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".