+ Reply to Thread
Results 1 to 11 of 11

Use formula in Blank Cell NA and nothing should be there if No value exist in Reference Ce

  1. 02-13-2019, 03:29 AM #1
    sg2209
    sg2209 is offline
    Registered User
    Join Date
    01-07-2018
    Location
    INDIA
    MS-Off Ver
    2010
    Posts
    37

    Use formula in Blank Cell NA and nothing should be there if No value exist in Reference Ce

    Hi Friends,

    i am using the below formula where i need to drag down this to end of the column , when i drag down this is hows NA as there is not value in Column C when correct value pops in C Column than only the result will show in a cell.

    Requirements :
    When i drag this down it should should blank if there is not Value in Column C.

    Formula

    =CONCATENATE(IF(C5="Already Billed To Secondary","None",IF(C5="Already Flipped","None",IF(C5="Already Worked","None",IF(C5="Amount Mismatch","GP As Per Eob The Patient Responsibility Not Matching With Erp-Hence Not Billing To Patient",IF(C5="Billed Patient","GP Flipped To Patient As This Is Patient Deductible",IF(C5="Billed Secondary","GP Billed Secondary Via Zirmed",IF(C5="Cannot Locate Eob","GP Eob Not Fnd On Zirmed & Healthlogic",IF(C5="Code Not Billed","GP Code Npot Billed",IF(C5="Credit Balance","None",IF(C5="Denial/Partial Denial","GP As Per Eob There Is No Patient Responsibility So No Need To Filp To Patient",IF(C5="Eob Not Found","GP EOB Not Fnd On Zirmed & Healthlogic",IF(C5="Fee Issue","GP Insurance Is Not Paying As Per ERP",IF(C5="Incorrect Fee","GP Insurance Is Not Paying As Per Erp ",IF(C5="Medicaid Secondary","GP As Per Eob Secondary Ins Is Medicaid",IF(C5="Medicare Crossover","GP Crossover Forwarded To",IF(C5="Need To Bill Via Hardcopy","GP Need To Bill Secondary Via Hardcopy",IF(C5="Payment Not Posted","GP Payment Not Posted Yet",IF(C5="Rejected By Zirmed","GP Billed Secondary But Rej By Zirmed",IF(C5="Zero Balance","None","NA")))))))))))))))))))," "," ",G5)


    how do i add If(C2=""," " before the formula, Kindly help
    Reply With QuoteRegister To Reply
  2. 02-13-2019, 03:38 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Use formula in Blank Cell NA and nothing should be there if No value exist in Referenc

    Shouldn't you use a VLOOKUP coupled to a small table instead of such a lengthy formula, prone to errors?
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  3. 02-13-2019, 03:39 AM #3
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,737

    Re: Use formula in Blank Cell NA and nothing should be there if No value exist in Referenc

    =IF(C2="","",
    CONCATENATE(IF(C5="Already Billed To Secondary","None",IF(C5="Already Flipped","None",IF(C5="Already Worked","None",IF(C5="Amount Mismatch","GP As Per Eob The Patient Responsibility Not Matching With Erp-Hence Not Billing To Patient",IF(C5="Billed Patient","GP Flipped To Patient As This Is Patient Deductible",IF(C5="Billed Secondary","GP Billed Secondary Via Zirmed",IF(C5="Cannot Locate Eob","GP Eob Not Fnd On Zirmed & Healthlogic",IF(C5="Code Not Billed","GP Code Npot Billed",IF(C5="Credit Balance","None",IF(C5="Denial/Partial Denial","GP As Per Eob There Is No Patient Responsibility So No Need To Filp To Patient",IF(C5="Eob Not Found","GP EOB Not Fnd On Zirmed & Healthlogic",IF(C5="Fee Issue","GP Insurance Is Not Paying As Per ERP",IF(C5="Incorrect Fee","GP Insurance Is Not Paying As Per Erp ",IF(C5="Medicaid Secondary","GP As Per Eob Secondary Ins Is Medicaid",IF(C5="Medicare Crossover","GP Crossover Forwarded To",IF(C5="Need To Bill Via Hardcopy","GP Need To Bill Secondary Via Hardcopy",IF(C5="Payment Not Posted","GP Payment Not Posted Yet",IF(C5="Rejected By Zirmed","GP Billed Secondary But Rej By Zirmed",IF(C5="Zero Balance","None","NA")))))))))))))))))))," "," ",G5))
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  4. 02-13-2019, 06:44 AM #4
    sg2209
    sg2209 is offline
    Registered User
    Join Date
    01-07-2018
    Location
    INDIA
    MS-Off Ver
    2010
    Posts
    37

    Re: Use formula in Blank Cell NA and nothing should be there if No value exist in Referenc

    Thank You


    Also Can you please solve if need to put the Value of C2 in D2 i usually use =C2 however of C2 is blank then it shows No, i tried =if(C2=""," ",C2), not getting results.
    Last edited by sg2209; 02-13-2019 at 06:51 AM. Reason: Query Stuck
    Reply With QuoteRegister To Reply
  5. 02-13-2019, 03:37 PM #5
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,737

    Re: Use formula in Blank Cell NA and nothing should be there if No value exist in Referenc

    dont understand the question
    Reply With QuoteRegister To Reply
  6. 02-13-2019, 10:49 PM #6
    sg2209
    sg2209 is offline
    Registered User
    Join Date
    01-07-2018
    Location
    INDIA
    MS-Off Ver
    2010
    Posts
    37

    Re: Use formula in Blank Cell NA and nothing should be there if No value exist in Referenc

    I want to drag tha same value in column D to be placed automatically from column C .

    And that should be dragged i tried putting =C2 in cell D2 it shows 0 because there was no value in C2 as i enter Sachin in c2 D2 has the Name Sachin
    Reply With QuoteRegister To Reply
  7. 02-14-2019, 06:28 AM #7
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,737

    Re: Use formula in Blank Cell NA and nothing should be there if No value exist in Referenc

    in D2 put
    =C2
    Reply With QuoteRegister To Reply
  8. 02-14-2019, 08:34 AM #8
    sg2209
    sg2209 is offline
    Registered User
    Join Date
    01-07-2018
    Location
    INDIA
    MS-Off Ver
    2010
    Posts
    37

    Re: Use formula in Blank Cell NA and nothing should be there if No value exist in Referenc

    But if there is no value in C2 then it shows 0 .. should be blank
    Reply With QuoteRegister To Reply
  9. 02-14-2019, 08:58 AM #9
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,737

    Re: Use formula in Blank Cell NA and nothing should be there if No value exist in Referenc

    =if(c2="", "", D2)
    Reply With QuoteRegister To Reply
  10. 02-15-2019, 01:58 AM #10
    sg2209
    sg2209 is offline
    Registered User
    Join Date
    01-07-2018
    Location
    INDIA
    MS-Off Ver
    2010
    Posts
    37

    Re: Use formula in Blank Cell NA and nothing should be there if No value exist in Referenc

    Thank You etaf.

    Have a Great Day
    Reply With QuoteRegister To Reply
  11. 02-15-2019, 05:04 AM #11
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,737

    Re: Use formula in Blank Cell NA and nothing should be there if No value exist in Referenc

    you are welcome
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Reference cell formula returning 0 when BLANK, need BLANK to show
    By williamc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-31-2016, 04:23 PM
  2. If formula to return blank if reference cell blank
    By dhooper in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-10-2013, 08:56 PM
  3. Excel 2007 : Reference to cell that do not exist?
    By kenwood in forum Excel General
    Replies: 1
    Last Post: 08-08-2011, 04:18 PM
  4. Excel 2007 : in formula -return a blank cell if a reference cell is blank
    By osteolass in forum Excel General
    Replies: 5
    Last Post: 03-23-2011, 07:09 PM
  5. [SOLVED] reference blank cell in formula
    By Karen in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 AM
  6. reference blank cell in formula
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 05:05 AM
  7. reference blank cell in formula
    By Karen in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1