1. T

    Convert a negative number from a a2-b2 calculation to N/A if the answer is a negative number in one formula.

    When calculating the difference between two dates and one date has not occurred you can get a negative number. How do I turn that negative into N/A?
  2. J

    I need to replace 0's with NA

    Good morning, I am looking to update a dynamic getpivotdata table so the results can be charted in a bar chart (a pivot table is used to summarise the getpivotdata table so only relevant cells are reflected in the chart). The issue is that the getpivotdata table includes REF errors that impact...
  3. K

    SUMIF where range and sum_range are in every nth column

    Hello! I would like to get result of 19 in B7 for sum up values where criteria is always banana (A7) but there are multiple ranges(A,E,I) and sum_ranges(C,G,K) which are in every nth column. A B C D E F G H I J K 1 apple n/a 1 cherry n/a 1 banana n/a 10 2 banana n/a...
  4. S

    Last row insert formulas

    I need some help. I get a few reports like this on a weekly basis the largest one only containing about 150 rows and 26 columns. What I would like to do is use a macro to add a few things to the bottom of each sheet. Each sheet is dynamic in its number of rows and columns. <tbody> 123465...
  5. M

    Remove empty rows from .txt file

    Hello all, I have created a VBA script that pulls data from one document, and exports that data into several different files. There is a blank row in my data (which I want to keep, row 17, i.e. the blank row in between the "Quad#" values and the PlateData value), but instead of showing up in...
  6. J

    IFANDSUM Questions

    I have 4 =IF(AND(SUM formulas that all work individually, but I would like to get them all to work in one cell. Can this be done? Here are the formulas: =IF(AND(SUM(D3:L3)>9,(SUM(D3:L3)<14),SUM(D3:E3)>0,SUM(F3:G3)>0),"$50","$0") =IF(AND(SUM(D3:L3)>14,SUM(D3:L3)>0,SUM(F3:G3)>0),"$75","$0")...
  7. D

    why giving me N/A?

    =IF(OR(G8=0,AB8>0),0,AB8) g8 is N/a....AB8 is 0.....shouldnt the if statement produce 0? it's giving me N/A
  8. B

    Vlookup formula for returning dates

    I am trying to use the vlookup formula to return dates, but it just comes up with an N/A error. I am using the formula: =VLOOKUP(A10,Master!$G:$M,3,FALSE). Am I doing this wrong??
  9. J

    Change Cell Button to apply to all selected cells

    I have had a request from management. I have the following macro assigned to a button Sub TaskNotDone () With ActiveCell .Value = "N/A" End With End Sub This changes the active cell to N/A is there a way I can have this work if there are multiple cells selected. The guys find...
  10. T

    Hidden column cells - data being added but formatting not applied

    Hi all, I have the following code which is supposed to add "N/A" to cells and colour them in green: If ch = vbOK Then Target.Cells.Interior.ColorIndex = 43 Range(Cells(Target.Row, twelvewl + 1), Cells(Target.Row, twelvewr)) = "N/A" Range(Cells(Target.Row...
  11. L

    match the second value if the result of first match is N/A

    what is the formula to match automatically the second value if the result of the first match is n/a or zero? thank you!
  12. schielrn

    Form for Checklist

    I am struggling figuring this out and thought it should be something simple. What I want to do is create a subform in a main form that is a checklist that needs to be marked as pass, fail or n/a. I have a table that based on the product they select will show the appropriate checklist items...
  13. K

    IFs, ANDs, and BUTs

    Need help finding a formula for the following conditions: If AV14="Yes" AND AV76 is a number, then enter the value of AV76, BUT if AV14="No" then enter "N/A". Any help would be appreciated. Thank you!
  14. I

    Blank entry in drop down list

    Morning, I have a userform which has many comboboxes with drop down lists. I have noticed that if a drop down list entry only shows N/A then below it is a blank entry also. Anything else where there are 2,3 or 20 options to select then the blank space at the end isnt shown. The sheet where my...
  15. R

    VBA for Auto Populating with Fixed Values & Shading

    Hello, Can you help me with a macro please? I need to auto populate certain cells in different rows with "N/A" and grey shading depending on selections made in column B from a dropdown list. For example, if we select "School" in cell B19, then E19 to P19 show "N/A" with grey fill colour and...
  16. A

    Macro to Stack rows in columns- ignore certain values

    Hello everyone! I am trying to write a macro which will stack data into multiple columns. For example, for this table: <tbody> mgn PPO Prem Eff 123 1 2 n/a 456 4 n/a 8 </tbody> I want the output in another sheet to be: <tbody> mgn metric value 123 ppo 1 123 prem 2 456 ppo 4 456...
  17. I

    Renaming files now obviously stops code working

    Hi, I have this code in use which when in my database & i press its button the corresponding invoice is then shown. At the time the invoice were saved like this, 1.pdf 2.pdf 3.pdf Private Sub OpenInvoice_Click() Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY...
  18. B

    Excel Formula - compare two set of cells and find return the result based on the logic

    <tbody> A B C D E (RESULT) 1 10 10 5 50 Y 2 20 20 1 1 N 3 10 10 N/A N/A N/A 4 N/A N/A 2 3 N/A </tbody> Need help with this as well. Logic : C>A OR D>B = Y else N if N/A present in A&B or C&D then result is N/A
  19. B

    Need help: compare two cells for highest number, if text in one of the cell then return N/A

    Need help with the following if B>A result Y, if A>B result N, if text in any of the cell then result is N/A <tbody> A B C (Result) 5 4 N 10 100 Y N/A 10 N/A zero 100 N/A </tbody> <tbody> </tbody> Thank you
  20. I

    Data Validation Circle Usage

    Morning, I have 2 columns as explained below. Column J consists of values like Z159, R235, P004, N/A Column L consists of values like JHMCN129357842S87, N/A The columns have a drop down where the only selectable option is N/A When i use the Data Validation Circle Invalid Data option i see a...

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