custom function

Hi, This is just about general advice rather than a specific case, but when writing custom function, when do I need to use the variables first and then let and when within the the let statement; l let RunningTotal = (Avlaue, Aindex) => List.Sum( List.FirstN( Avlaue, Aindex ) )...

TLDR - Create a VBA custom function (OurDataFx) replicating INDEX MATCH MATCH sourcing to another workbook which can be used in any Excel spreadsheet and you only need to set up MATCH lookup_value parameters in this OurDataFx because INDEX array and MATCH lookup_array are defined directly via...

Hi, I did use match, index and lookup but now use the xlookup to collate relevant data from different tables for information when adding new data to the new new table. Ie copy contact information to help identify the client to add more data against that client. I have generated an equation...

I'm attempting to have PBI do some linear regression for me. From a helper query that iterates over multiple files, I'm calling this custom function to determine the highest reading above and lowest reading below the linear regression line over a variable length period for each row of data. It's...

Hello all, I have some code that works in a sub, but I need to use it several times over the course of my spreadsheets timeline. It currently only works on the active sheet, which I also need to change to be the sheet/variable that is called for in the argument when calling the function. This...

Hi all...I have a bunch of data in a table, and I am trying to write a custom function to call that data based on the function inputs. Here is an example of the data: <tbody> </tbody> <tbody> Motor HP RPM Alpha Beta Gamma EE 5 3600 .93 .03 .30 EE 7.5 3600 .98 .1 .2 EE 10 3600 .86 .25...

Could somebody help me create a custom function that returns the following: A*SUM(A1:AN)+B*SUM(B1:BN)+C*SUM(C1:CN)+....+M*SUM(M1:MN) The variables would be A, A1, and MN. Many thanks! A A1 A2 A3 ......AN B B1 B2 B3 ......BN C C1 C2 C3 ......CN ...

Hi there. I am using this to retieve HTML from a webpage Function GetHTML(url As String) As String With CreateObject("MSXML2.XMLHTTP") .Open "GET", url, False .Send GetHTML = .ResponseText End With End Function I have created another custom function...

A Lookup Function Across Spreadsheets How did this function come to be and what does it do? I was working on a project comparing the statistics of states in pivot tables. Most of the information I found used state names, but for pivot tables I wanted to use their abbreviations. I got tired...

My current custom function is: Function GetValue(ByVal str As String) As String With CreateObject("Scripting.Dictionary") .Add "A", "Apples" .Add "B", "Bananas" If .exists(str) Then GetValue = .Item(str) End With In this function, if I put "=GetValue(B1)" in A1 and B1 is A, then I get...

I'm hoping I can create a custom function to help me with a tricky Vlookup type problem: Let's say my vlookup should return the color of a fruit. I have a table on a hidden sheet that has each fruit with it's corresponding color. On another sheet, in cell A1 I have multiple fruit separated by...

Hi! I have write a custom function sista raden means last row: Public Function SISTARADEN() Dim a, h As Integer a = Worksheets("Stl").Cells(Rows.Count, "A").End(xlUp).Row - 1 h = Worksheets("Stl").Cells(Rows.Count, "H").End(xlUp).Row - 1 If a > h Then SISTARADEN = a...

Hello Guys, I have a custom function that I created to make naming batches of work much easier (it was base on a formula that I used in excel). The issue I'm having with it is that if any changes happen within the sheet that the function is it will work with no problem, but if I'm working on a...

As the subject says, I'm trying to create a custom function in VBA that will check the validity of a hyperlink. The purpose of this is to have a personnel tracking sheet with hyperlinks to supporting documentation (such as a scan of the employee's diploma) which, when the supporting...

I'm trying to create a UDF / custom function that takes the place of four vlookups to return a boolean response. It seems to work fine when I'm debugging it (I have a separate sub which calls the function so I can step through it), but when I attempt to use it, it does appear in the autocomplete...

I've written a simple formula (below, i don't have excel 2013 with the =formulatext function) on a tab in a standalone model which i then need to drop into a large number of other models. The formula is written on the worksheet in the standalone model (not a module) and it works in the...

I am having trouble searching a range containing workday dates for the closest one to the target. Ie. Jan 26, 2014 is on a Sunday, if this is the start date of a project I want the function to output Jan 24,2014. (This function is triggered by another sub, so I'm not really using it to output...

Hello all,<o:p></o:p> I've written a rather complicated custom function with nine input variables and would like to have the tip text show up as it normally would with a normal Excel function. When I say tip text I'm referring to the little grey box that appears below the cell when you start...

starting from scratch, I need to come up with a custom function to get cells with key words in a column and add up hours to get me the total hours for each day(column). here is how the sheet is set up. column a,b&c are frozen and contain equipment id and general info about the...

Hi everybody I need to be able to locate page breaks in a sheet automatically by returning a value in a cell if there is a page break above. E.g. if there is a page break between row 3 and 4, I need cell A4 to return a value like "Page break". I take it there must be a smart way to do this...