Conditinal Formatting - Result of a 2-way search

[peterbata]

Hello and thank you for taking my inquiry. First time post. I am very new to Excel so please bear with me.

I have a column and row with 100 postal codes. I am trying out CDXZipStream to calculate the distance between each and every one of those postal codes (100X100). By using an Index / Match formula I am able to see the results of two unique postal codes that I specify. I am also able to yield the row and column where the result resides. I would like that intersection eg. row 50 column 85 to display in red for example so that the location would be much easier to spot at a glance. I was hoping to attach a small screen shot 10X10 rows/columns but am not able to do so on the forum.

 

[Eric W]

Welcome to the forum.

Here's a possibility:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
8
9		row	13
10		column	6
11				$D$11	$E$11	$F$11	$G$11	$H$11	$I$11	$J$11	$K$11	$L$11	$M$11
12				$D$12	$E$12	$F$12	$G$12	$H$12	$I$12	$J$12	$K$12	$L$12	$M$12
13				$D$13	$E$13	$F$13	$G$13	$H$13	$I$13	$J$13	$K$13	$L$13	$M$13
14				$D$14	$E$14	$F$14	$G$14	$H$14	$I$14	$J$14	$K$14	$L$14	$M$14
15				$D$15	$E$15	$F$15	$G$15	$H$15	$I$15	$J$15	$K$15	$L$15	$M$15
16				$D$16	$E$16	$F$16	$G$16	$H$16	$I$16	$J$16	$K$16	$L$16	$M$16
17				$D$17	$E$17	$F$17	$G$17	$H$17	$I$17	$J$17	$K$17	$L$17	$M$17
18				$D$18	$E$18	$F$18	$G$18	$H$18	$I$18	$J$18	$K$18	$L$18	$M$18
19				$D$19	$E$19	$F$19	$G$19	$H$19	$I$19	$J$19	$K$19	$L$19	$M$19
20				$D$20	$E$20	$F$20	$G$20	$H$20	$I$20	$J$20	$K$20	$L$20	$M$20
21
22
23

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet21​

Select your table. Click Conditional Formatting --> New Rule --> Use a formula, and enter this formula:

=AND(ROW()=$C$9,COLUMN()=$C$10)

where $C$9 and $C$10 represent the row and column of the cell you want to highlight. Click Format and select a fill color. Let me know how this works.

 

[peterbata]

Hello Eric. Thank you so much for the prompt reply. Perhaps I hadn't provided enough details. Or, I am not wrapping my head around the rule that you provided. Hopefully, you will be able to access this screenshot: http://i65.tinypic.com/if7t6x.jpg

Cells D14 & D15 are the two postal codes that I entered manually. Please remember it could be a combination of any two postal codes. In the case of postal codes H3H Canada and H4G Canada a distance of 6.51999972 is returned. That is what appears at cell K2. This is the cell that I would need formatted (fill color). Obviously, if I choose another postal code combination, the result will appear in another cell. Sincerely. Peter

 

[jtakw]

Hi,

I haven't recreated your table to test, but basically:

Select the entire range of your table, CF using formula, I believe you can do this 2 ways:

1. Use the formula in D17 for CF, or
2. Just use =$D$17

 

[Eric W]

I'm sorry if I wasn't clear. In your original post, you said that you were able to yield the row and column where the result resides. I assumed that those values were in C9 and C10 (which you can change to whatever you want), and then just created a formula using that. That's probably the simplest method.

I tried to view your screenshot, but it wouldn't come up. I came up with a mockup that I hope is closer to what you have, and a formula that does not require you to find the row and column first:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4
5
6
7			Distance
8	Code 1	90111	12					Code 1
9	Code 2	12122
10				12345	10101	45678	123	90111	44444	12121	12122	12123	55055
11			12345	1	2	3	4	5	6	7	8	9	10
12			10101	2	3	4	5	6	7	8	9	10	11
13			45678	3	4	5	6	7	8	9	10	11	12
14			123	4	5	6	7	8	9	10	11	12	13
15		Code 2	90111	5	6	7	8	9	10	11	12	13	14
16			44444	6	7	8	9	10	11	12	13	14	15
17			12121	7	8	9	10	11	12	13	14	15	16
18			12122	8	9	10	11	12	13	14	15	16	17
19			12123	9	10	11	12	13	14	15	16	17	18
20			55055	10	11	12	13	14	15	16	17	18	19
21

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet21​

Worksheet Formulas Cell Formula C8 =INDEX($D$11:$M$20,MATCH(B9,$C$11:$C$20,0),MATCH(B8,$D$10:$M$10,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Select your table and use this CF formula:

=AND(ROW()=MATCH($B$9,$C$11:$C$20,0)+ROW($C$11)-1,COLUMN()=MATCH($B$8,$D$10:$M$10,0)+COLUMN($D$10)-1)

updating the ranges to match your actual sheet. Let me know if this is closer to what you want. If not, it appears that jtakw has seen your screenshot and should be able to help.

I did add 2 additional CF rules for the yellow highlighting, I can tell you those formulas if you'd like too.

 

[peterbata]

Hello again Eric. I will give your formulas a go. If you cannot access the screenshot, perhaps accessing the file itself may be simpler.
https://mega.nz/#!owBVlC4T!XfDMWYywvRuiwk5A8hQlVtppka-SEpMP9WJyry5-oho

 

[peterbata]

Eric, your solution worked like a charm. Amazing. Thank you so much for sharing your time and knowledge with me. I appreciate it immensely. Now on to the next battle. Peter

 

[Eric W]

Happy to help!

 

[peterbata]

Thank you for offering to provide me with: "I did add 2 additional CF rules for the yellow highlighting, I can tell you those formulas if you'd like too." I would have gladly taken you up on your offer but I was determined to try figuring it out on my own. The best way to learn wouldn't you agree? This is what I did. I created two rules based on the builtin "format only cells that contain / format only cells with: Cell value equal to =$B$8 and another for =$B$9. My next challenge should be pretty interesting. The worksheet / project that I am working on and using as a learning tool is for a small courier company. I would like to use the calculated distances that I mentioned in my original post to prepare a multiple delivery service price list that can be issued to clients based on the clients location / postal code. So I was thinking: 1) A static distance sheet 100X100 postal codes 2) Then I would roundup the fractional distance 3) Use these distances with a multiplier table. eg. if the distance between postal code A and postal code B is equal to "X" or resides between a range "X-XX" then use multiplier $x.xx 4) Results are transferred to a master template / price list with columns for 8,4,2,1 hour service. 5) Print and send to customer.

My apologies for the long-winded explanation. Have a wonderful day. And again, thank you so much for your assistance. Peter

 

[Eric W]

I agree, if you figure it out for yourself, you learn more and remember better. And (I'm a bit embarrassed here), your way was better than mine!

Your strategy for your workbook seems workable. Good luck and have a good day!