Hi John,

No need to post the same question in different forums, just once is enough.

The logic in your question is difficult to understand.  Why for example would I check Order 13 when I select number 73 from Order 5?  Where's the logic that connects these?  It seems arbitrary.  

In order to create any formula/code to solve your problem, we need to understand the rules/logic that drives the process.

Please explain why:

• Any particular number is chosen e.g. 73
• One a number is chosen, what determines which row(s) to check for relationships.  Do you check 1 row or multiple?

Regards

Phil

Hi Phil,

I am so sorry for posting into a another group. I am new here and I didn't know. I posted in the new group because I thought I was on the wrong group before where I made the first post since I did not get any answer.

That being said, let me explain the problem to you.

From above, I have NO. 1, 2 ,3.

1. The relationship of NO. 1 is 46,74,89,58,4,3,8,0,10. You can find this information in the RELATIONSHIP SHEET of the attached file. So every NO. (from 1 to 90) shares a relationship with other number (Just as above).

2. Now, I have Number 1 in cell J8 of SALES BOOK sheet (Attached). I want to know if number one in cell J8 has any of its number relationships in row C16:G16 and I16:M16 (Yellow highlighted) 

So what the formula will do is that, it will compare all the numbers in row C16:G16 and I16:M16 (Yellow highlighted) of the SALES BOOK sheet with column A:J of the RELATIONSHIP sheet. Since I am looking for the number 1, it will detect number 1 in column A of the RELATIONSHIP sheet and look for all the numbers in cell A3:J3 and compare those numbers with C16:G16 and I16:M16 (Yellow highlighted) of the SALES BOOK sheet. The result cell (O8) will be true in this case because cell A3:J3 has 46 and cell C16:G16 and I16:M16 (Yellow highlighted) also has 46. 

With this formula, I will be able to look at any number and check the relationship in any cell of the SALES BOOK sheet.

To answer you other question,

• One a number is chosen, what determines which row(s) to check for relationships.  Do you check 1 row or multiple?

I can only check one number in one row of the SALES BOOK sheet and it will compare its number relationships in the RELATIONSHIP sheet. If any number is available, even if it is just one number detected, result should be TRUE and FALSE if no number relationship.

Please do let me know if you need more clarification

Thanks Phil.

John.

Hello,

Below is an example of a formula to check if any value in a range exists in another range. This is however static and of lttle use, but as you have not described the logic of why you want to check the relationships of number 1 and not number 29 or 51 etc, and there is no obvious logic to why you want check against row 16 and not against row 15 or 20, it is impossible to write a dynamic formula.

=SUM(COUNTIF(RELATIONSHIP!B3:J3;C16:M16))>0

Br,
Anders

Hello Anders,

Thanks for taking interest in my problem. I really appreciate.

The Logic behind the whole thing has you have asked is that, if you check cell J8 of the SALES BOOK sheet, I discovered that its number relationship (46) is on row 16. Meaning that, a number at Colum J has one or two of its relationship 9 rows down and the result formula will be at cell O8. Now, since I am using a table, if the formula works, the table will automatically check for number 4 at ell J9 and check also its number relationship in row 17  (which is 9 rows down also counting from cell J9). This is the logic behind it. I may decide to check for any cell number in any row and the next automatic result will take the same pattern. 

So, if I can be helped with the formula and it works, all i need do is change the row of the formula anytime I want to check other cell number in any row I so desire to check. This is the whole Idea.

Please let me know if you need more clarification. 

Once again, thanks so much for the effort you are putting in for me.

Regards,

John

Hello John,

Ok, if we make this parameter based then it's a lot easier. See attached file.

You just simply type the cell reference you want to check, and then the order number. The cell and row will be highlighted and the result will be displayed on the same row. Hope this works for you.

* Notice that the first total column is included in the LookupRange, if you dont want that, then the easiest solution is to move it to another location.

Br,
Anders

Hello Anders,

Thank you for your help. You got the whole concept correctly and the formula is working fine. Meanwhile, the formula should work in a way that cell O8 will be FALSE from the file you have attached instead of cell 013. This is because we are checking for 19 (K8). Meaning, all cells in column O will either have FALSE or TRUE. That is, since you have checked for cell K8 and Order No. 10 and result will be cell 08, the next automatic result since it is a table will be cell 09 which will check for 32 (cell K9 and order No. 11) and the result will also be false because number relationships of 32 are 32,77,27,58,11,8,34,67,89,23 of which none of those numbers appear in order number 11. This will continue for cell k10 against order number 12, and so on. 

This is how its going to be.

Once again, thanks so so much. I know you have other engagements but you took out time to assist. May God bless you abundantly.

If you need more clarification, please let me know.

Regards,

John

Hello John,

Just change the formula in cell O4 to =IF(ROW(INDIRECT($R$2))=ROW(),Result,""). Make sure it copies down to the other cells in the table column. As you probably know, the INDIRECT function is volatile, so it is better to set the indirect part of the formula as a defined name.

Br,
Anders

Hello Anders,

Thanks for the prompt response. Much appreciated. 

I have edited the formula as you have instructed but still, it is not working. Kindly find attached what I am talking about. 

By default, if enter for instance K8 into cell R2 and order number 10 into cell S2, if condition is true or false, the result will be displayed in cell O8 and automatically, the next cell K9 will search order number 11 and its result, either true or false will be displayed in cell O9 and so on till the last row which will terminate if the last look up row of the SALES BOOK sheet is blank. With this, I will be able to filter column O for all TRUE or FALSE.

This logic will work same way if for instance I entered G4 in cell R2 and I enter order No. 2. the result will be displayed in cell O4, and automatically, G5 will look up order No. 3, G6 will look up order no. 7 and so on.

NOTE: There is no need for there to be colors. I only used the color for presentation purpose.

Thanks once again.

Kindly let me know if you need more clarification.

Regards,

John

Hello John,

So you don't want a parameter based solution then.

As far as I understand what you want to do, you are most likely better off with a VBA solution. No matter how it is done, Excel only do what you tell it to. If you take the solution I have provided, tweak it a bit (probably more) you might find a working solution to what you want. Or hopefully there is someone else out here in this forum that is able to provide a ready-made solution.

All the best and if you do get a working solution, please share. We can all learn new methods and techniques.

Br,
Anders

Hello Anders,

Thanks for your response. I am really not good in programming and even some advance formula that can solve this.

I wish someone can help me out as I am really suffering at work because of this.

Thanks anyways for your assistance so far.

God bless. 

Please house, is there anybody that can help out here. I am seriously in need of your help.

Thanks

John,

Greeting to the house,

Please is there anyone that can assist with my project. I will be grateful.

Regards,

John

I don't think you're going to get an answer for this, John. It's the switching of the values from 73 to 37 that is not something Excel is designed to do. You'd have to split all numbers into two columns, one for each digit and then somehow compare them. I don't know how you'd do that with formulas.

I'm not really sure I followed the logic fully, but is this anything like what you wanted?

AND BOOOOOOOOOM!!!!! I HAVE THE ANSWER.

OH GOD Velouria, you are a GEM!!! 

This is amazing. This is the solution!!! you got it all complete and it is working perfectly well. Oh my God! I cannot contain my Joy right now. Thanks man so so much. I am grateful to you for this. God will bless you abundantly. 

WOW! and Yes, it is everything I wanted Velouria. Thanks.

I will go through it all over again and revert but I have checked so many cases and they are working fine.

Regards,

John