custom function

  1. D

    Power query custom functions

    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 ) )...
  2. S

    VBA Custom function to replicate INDEX MATCH MATCH with predefined fixed arrays

    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...
  3. R

    Help with converting xlookup equation into a custom function with one input.

    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...
  4. S

    Nesting This Function

    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...
  5. spencer_time

    turn sub into function with arguments

    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...
  6. R

    Store and Call Data in a Macro

    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...
  7. V

    Custom function needed

    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 ...
  8. A

    VBA getHTML as custom function - Avoid several HTML retrival

    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...
  9. A

    Looking for Feedback or Previous Experience on My Article on Scripting.Dictionary

    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...
  10. A

    Need Custom Function to Return Numeric Value Rather Than String Value

    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...
  11. Z

    VLOOKUP multiple results (Custom Function)

    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...
  12. P

    VBA Cutsom function breaks on updating another workbook

    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...
  13. K

    Custom Function question

    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...
  14. M

    Custom function for checking validity of a hyperlink

    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...
  15. A

    Custom function to replace several vlookups

    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...
  16. C

    User defined (macro) formula not Transferring when moving tab

    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...
  17. S

    Function to find closest date to target with Hlookup

    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...
  18. C

    Custom Function Tip Text?

    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...
  19. S

    need help with custom function

    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...
  20. E

    Return value in cell if there is a page break above

    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...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back