1. D

    Xlookup, If, etc formulas and the dreaded #Value! that comes with "" when pasting as values

    In xlookup you can define what your error response is ~~ =xlookup(a3,b:b,c:c,<whateveryouwantyourerrorresponsetobe>,option,option). Typically I use "" as my error response. However when you copy the cell and paste as value (the result being an error state equaling "") in another cell...
  2. A

    Check if combobox has assigned value

    Hello, I am using the value selected in a combobox as a sort of filter, as in If Range("A1")=ActiveSheet.OLEObjects("ComboBox1").Object.Value Then Range("B1") = Range("A1").value/ ActiveSheet.OLEObjects("ComboBox1").Object.Value However if ActiveSheet.OLEObjects("ComboBox1").Object.Value is...
  3. Z

    How to exclude value from Application.Min

    Hi, How should I change the 6th line in below code If I want the result D is 8 ? Sub nulltest() A = 4 B = 8 C = 20 If A < 10 Then A = Null End If D = Application.Min(A, B, C) End Sub
  4. gheyman

    Sumifs were the Criteria is > 0

    =SUMIFS(qry_OnTimeDelivery_MetricData[MONTH1_DUE],qry_OnTimeDelivery_MetricData[Category],$B5,qry_OnTimeDelivery_MetricData[TAT],">"&"0") This is not working. I want to sum if the value in [TAT] is greater than 0 (zero) Or I can use a formula that sums if [TAT] is not null I tried...
  5. gheyman

    ACCESS: Not Null Question

    I want to change this to Not Null from Is Null. Any help is appreciated TAT: IIf([PDIR Completed] Is Null,DateDiff("d",[PDIR Due Date],[PDIR Completed],"") Thanks for the help!
  6. E

    Finding the position of the Nth match in an array with multiple criteria

    Hello all :) I hope someone can help me please, I've tried searching lots of sites for an answer but so far I haven't been successful... In my worksheet, each row contains one of certain text values in columns CL to CV - either 'Transferred', 'Abandoned', 'Consult', 'Call Ended' or 'NULL', as...
  7. M

    Multiple column transpose query

    Hello - I'm trying to transpose multiple columns into two columns in access (second column being the title) access data currently looks like this Period01 Period02 Period03 120 200 500 110 300 600 200 400 700 I want it to...
  8. R

    Null values

    Hello, I do not have values in all cells of my spreadsheet and I have to add "0", to those cells with nothing on it between D to CU columns. Is there a way to do this with MACROS? I would really appreciate your help Many thanks
  9. Welsh Mark3

    Countifs - 2 part question

    This is a two part question - 1. I am familiar with the countifs function however I would like to find out if one of the criteria is able to count if cell is not null. So for example if I have two columns A & B my formula would look something like =COUNTIFS (A:A, "XYZ", B:B, "CELL VALUE NOT...
  10. J

    M Code - remove all columns where the last row is null for that column

    Hello everyone and thank you for the help. Is this possible? I am a doing an append query and if the last row has a null value, I want to remove that particular column that has the null value in the last row, there could be multiple columns with null. Thank you.
  11. S

    listbox clear selections

    I have tried every possible combination to resolve this issue and can not find an answer. Ia about to pull all my hair out. most of the code I have tried produces an error. the following code does NOT produce an error...but also does NOTHING. Sub Fix_Font_size()...
  12. danhendo888

    Unique list from table in Power query (don't show 'NULL')!AvjBsEPEq12ngSTtNlKRTZmnoBHr?e=k1ek3y I have created a Custom Column using List.Distinct on the Payment Date column. If I expand these values, the NULL values will also appear. How do I make it such that it only shows the dates when I expand the values in Power Query? That...
  13. M

    Matrix matching function

    I created an x by y non-symmetric matrix in which, for values 1-5, "O" denotes a "preference to match". A "match" occurs only if both values reciprocate the preference (1 and 2 are a "match" because both prefer the other, while 1 and 3 are NOT a match because the preference isn't reciprocated)...
  14. A

    pass variables to function to search sheet

    I am trying to pass xlvalues, xlwhole, xlbyrows but I am getting an error msg (byref) when I try to compile this. I have the values that I am trying to pass, come from a table that stores the values. My error is happening at this line: Set ResultRange = FindAll(iRange, kWord, p3, p4, p5, p6)...
  15. M

    Expression builder syntax, if not null then...

    Hello and thank you in advance if you can help, I have a column I created that works great: IIf([eBird_ALL_DATA Query2 Base Data Output.DAY-MONTH_VALUE]<[eBird_ALL_DATA Query2 Base Data Output.START_Date_VALUE] OR [eBird_ALL_DATA Query2 Base Data Output.DAY-MONTH_VALUE]>[eBird_ALL_DATA Query2...
  16. D

    sumproducts but only last 50 data points

    All, I have a data set were i do a sum product =SUMPRODUCT(--ISTEXT(F9:F15249))-COUNTBLANK(F9:F15249) the column has numbers all down it and sometimes it says null null is what i am looking for here thats what i am counting. the data set moves abd currently the last number or null value is at...
  17. D

    Date format Pasting from report not playing Nice

    Hi All I'm having a nightmare with a workbook, it has a whole bunch of amateur code, but it worked however! the report this workbook imported its results from has been changed and the Date formats are acting crazy, swapping the months and days. The issue being that the workbook uses an Index...
  18. JenniferMurphy

    Empty vs Null vs ""

    Is the null (0 length) string ("") the same as Empty or not? The Immediate window seems confused or ambivalent: ?"" = empty True ?isempty("") False xblank = "" ?xblank = empty True ?isempty(xblank) False It appears that Null is different than anything else: ?null=empty Null ?null=""...
  19. jase71ds

    Mixed Column of Text & Numbers - want to replace text with 'null', but keep numbers

    I have a mixed alpha/num column. I want to keep all numbers (which Power Query correctly identifies and places to the right of the cell). All of the text (again, correctly identified as text, and therefore left justified) I want to replace with 'null' NOTE. The text is extremely varied, so I...
  20. synergy16

    blanket "if null" statement

    good morning all. after a bunch of calculations i narrow down a single row based on user choice and populate textboxes with related data. some of the cells are blank tho. is there a way to, perhaps with a loop, to tell the program if any of those cell locations are null to put a "N/A" in the...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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