Announcing LAMBDA: Turn Excel formulas into custom functions
Today we are releasing to our Beta customers a new capability that will revolutionize how you build formulas in Excel. Excel formulas are the world’s most widely used programming language, yet one of the more basic principles in programming has been missing, and that is the ability to use the formula language to define your own re-usable functions.
Autocomplete not working for data validation
At work we have a shared excel sheet (we open it on the web) and we use it to store data. We had it set up with data validation list's and when we typed into the boxes, it would autocomplete one of the options. Now, you have to click the down box then start typing because autocomplete has stopped working. Autocomplete is still checked in options, and everything is still the same setting that it was previously as far as anyone can tell. Please help!create a kind of Gantt beam from a Working time sheet
Hello people need help too, Would like to create a kind of Gantt beam in this worksheet. Would like to enter the working hours (von=from, bis=to) in sheet "January", in three Duties ( Dienst1, Dienst2, Bereitschaft ) and in MA1 to 9 (colleagues) as well as the TeamChief, in the end it should be in the specified beam in sheet "IND1" should appear. The bar / timeline should appear (“IND1”) in the default color and row of the colleague I insert the working time in sheet “Januar”. Only the entered working hours should appear with color in sheet “IND1”, everything else without color in the row. In the end, the VBA code should be so that I simply copy it into the next sheet every month. A very nice person wrote me this VBA code (which I am also very grateful for) to accomplish my project. However, due to my VBA ignorance, I overexcited his good mood. I tried all the days to make it on my own, but without success. That’s why I contact the community. Any help in any kind is welcome and would like to thank you in advance for taking the time to read my request. If it could come to a solution, I would not only be happy, but also to do a somersault ... and that at my age :-))PLEASE VOTE!!! for Additional function for formatting cells. Adjust cell size to text length
Your vote counts!!! Adjust cell size to text length Additional function for formatting cells. https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/41191999-additional-function-for-formatting-cells Additional function for formatting cells. Adjust cell size to text length. At the moment there is in the menu of cell format, alignment, among other things the possibility to adjust the text size on the cell. In addition to this function, it would be very helpful if there was a box to click on where the cell would be adapted to the font size. If you like the idea, please click on the link and vote!VBA - Show data selection in pop-up menu
Hi Guys, have a "little" problem where I need a little help. I have a user form with a list field where I take data from a table from another worksheet. My problem is that it only shows the labeling area and not the data. VBA Code in Scheet1 Option Explicit Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean) Me.Unprotect ("1234") If Not Intersect(Target, Range("G5:EZ6")) Is Nothing Then Cancel = True ASK_Auswahl.Show End If Me.Protect ("1234") End Sub User Form in sheet 1 Sheet2 - table where the data is taken from Properties window in lists Box1 How can I get all data from Sheet2 to be displayed in the user form? Thx in Advance 🙂 Nikolinoincorrect formulation
Hello everyone, need a little food for thought ... or better...need your help 🙂 Examble (Cell L14) : =ODER(UND(L$9>=$C$3;L$9<=$D$3);UND(L$9>=$E$3;L$9<=$F$3); UND(L$9>=$G$3;L$9<=$H$3)) With this formula I can create a timeline as long as the working time is longer. If the working hours from 8:00 p.m. to 11:00 p.m. then everything is ok, shows how it should display. If the working hours are from 10pm to 3am then it shows empty, how can I correct this error in my formula? Nikolino I know I don't know anything (Socrates)
LAMBDA Examples: Distance between two cities
This post is the first of a series where we will be sharing out examples of lambdas. This is intended to highlight lambdas we have cooked up that show the power of this new super-charged function. Additionally, you have the opportunity to engage with us on the lambdas you have built yourself and of course let us know how our own formulas could be improved. If you didn’t catch the announcement, be sure to check out the blog post highlighting the release of this new function, Announcing LAMBDA: Turn Excel formulas into custom functions In today’s example we will be picking up where we left off in the announcement blog and making good on the promise of: “a custom function that takes two cities as input and calculates the distance between them...” So let’s get to it! Distance between two cities The first thing to note is that this function will be making use of Excel Data Types. Data Types are useful here because of the ease at which we can retrieve latitude and longitude for a given entity through “dot notation”. The next thing to cover is the mathematical formula which we will be encoding as an excel formula. We will be making use of the law of cosines which can give you an as the crow flies distance calculation. This formula is a great one to encode as a lambda given its complexity which will make it more prone to formula-authoring errors. It also happens to re-use multiple inputs which makes it a great candidate for LET. In short, this is the type of function I would want to author once and store for re-use. The equation looks like this: While this might seem a bit daunting, the inputs we really care about are: Latitude and longitude of the start location Latitude and longitude of the end location In the equation Δλ is the delta of the longitudes (_lon2 - _lon1) φ1 and φ2 represent _lon1 and _lon2 respectively r is equal to the radius of the sphere, in this case 6378 which is the radius of the earth in kilometers. The last trick we employ in this example is to convert everything into radians which is where we make use of the LET function to do these transformations. This is important as the equation expects lat/long in radians and the Data Types return them in degrees. This is illustrated in the first series of name definitions where we: Calculate the values in terms of radians for re-use Assign new names to differentiate between the input values (_lat1, _lon1, _lat2, _lon2) and transformed values (lat_1, lat_2, lon_1, lon_2) Putting all those concepts together gives us the following solution for LATLONGDISTANCE which will be doing the heavy-lifting for the final formula that takes in cities and passes in their respective latitudes and longitudes to LATLONGDISTANCE. =LATLONGDISTANCE =LAMBDA(_lat1, _lon1, _lat2, _lon2, LET( lat_1, RADIANS(_lat1), lon_1, RADIANS(_lon1), lat_2, RADIANS(_lat2), lon_2, RADIANS(_lon2), r, 6378, ACOS( (SIN(lat_1) * SIN(lat_2)) + (COS(lat_1) * COS(lat_2) * COS(lon_2-lon_1)) ) * r )) =ASTHECROWFLIES The last piece of the puzzle is to create a lambda which will take two cities as inputs. You could encode all of this into the previous formula, but we like the composability of lambdas and figured it would be a great way to show lambdas calling one another. Revisiting the previous comment, about extracting properties from data types, we will need to define something which takes two cities as inputs and then extracts the latitudes and longitudes. For this we will make use of dot notation and LET. =LAMBDA(city1, city2, LET( lat_1, city1.Latitude, lon_1, city1.Longitude, lat_2, city2.Latitude, lon_2, city2.Longitude, )) With the values extracted and names defined, the last thing to do is define a calculation which we call distance that returns the value. You’ll notice we wrap this in an IFERROR to catch any errors which might result from passing in bad values. =LAMBDA(city1, city2, LET( lat_1, city1.Latitude, lon_1, city1.Longitude, lat_2, city2.Latitude, lon_2, city2.Longitude, distance, LATLONGDISTANCE(lat_1, lon_1, lat_2, lon_2), IFERROR(distance, "an error occurred") )) And that's it! We hope you found this example useful and look forward to seeing what lambdas you have cooked up on your own. Until next time! Chris Gross, Program Manager Excel
