function

  1. G

    How to find the 2nd largest value in a series of data that may contain multiple incidences of the same value?

    Hi, I am trying look for a function or a solution to finding the second highest value in the array of data that may contain multiple instance of same values. for example: array = [31,28,31,27,28,29,26]. The LARGE function couldn't help me because if I pass arguments like LARGE(array.2) it will...
  2. S

    Function returns #VALUE but works in immediate window

    I am trying to make a function where, based on an article code, the funciton looks in a different sheet with matching codes and takes the right discount (5 columns to the right of the cell with the code). Then I would like to return a specific value when the discount is set as "standaard"...
  3. G

    Is AutoComplete possible across sheets.

    I am using a workbook as an order form for inventory. I have a macro that copies all the Items that were entered on the order form to a Item Master Sheet. Another Macro then removes any duplicate items. When I fill out a new order form and start typing in the item, I want excel to...
  4. M

    Function Not Working - Sort & Remove Duplicates by Creating New Array

    I am not well versed in VBA and have no idea where to start finding the issue here. I need to write a function that will take a 1 dimensional array as an input and spit out a new array with all the values sorted alphabetically and duplicates removed. This input "SourceList" column has text...
  5. T

    IF statement = the second occurrence, then paste that corresponding row's second column

    Can someone help me find a shorter formula. I would like the corresponding row's first column to appear in successive columns in a second sheet. Each F number (ie F1, F2, etc) refers to a person. the cells to the right of each person refer to a job that they will be doing (ie Team 1, Team 2...
  6. S

    Function Checking if a network file is open - not working as expected?

    Running in Excel 2016 on Win10: I've been testing a workbook that will be used by several users to write data to a single workbook stored on a network. I am using a VBA function to checks that the network file is not already open before opening it, writing data in it, and saving and closing...
  7. Jyggalag

    How to create pivot table + slicer in Excel

    Hello everybody! It's nice to be back on the MrExcel forum :) I am currently managing a list that essentially looks like this (just a lot bigger): I would like to create a pivot table and a slicer for this (the real file is over 1000 cells) so it becomes much easier to look up specific...
  8. Jyggalag

    Excel vlookup problems

    Dear all, I am relatively new to Excel and have little experience with functions, especially across different excel files. I currently have two excel-files, where one looks like this (file 1): And the other one looks like this (file 2): Currently, what I would like is for file 1 to...
  9. R

    If cell contains specific text then push function to the cell to the right of it

    Is there a way to add another condition to the code below to read if the cell it's targeting for the function is not blank then do not push the function? Thanks! For Each cellAR In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) If InStr(cellAR.Text, "City, ST, Zip") > 0 Then...
  10. E

    Participants selected 35 out of 100 squares on 10x10 square grid - Need help calculating mean of all possible pairwise distances between 35 squares

    Hello everyone, I recently ran an experiment that required participants to select exactly 35 out of 100 squares on a 10x10 grid. I now need to calculate the mean of all possible pairwise distances between the 35 selected squares on the grid. I have attached images of a file that has two tabs...
  11. T

    How to assign a cell to a countif function

    Can someone help me please, I'm trying to get this COUNTIF function's criteria to be a cell (see image below) it works fine when I have it as =COUNTIF(P2100:P2205;"Nov 2020") but then every month I would have to change it manually. cell N2208(highlighted in green) updates automatically every...
  12. R

    Highlight just the Similar Text between 2 Cells

    Greetings All, I'm looking for a VBA solution or function to help in this scenario. In A2 I have some text. I want to know if some of that text is in a long text string within B2. And if so, HIGHLIGHT that text so it is easily identifiable. SOURCE FOUND GRANNY SMITH APPLE ORANGES...
  13. H

    Running VBA with "custom variable"

    Hi all, I am extremely new to VBA so I apologize if this is a stupid question. However, I have created this code sub (format) Sheets("T_maintained").Select ActiveCell.Offset(0, 2).Activate Dim B As Long For B = 1 To 36 Fill Next B Sheets("Mastersheet").Select ActiveCell.Offset(1, 0).Activate...
  14. I

    Normal Formula and Array Formula

    I have question is it possible to combine a normal formula and array formula because as of my knowledge one only requires enter and another ctrl shift and enter. How do I combine them both if it is possible? =IF(IFNA(VLOOKUP(M2,CHOOSE({1,2},IF('[Tier Price List.xlsx...
  15. H

    create function and apply it in datavalidation formula box

    hi everybody ,,, i have created a function named check_ punctuation like this : Function check_punctuation(my_str As String) As Boolean Dim asci_code Dim str_temp For i = 1 To Len(my_str) asci_code = Asc(Mid(my_str, i, 1)) Select Case asci_code Case 32 To 47 check_punctuation = True Case 123 To...
  16. D

    Function will not calculate as desired reliably.

    This function's behavior has me stumped. I want it to run and recalculate whenever a cell in rng changes. Sometimes it does, sometimes it doesn't. It depends on how I enter a new value in rng (copy/paste, keyboard, another UDF, etc.). The result is that the function isn't updating to the most...
  17. Q

    Compile Error for defining a function in VBA

    macroTest1 runs fine when the entire function is commented, but this error comes when I comment the function I am using the following references can someone help me with it?
  18. S

    How to average same cell across worksheets given a condition

    Hello - I'm trying to create a master Workbook that can take the average of the values of an individual cell across multiple workbooks, but given a certain condition is met. For example, I want to average all of the values of C3 (which is a number) across all worksheets, but only when cell A4...
  19. B

    Shifting data right in random data set

    morning, I have 3 rows of random data showing a number of product orders across 12 months. Each row shows a product line. However not all sales lines start in month 1. Therefore I need to shift the start month right by ’x’ number of months. X is again random. product 1 starts in month 1...
  20. N

    [VBA] why is my match function not looping through correctly?

    I'm trying to locate a row # with data I need to update based on two text boxes within a userform. Code is here: Private Sub cmdSendData_Click() Set wb = Workbooks.Open("\\TABLE.xlsx") Dim wsTgt As Worksheet: Set wsTgt = wb.Worksheets("Sheet1") Dim recRow As...

Watch MrExcel Video

This Week's Hot Topics

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 MrExcel.com.
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 "mrexcel.com".
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
Top