• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel formula help

Z

zohaib

Member
Hello all,

https://onedrive.live.com/redir?resid=5124B6B7FA7D175A!158&authkey=!AEwHE5Qph62FiCQ&ithint=folder,

Here is what I am trying to do. I am working with two books (Book1.xlsx and Book2.xlsx, see link above to download the books). In column H of Book2.xlsx I need to display the following text (no er charge, no er porc charge and biller to rebill) base on test below.

test 1 (1st logical test)
if the customer id in Book2.xlsx matches the customer id in Book1.xlsx then do test3.

test2 (what to do if no match found)
if there is no match found then display text "no er charge".

test3 (what to do if customer id matched)
if the test1 result is true than test to see if their is a matching cpt number also match. for example, when excel is looking for customer id #6 from Book2.xlsx matches with customer id in Book1.xlsx excel than checks to see if any of the values in E7, F7, G7, H7 match with values in D12. ***note the cpt cells excel compares has to be of the two matching customer id and not the others.*** for example, the customer id #6 is found in both Book1.xlsx and Book2.xlsx but the cpt in cells E7, F7, G7, H7 of Book2.xlsx do not match cpt D12 in Book1.xlsx than show text "No ER proc Charge" and if the customer id along with cpt code match than show text "Biller to rebill".

P.S. if there is no cpt number in Book1.xlsx than the related field in Book2.xlsx should show text "no er charge".


I want to thank everyone in advance for helping. Have a nice day :)
 
Hi Zohaib ,

I am not sure I have understood you , but see if this is OK.

Narayan
 

Attachments

  • Book2of1.xlsx
    12 KB · Views: 9
Hello Narayan,

You understood me correct. Do you mind explaining the formula to me? Also, when i open the file (Book1of2.xlsx) you send me it is looking for Book1of2.xlsx. I tried to change all the formula by changing 'I:\Personal\[Book1of2.xlsx]Sheet1' to '[Book1.xlsx]Sheet1!' but it does not work. You can you tell me what im doing wrong?

Thank you lots for your help.

Zohaib
 
Hi Zohaib ,

As far as I can see , I renamed the file Book1 to Book1of2 just to distinguish it from the various other Book1 files ! It should not make any difference , since in your case , we are only accessing Book1 and not modifying it in any way.

All you need to do is rename your Book1 , which you uploaded , to Book1of2. As such , if you change all references to Book1of2 to Book1 , and your Book1 is in the same folder whose path is already defined in the formula , it should work.

Once you confirm that the formula works , I will explain its working.

Narayan
 
Hello Narayan,

I changed my book name from book1 to Book1of2 and it works and also updates if I make a change in cpt in book1of2. But, when I try to change the book name back to book1 and update the formula to say book1 it stops working again. Once I change the book name back to book1 and update the formula Cells J2:J12 show "#VALUE!" and cells J13:J20 show "no er charge"

Thank you,

Zohaib Ali
 
Hi Zohaib ,

Let me check ; Excel is intelligent enough that this behaviour is unusual.

If your calculation mode is set to Automatic , any change should be recognized and the formulae should work correctly.

Did you change the workbook name when the other book was open or when both books were closed ?

Narayan
 
Hi Zohaib ,

Sorry , but when you change file names , you need to edit external links ; Excel does not do it on its own when you change file names while the book is closed.

Anyway , I am uploading both books ; download both of them , and see if you have any problem after opening Book2.

Narayan
 

Attachments

  • Book1.xlsx
    10 KB · Views: 5
  • Book2.xlsx
    12 KB · Views: 8
Hi Narayan,

Ok when I update the external link it starts working so after I change the name of book1of2 I have to update the external link. All I need now is help on understanding the formula. I would really appreciate it if you can tell me step by step process. Thank you very very much for helping. Have a wonderful day :)

Thank you,

Zohaib
 
Hi Zohaib ,

The formula , without the external path name is :

=IF(ISNA(MATCH(A2,'[Book1.xlsx]Sheet1'!$A$2:$A$22,0)),"no er charge",IF(SUMPRODUCT(--(IF($D2:$G2<>"",$D2:$G2,0)=IF('[Book1.xlsx]Sheet1'!$A$2:$A$22=A2,IF('[Book1.xlsx]Sheet1'!$D$2:$D$22<>"",'[Book1.xlsx]Sheet1'!$D$2:$D$22,999),999)))>0,"Biller to Rebill","No ER proc charge"))

The section in RED is checking for whether the customer ID in A2 has a match in the other workbook.

The section in BLUE is checking for whether the values in columns D , E , F and G have any match in the other workbook.

The steps involved in the logic are :

1. The SUMPRODUCT function is used so that an array of values is formed. This is required because we have a possible 4 values in columns D , E , F and G. In the other workbook , there is a possibility of any number of values in successive rows. So we need to form an array of values , where the customer ID is the same as the one in Book2.

The further problem is that we can have blanks in the 4 columns D , E , F and G.

When Excel compares two values for equality , there are two results possible TRUE and FALSE , TRUE being equal to 1 , and FALSE being equal to 0.

The left side of the check is :

(IF($D2:$G2<>"",$D2:$G2,0)

This will create a matrix of values , with 4 columns ( referring to the values in columns D , E , F and G ) , where blanks are replaced by zeroes.

The right side of the check is :

IF('[Book1.xlsx]Sheet1'!$A$2:$A$22=A2,IF('[Book1.xlsx]Sheet1'!$D$2:$D$22<>"",'[Book1.xlsx]Sheet1'!$D$2:$D$22,999),999)))

This will create an array ( a single column ) of values , where if the customer ID matches , and it is not a blank , the cpt value will be available , and where either the ID does not match or the cpt is blank , the value of 999 will be used. There is nothing so particular about this number 999 ; the only consideration is that since we used 0 in the left side check , we have to use a different number here. We cannot use 0 because then the 0 on the left side will match the 0 on the right side.

Narayan
 
Hi Narayan,

I started completely new books and copy paste the data only in those books and carefully typed your formula into the cell but i get the same error "#value!" in cells H2:H15 and "no er charge" in cells H16:H20. What am i doing wrong now?

Thank you,

Zohaib
 
btw everytime I go into to check the edit links menu the status say "unknown" and when i click "check status" it says ok than when i close the edit links menu and open it again it shows "unknown" again.
 
Hi Zohaib ,

The #VALUE! error is a sign that the formula has not been entered as an array formula ; after either copying and pasting or typing in the formula , you need to :

a. be in EDIT mode ; if you have copied and pasted , you need to press F2 to enter EDIT mode ; if you have typed the formula in , do not press ENTER. Ensure you are still in EDIT mode.

b. Press the combination CTRL SHIFT ENTER ; you need to keep the CTRL and SHIFT keys pressed together , and while holding down these 2 keys , press the ENTER key. Doing this enters the formula as an array formula.

Can you do this , and then confirm the result ?

As far as your second post is concerned , this is standard behaviour and does not signify any problems.

Narayan
 
Hi Narayan,

I have to say you are brilliant. It worked like a charm. Thank you so much for being patient with me and helping me understand the formula and help resolve the error I was getting. Thank you again and again. I am happy to see experts like you are volunteering your time to help others. God bless you and have a wonderful day.

Thank you,

Zohaib
 
You must log in or register to reply here.
Back
Top