Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Struggling to get multiple arguments to work in a formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2014
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Struggling to get multiple arguments to work in a formula

    Hi All,

    I have attached a link to the spreadsheet I am referring to:- https://www.dropbox.com/s/i5lvozes38...xcel.xlsm?dl=0

    If the part from A1 "XPGLIDE" matches F3 then I want N3 to populate the same as A3, but the rest of K3 through to Z3 to return Zero.
    Similar if A1 was changed to "25412AYERSROOF." and F4 matches the part "ROOF" then I want to populate X4 the same as A4 but the rest of K3 through to Z3 to return Zero.
    As you can see from the spreadsheet there are 16 different variations (see row 1) that could possibly be in column F and hence could be in A1.
    The formula in N3 is something I thought would work but unfortunately is not.
    If there is a match I then need the same cell to populate the difference between cells AF and AI.

    I hope this makes sense when you look at the spreadsheet.

    I have googled and tried different various ways but I am at a dead end.
    I hope one of you helpful people can help resolve this for me.

    Thanks in advance.

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,587
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Struggling to get multiple arguments to work in a formula

    Hi,

    A couple of remarks regarding consistency between cells A1, F3 and N1 ...

    A1 : 25412AYERSXPGLIDE
    F3 : XPGLIDE
    N1 : XPGLIDE

    In cell N3 ... you can test following formula :

    Code:
    =IF(AND($F3=N$1,ISNUMBER(FIND($F3,$A$1))),$A3,"")
    Hope this will help

  3. #3
    Board Regular
    Join Date
    Sep 2014
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Struggling to get multiple arguments to work in a formula

    Thanks James for your reply.

    Your solution returns a zero for all. What I am wanting is for it to also return the value from $A3 if $F3=N$1


    Quote Originally Posted by James006 View Post
    Hi,

    A couple of remarks regarding consistency between cells A1, F3 and N1 ...

    A1 : 25412AYERSXPGLIDE
    F3 : XPGLIDE
    N1 : XPGLIDE

    In cell N3 ... you can test following formula :

    Code:
    =IF(AND($F3=N$1,ISNUMBER(FIND($F3,$A$1))),$A3,"")
    Hope this will help

  4. #4
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Struggling to get multiple arguments to work in a formula


  5. #5
    Board Regular
    Join Date
    Sep 2014
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Struggling to get multiple arguments to work in a formula

    Jason,

    My apologises. I did think that with it being a different formula and therefore a different problem that it would not be a cross post.
    The cross post you refer to is athttps://www.excelforum.com/excel-for...a-formula.html




    Quote Originally Posted by jasonb75 View Post

  6. #6
    Board Regular
    Join Date
    Sep 2014
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Struggling to get multiple arguments to work in a formula

    Jason,
    Again apologises for this. My misunderstanding of a cross post obviously.
    I have included the link to excel forum post in my original post as suggested by yourself.


    Quote Originally Posted by jasonb75 View Post

  7. #7
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,587
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Struggling to get multiple arguments to work in a formula

    Hello,

    The proposed formula returns 6 ... in cell N3 ...

  8. #8
    Board Regular
    Join Date
    Sep 2014
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Struggling to get multiple arguments to work in a formula

    Hi James,

    I am using the following formula which seems to work fine :-
    =IF(AND($F3=SUBSTITUTE(N$1," ",""),$D3&$E3&$F3=$A$1),$A3,"")
    The problem I now have is that when $A$1 is blank it does not remain at 6.

  9. #9
    Board Regular
    Join Date
    Sep 2014
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Struggling to get multiple arguments to work in a formula

    Thanks all for your help so far.
    I now have a new problem.

    I’m hoping you can help with a drag down of formula’s.
    Row 3 works fine for what I need it to do. However when I drag the formula’s down and then insert the source data into row 4 column G through to column Z there becomes a problem and downwards.
    If in A1 I input 25412AYERSXPGLIDE.. and then a quantity into A2, row 3 updates as it should.
    Then if I input 25944ELLISXPVIEW. into A1 and a quantity into A2 row 4 won’t update as row 3 did. Also cells AK3 and AL3 go wrong.
    Any help on this will be appreciated.
    I have attached a link to the workbook.
    https://www.dropbox.com/s/d3ya2v7ls4...Scan.xlsm?dl=0

  10. #10
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,587
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Struggling to get multiple arguments to work in a formula

    Hi,

    Sorry but the file you have added a link to ... does not hold the formula your are referring to ...???

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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