Do we know having lookup on the Pictures is as interesting and affective as normal lookups in the excel!
It is just as simple as other lookups are!
It is certainly helpful when working on some visuals, Dashboards or Pic elaborative reports.
Lets start how we can have fun with this:-
I am having just a simple below dataset which comprises on Top player and bowler for each calendar year in the ICC ranking.
We want the Batsman and Bowler picture to be displayed when a particular year is selected via data validation. To get this working, we have to place each player picture in a particular cell and have to give that cell a particular name - prefer to give the name of the player to that cell as highlighted in the below image.
The simple way to name cells is to place the picture on the right column and names on the left column. By going to "Formula Tab" and then by clicking on the "Create From Selection", we can create names of right cells by copying the left cells' texts. Below video explains this step.
Having created the cell names, now we shall use the Indirect Formula along with Defining new formula. Before defining the formula, just use the data validation somewhere on your spreadsheet for the year and link simple VLOOKUP on that to get Batsman and Bowler name from the dataset. I have placed the player name on Cell D4 and J4 of the Top Player sheet.
Now we will define a new formula by goin to Formula Tab, Name Manager and then to click on New.
Here we will use Indirect Formula and shall give it reference to our player names. like in our example, for batsman - we will give reference to Cell D4 and for Bowler, it shall be Cell J4.
Below video will help to grasp this.
After defining the formula for both Bowler and Batsman, now you copy any of the 2 pictures on their respective locations, one for Batsman and one for Bowler. We have to apply now the already defined formula to these pictures. Here in below video, I am allocating the formula of "Player_Pic" to Batsman Pic. How this will be done is explained in the below video.
Repeat the same steps on the bowler side of the spreadsheet or on as many pics as you want to have in your report and enjoy this visual rich presentation.
Comments
Post a Comment