00:00Any Excel users at least one time in their career had to find an intersecting data value on your
00:06data set and end up having a cross-eye. You know exactly what I'm talking about. Consider my test
00:11data set here. Let's say if you're looking for coconut that was sold on 2018, what you do is
00:17go down your list, look for coconut, and then go all the way up. Make sure you're on column F,
00:23which is 2018. Scroll all the way down, and the answer that you're looking for is 44. As you can
00:28see the process can be really tedious, especially if your data set is very, very large.
00:33So the first thing I'm going to do is basically allow the user to enter fruit and the ear, and
00:38then
00:38create a crosshair with a yellow line or whatever color that you like. Instead of typing, I'm going
00:44to do a pull-down or a drop-down. You're going to select the fruit on cell C2 here, and
00:48then you're
00:49going to go data. Under data tools, there's this data validation. Select data validation. Under the
00:55settings tab, you're going to change the allow to list, and on the source, basically you're going to
01:02select or click on this arrow here, and then the pop-up window becomes small, which allows you to
01:07select any items on your data set. So basically, I'm going to select all the fruit because I'm
01:13working on this fruit here, all the way down to the very last one, except the total. And then after
01:18that, if I hit enter, and enter one more time, you can see a pull-down menu appear with all
01:24your fruits
01:26that are available in there. Just select one, and that completes the pull-down. Let's do the same thing
01:32with year. Select the year on cell C3, data, data tools, data validation, a pop-up will appear again.
01:40Setting tabs, allow changes to lists, click on the arrow again. But this time around, your ears are in
01:46horizontal format. So basically, click the very first cell on B6 here and drag all the way to the
01:52very last year that you have. And then click or press enter twice like this, one, two, and then you
01:58can
01:58see on your pull-down, you have all the years that's on your data set here. So that's how you
02:04do the pull-down
02:05or drop-down list. It makes it easier so that you don't have to actually manually type it. So now
02:09for the
02:10crosshair, what you do is that you first highlight your data set area like this all the way
02:16to the bottom. I have a total at the bottom. I'm just going to skip the bottom
02:19total here. And then go all the way up. Now you're going to select a home on your
02:23ribbon tab here. Go style under style. There's conditional formatting and click on
02:29new rules. And on this pop-up here, you're going to select use a formula to
02:33determine which cell to format. And the formula that you'll be using is equal or
02:38open parenthesis. So it's going to use two conditions to validate the crosshair because crosshair has two lines, right?
02:45the horizontal and the vertical. We're going to start off with fruit here.
02:49So basically I'm going to say the very first one which is A7 and you're going to
02:53press F4 twice which is going to make our column static and our row variable like
03:01that so that it will give you the crosshair look of the horizontal. And then that's
03:07going to equals to and basically look for the fruits that the user have entered on cell C2
03:13like this. So basically you're checking what the user have selected against what's
03:17available so that they know exactly which row to highlight. And the second
03:21argument on the OR would be the year now. So the year basically we're going to
03:26select the very first year on our data set which is in cell B6. And this time
03:30around we're going to press F4 once so that the the row is fixed but the column is
03:36this variable like this dollar sign on the on the row. And then we're going to
03:41check say equal to whatever the user has selected on cell C3 here like this.
03:47You're going to do close parentheses and that completes your simple condition for
03:51your crosshair. Now to highlight or color the crosshair click on format button and
03:57then you're going to select fill and then under the fill select the color that you
04:02want the crosshair to look like. So I'm going to select yellow for mine and I'm
04:05going to click OK and OK one more time. And you can see that the crosshair is
04:10already working there. It's basically looking for olive on 2020. Now if you do a
04:15quick test here maybe change this to something up in a fruit chain here. Orange
04:19you can see orange on 2020 and if you want to change the year you can easily
04:23change the year like this in 2017. Let's do a quick test on our coconut that we
04:28talked about just now in the early part of the video. Coconut on 2018 we should
04:34give you the value of 44 like this.
Comments