Formula Question
Results 1 to 6 of 6

Thread: Formula Question
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula Question

    Hi Guys,

    Could someone explain how I would merge these two formulas below so that it looks in one range and returns ROUTE, then looks in another and returns COLLECTIONS, I'm slowly getting my head around formulas but bringing them together is what I struggle with.

    =IF($B8="","",IFERROR(IF(MATCH(RIGHT($B8,3),Mon!$E$5:$E$100,0),"ROUTE",),"."))
    =IF($B8="","",IFERROR(IF(MATCH(RIGHT($B8,3),Mon!$E$101:$E$150,0),"COLLECTIONS",),"."))

    Thanks

  2. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Formula Question

    What do you want the cell to output exactly, "ROUTE COLLECTIONS"? in one cell?
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Question

    It's difficult to say exactly how to do this without seeing how your data is set up, and without knowing more about what you want to do exactly.

    But perhaps something like this . . . .

    =IF($B8="","",IFERROR(IF(MATCH(RIGHT($B8,3),Mon!$E$5:$E$100,0),"ROUTE",),".")&IFERROR(IF(MATCH(RIGHT($B8,3),Mon!$E$101:$E$150,0),"COLLECTIONS",),"."))
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  4. #4
    New Member
    Join Date
    Dec 2017
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Question

    Quote Originally Posted by tyija1995 View Post
    What do you want the cell to output exactly, "ROUTE COLLECTIONS"? in one cell?
    Hi,

    No, I have one sheet which staff input to, this has vehicle registrations against route numbers which are input daily. I have another sheet on the same workbook which is locked down and it contains every list of vehicle registrations that we have on site and this formula looks it up from the working page and returns "ROUTE" next to any reg which has been used that day, I also have a section where we have collection routes so when a registration is input to the collection routes, I want it to return "COLLECTIONS" instead of "ROUTE" and vice versa which is why I require it to look in two ranges....but dont know how

  5. #5
    New Member
    Join Date
    Dec 2017
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Question

    Quote Originally Posted by Gerald Higgins View Post
    It's difficult to say exactly how to do this without seeing how your data is set up, and without knowing more about what you want to do exactly.

    But perhaps something like this . . . .

    =IF($B8="","",IFERROR(IF(MATCH(RIGHT($B8,3),Mon!$E$5:$E$100,0),"ROUTE",),".")&IFERROR(IF(MATCH(RIGHT($B8,3),Mon!$E$101:$E$150,0),"COLLECTIONS",),"."))
    Thanks for the reply Gerald, I've tested it but it returns ..

  6. #6
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Question

    Well I think that might depend on what your other inputs are.

    I was able to get it to sometimes return "ROUTE.COLLECTION" or something similar.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

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
  •