Forum Widgets
Latest Discussions
Count only Scheduled Employee's
I have a spread sheet of employee's and schedules. I need to be able to see how many are scheduled today, vs how many actually showed. Currently i'm using =COUNTIFS('Active Associates'!$G:$G,"1st",'Active Associates'!$E:$E,"Full TIme") However I have employee's who fall under a different employment term i.e Temp & Seasonal.I also have a weekend shift that overlaps twice a week with 1st shift on Monday & Friday Ideally what I want is to be able to count all the people who are scheduled for any giving day of the week based on the current date, count how many are absent and then calculate total percentage on site. Hopefully the attached file makes more sense.What do you think of thunks?
OK, so the most likely response by far is going to be "I don't". However, I tried one of Omid Motamedisedeh's regular challenges and found it a suitable problem for exploring some of the lesser known byways of modern Excel. The challenge is to pick out locally maximum values from a rolling range. What I did was to write a function that used MAP to select one cell at a time, using DROP to remove the initial cells and TAKE to return a range of 5 cells with the active cell in the middle. The direct route to solving the stated problem would be to calculate the maximum value within each range immediately, but I was interested in the more general problem of "could I return the array of ranges in a form that would support further analysis?" As shown, the following formula ROLLINGRANGEλ = LAMBDA(values, n, LET( rows, SEQUENCE(ROWS(values)), MAP(rows, LAMBDA(k, LET( m, QUOTIENT(n, 2), rng, TAKE(DROP(values, k - m - 1), MIN(k + m, n)), rng ) ) ) ) ); gives and array of ranges error, but simply by enclosing the 'rng' variable within a further LAMBDA ... LET( m, QUOTIENT(n, 2), rng, TAKE(DROP(values, k - m - 1), MIN(k + m, n)), LAMBDA(rng) ) will cause Excel to return an array of functions, each one of which would return a range if evaluated. In the attached workbook, a number of formulae are based upon this array of functions = ROLLINGRANGEλ(dataValues, 5) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, ROWS(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, ISREF(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, AVERAGE(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), dataValues, LAMBDA(ϑ,v, MAX(ϑ()))) = LET( rollingMax, MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, MAX(ϑ()))), FILTER(Data, rollingMax=dataValues) ) The first simply returns #CALC! errors on the worksheet which is the normal response to a Lambda function defined on the grid. The second formulas uses ROWS to show that the ranges are not all the same size, the third shows the returned objects to be range references and not simply arrays of numbers, the forth is a rolling average while the fifth is a rolling MAX. The final formula returns the solution to the problem, being a filtered list. The purpose of this post is to demonstrate that Excel, which started out as a 'simple' spreadsheet program, now contains a very different programming environment that shares a function library and uses the grid for input/output but, other than that, has very little in common with 'normal' spreadsheet practice! A related survey can be found at https://www.linkedin.com/feed/update/urn:li:activity:7285432559902068736/I need help with three difference searchable dropdown lists
I need help with three searchable dropdown lists on a team info worksheet. I want to make the work searchable with no repetition. The list goes on the worksheets. When a team is picked, the team will not show on the list also no blanke in the dropdown to Bye weeks worksheet Range("B6:B11,E6:E11,H6:H11,K6:K11,N6:N11,Q6:Q11,B14:B19,E14:E19,H14:H19,K14:K19,N14:N19,Q14:Q19,B22:B27,E22:E27,H22:H27,K22:K27,N22:N27,Q22:Q27") NFL playoffs worksheet AFC PLAYOFFS TEAMS SECTION Range("B6,B8,B10,B12,B14,B16,G8”) NFC PLAYOFFS TEAMS SECTION Range(“B20,B22,B24,B26,B28,B30,G22”) Thank You
VBA: Input Box vs User Form Capabilities
Seeking advice on which of the title items seems like the most appropriate option for the criteria below. Preface also to say lots of folks on here have been incredibly helpful with all the VBA questions. Also, i'm quickly realizing as much as i enjoy trying to learn this stuff, it would probably be a full time job to learn just what i want to do... it's amazing what all can be done with these tools - so big thanks to everyone who has contributed, including HansVogelaar especially! This template has team overview tab ("User List") which houses basic info required for workbook operation. And, as the users have varying levels of skill with excel, I'm seeing the best method to keep this functioning as intended is probably to force some actions in a given path, as opposed to allowing users to update as desired - as that allows inconsistencies and errors result. So my hope is to accomplish the following: On Opening sheet: Q1: Ask user if any new users to the team? Yes - request Employee ID, User Name, Start Date. On completion - More new users? Yes - repeat New User Info intake. No - move to next Question No - move to next Question Q2: Are any users leaving your team this month? Yes - Departing User Info Required: User ID, End Date. On completion: More users leaving? Yes - repeat Leaving User info intake. No - move to next Question All information received from "yes" answers should be added to the table as a new row "User List". Shot for reference here: If the user answers No to both Q1 and Q2, then the intake method should cease, and user can move to whatever task they need to accomplish next. Is this flow more easily accomplished or more well suited to a User Form or a series of Input Boxes? Started tryin to develop both, and realized I should ask the experts before I chase either too far. -Joe
Is there a formula / function for displaying the "Name" of a reference cell?
I am trying to see if there is an Excel function to display the "Name" of another cell. I looked through all of the options using the =CELL() function (there are many) but none seem to return the "Name" that I have given the cell. I have used reference names in several places within my spreadsheet to simplify formulas and make them more readable, but I would like to have a dictionary of these referenced inputs including what each one is named in a separate tab. Any pointers are appreciated.
Get Pivot Table Data returns zero
I have a consolidated spreadsheet that uses the getpivottable formula linked to another password protected file. however when the source document is not open the formulas return a zero. Is there a better way for the correct values to be shown when opening just the consolidated spreadsheet. Due to the sensitive nature of the original spreadsheet it is not possible for all users to have access to this file. Even when the consolidated sheet has been saved with the correct data showing, when re-opening the document the values change to zero.
