1. R


    XERROR allows for conveniently generating most of the Excel errors as output to functions With XERROR, it is very easy to generate all but four of the Excel errors as output to functions. Error types 0, 1, 2, 3, 4, 5, 6, 7, 8, 13, and 14 (i.e. #EXTERNAL!, #NULL!, #DIV/0!, #VALUE!, #REF...
  2. J

    IF(ISERROR((GETPIVOTDATA) won't show any value once pivot table is updated with new information

    I had a look through the forums but could not find a solution to this issue. I have a series of worksheets with data being entered and changed on a daily basis. That data is collated into a single sheet as a summary. Some of the information is not currently available in the data and therefore...
  3. L

    Macro for Formula with Reference Cell

    I’m creating a macro so I can click and drag down a formula X rows. The formula contains reference cells in another excel file that automatically increment. This is what I want. When I run the macro, the cells keep incrementing past what the original formula increments to. How do I repeat...
  4. S

    Using Dynamic File Path to Get Cell Data

    So I have been learning some VBA recently and noticed that I could save myself hours a day if I was able to figure out how to write this: I have the test sheet below before I implement this at work and would like to use the "Full Folder Path" and paste that into a closed excel file. I know...
  5. P

    Replace #REF! with 0

    I am using the following formula to get the quantities of the blue model if found in a pivot table(=+GETPIVOTDATA("Sales";'2019'!$A$3;"Model";"Blue"). When is not on the list I get "#REF!", how can I have "0" instead? Many thanks in advance.
  6. J

    Convert Sheet Events to Macro's

    Hi all, i have the below example of code that detects when BACK_COM is in cell T5 of Sheet1. When ba.placeBet is initiated, the program "Betting Assistant" will automatically call ba_betPlaced. What i am looking to do is rather than have the initial ba.placeBet triggered on Workseet_Change, i...
  7. R

    Fancy lookup formula!

    Hi, I need a formula that can look down entirety of Column B and identify all the blank cells that have a corresponding Ref number in column A. The answer I would be looking for is in column D. Apologies for not attaching any files (company restriction prevents me form doing so). If you can...
  8. R


    Hi Guys I have the following userform in the here under link I need to do the following :- when the user enters Name in the text box same shall replicate in Name1 and Name3 3.Example if the user enters John Smith , John Smith shall...
  9. Y

    Find and replce takes 5-6 hours

    Like any software developer, when I make significant changes to my spreadsheet I increment the version (So Ref Stats V7 becomes Ref Stats V8. I then have to change the references in one sheet for a couple of different files. Each file takes up to 6 hours to replace the references. (There's about...
  10. C

    Concatenate Break Formula

    Hi, The below table represents what I am currently doing manually but would am trying to come up with a formula to do for me. Or, some combination of formulas to get to the result I need. I need to write the result in the HTML column Currently I'm using the concatenation formula in the...
  11. A

    Finding multiple results

    Hi All, I am having a real hard time trying to explain what am actually trying to do as result so I have tried to demo it below. I need to change this <colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody> Ref Result a123 1 b123 2 c123 3...
  12. Trevor3007

    find case in a certain cell

    hi, I use the following code Dim Changed As Range, Cell As Range Dim Clr As Long Set Changed = Intersect(Target, Range("m2:k300")) If Not Changed Is Nothing Then For Each Cell In Changed Select Case LCase(Cell.Value) Case "win10": Clr = 35 Case "no"...
  13. D

    Why am I getting an overflow error

    I am getting an overflow error message and I don't know why. Here is the code: Sub AddReference() Dim wb1 As Workbook, wb2 As Workbook Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet Dim f As Range, client As Variant, Ref As String, ref2 As Integer Set wb1 = ThisWorkbook Set...
  14. R

    duplicate detection formula or vba macro

    I have a table in the following manner (let's say they are positioned from columns A to D) <tbody> ID Name Ref # Location 407 INVTF 100900 Paris 789030 New York 541001 Madrid 800 KLUMM 103990 Hong Kong 102030 Toronto 100900 Paris 750 JSIUI 130990 Montreal 657899 Miami...
  15. A

    Problem incrementing GetPivotData formula

    Hello everyone I have a GETPIVOT Data formula in A8 =GETPIVOTDATA("[Measures].[Name]",Total!$A$1,"[ValuesTable].[Employee ID]","[ValuesTable].[Employee ID].&[A0107733814033]") But this doesn't increment because of the ID written so i've tried to modify it to...
  16. P

    Formula to Count Cells

    Hi can anyone help with a formula? On Sheet1 I have headers in cells "A1:D1" A1 = Ref B1 = A C1 = B D1 = C On Sheet2 I have Data with headers in 3 separate blocks with 7 columns each: "B1:H" "J1:P" "R1:X" "B1:H1" - headers = A "J1:P1" - headers = B "R1:X1" - headers = C On Sheet1 in the...
  17. T

    Macro to fill in dates from one sheet to another finding the account line?

    Hi Everyone, I have two sheets. 1 "Raw Data" and holds a list of sales 2 "Sales" and is my official Sales list. I need to update one column in Sales with the new data from "Raw Data" so heres what I need. In "Raw Data" column A has a Ref Number. (that's rows 2 to last row) In "Sales" the same...
  18. J


    Afternoon Everyone, I hope someone can assist with a small problem. I want to get a formula which searches 2 criteria and return a answer. It's like a Vlookup but with an additional criteria. I've tried consolidating the cells but someone told me that using INDEX and MATCH are a better was to...
  19. O

    REF error

    I have just updated to Office 16. I am trying to add a cell from worksheet and a cell from another worksheet. I use the formula; =A1(first worksheet) + a1(second worksheet) and enter. I am getting a REF error and a dialog box that look slike it needs to know a file location? Thanks, Mark
  20. E

    Code to replace formula

    Hi all, I have the following formula that searches to find the latest row of data Column C is the date Column F is the job ref Column G is the article ref IF(AND(MAX(($C$12:$C$10023*($F$12:$F$10023=F322)*($G$12:$G$10023=G322)))=C322,C322<>""),"Latest","") I have this on a long list and have...

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