Forum Widgets
Latest Discussions
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
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/
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.
Field Parameter when used in filter pane not working for TOPN filters
Hi, I have created a visual as below: Now I want to filter this visual by bottom 10 worst performers Site Name by Variance parameter created. FYI the variance Field parameter table shown below: So I dragged that field parameter column into filter on this visual 'By value'. But its not showing top10 or bottom 10 instead it is showing all site name. Please advise! PFA file here Portfolio Performance - v2.15 (1) - Copy.pbix Thanks in advance Sir! SergeiBaklan
Use Form to update data
When the command button New Record is clicked the user enters a value into a Form which will then be entered into F7 (or the next available empty cell in F) When the user clicks OK E7 is then updated with Todays date Followed by Filldown formulas in I6 and J6 to the next row Followed by copy cell J2 and past in to K7 End I have included a sample sheet to help explain what I am wanting to do. All and any help will be greatly appreciated. Thanks Summary Form.xlsxIs 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.File for work. please help!
Hi all, i have a file that i use for work. Right now there are 2 different pages. a list of the items with their code and an info about their situation. For Example if they are out, lost, etc. a list of numbers that i send next to the date. if they come back they are yellow, if not they are red. i do all of this manually every day, but when i am not in the office no one does that because it's long, boring and probably very confusing. Ideally i need something that keeps track of what goes out and what comes back and the dates. i attached the two pages. any help, advice, would be highly appreciated!! Many thanks!
