Actions on Filter
In this Blog we learn about the Actions.
Now what exactly is action -
Actions- Actions are something which will perform some activity when you trigger some event.
Types of Actions-
Actions are basically of three types.
1. Actions on filter
3. Actions on the highlight
3. Actions on the highlight
For example -
I have some two different sheets which are given as PC sales and PSC sales.
PC sales stand for the product category sales- If you see category wise sales which I have given below.
1. Action on filter-
Dataset- Sample Superstore.
Step 1-Take Category in Cloumns field. and Take Sales in Rows field.
and 2nd sheet which is named as PSC sales- product subcategory sales, which is again given below.
Step 2- Open New sheet named as PSC sales. Take Category and Sub-category in Columns field and Take Sales in Rows field.
So, Category is the product, we have the Sub-category, and the Sales are given above in the screenshot. So, if you observe a product furniture is a category on Y axis And on X axis all the names like Bookcases, Chairs up to Phones these are the different subcategories.
Now in these actions filter what we will be doing is if I select any one bar graph from PC sales sheet. For example: If I select furniture in PC sales sheet, in the next sheet means PSC sales sheet any information related to furniture will be highlighted. So, what I'm doing is I'm triggering an event there in the first sheet (PC sales) in the source sheet and the action is performed on the 2nd sheet (PSC sales). So, this is how we'll be working on actions on filter.
The database which I've connected is Sample Superstore. So, I hope you understood what I'm trying to tell you. If I select option office supplies, in the next sheet this entire data should get highlighted. This is what actions on filter will be working. Now remember two things One is the source sheet and 2nd is the destination sheet.
Let's see how to work on it. Now for inserting an action there are various methods.
Step 3- I will go to worksheet and click on these actions and there it says I want to connect multiple sheets in the same workbook I can do that.
Step 4- So I'll just click on add action. As I said the three types of actions filters, highlight and the third one is URL. Select Filter and click on Ok button.
First, I will show you for filter now name of the action what you want to give is I want to give as sales1.
Step 5- Some name I'll give it 'Sales1'.
Now, my source sheet is right now Product category sales.
Step 6- I will select the PC sales as a source sheet. and In that there are three things, I select "Select" as a Run-on Action.
Step 7- then Select PSC sales as a Target sheet. and Click on "Show all value" Option.
Once I select this source sheet that is PC sales. and while selecting anything in the target sheet where the target sheet there are two sheets which are target So I will say the PSC sales sheet is my target sheet.
Step 8- And I will click on OK and So I will just again click on OK.
So, what will happen is now whenever I select PC sales any option any bar graph in the destination sheet or target sheet something will happen.
Step 9- So if I select your furniture and if you observe the sheet has changed furniture is selected. If you observe here that's the action part.
The PSC sales sheet get changed and displaying Furniture Sub-category like Bookcases, chairs and two more products.
Again, if I go back and if I click outside OK.
So, I will again come back to all bar graphs.
Step 10- So if I select two. ex: Furniture and Office Supplies.
Here, I am just trying to check selecting these two categories of bar graphs and this is when you trigger an event in the source sheet something will happen in your target sheet. This is how it works itself. I can see the changes occur in destination sheet as per our selection. The following changes are reflected.
Observe your furniture and Office supplies subcategories are reflected on Target sheet, because in the source sheet I have selected two things furniture and Office Supplies.
Step 11- Now Click outside.
So, when I click outside what will happen is, if I go to PSC sales all the three sections i will be able to see, that means when you leave the filter at this place, what will happen You are able to see all the values in the destination sheet.
Now let's see we will try something new.
Step 12- I will go to worksheet click on these actions. Double click on this sales1, that is a filter which you have created.
Step 13- Select "Hover" Run action on option. Click on OK.
Step 14- Again click on OK.
Whenever I hover in PC sales, in the target sheet that is PSC sales show me all the values. I should say "leave the filter" clearing the selection will leave the filter itself.
Now I will go back to PC sales sheet and now if I move my cursor there for the I will just go on the top side Now I will come on this technology.
Step 15- Keep my cursor on the technology in PC sales.
It was very fast now; I will just move my cursor on the technology.
And on the next sheet only technology is selected because the actions I am changing the attributes there.
Step 16- Again if I go back Move cursor over Office supplies in PC sales sheet.
And now the office supplies are visible on the PSC sales sheet.
Let's go to worksheet and if I go to these actions double click on source. source sheet is simple and Target sheet is simple Now what you do the action Run on action as 'Hoover' or run on action on the 'select'. What do you want and what should the actions happen on the target sheet is given under "Clearing the selection will."
The last one I will show you what is all about the 'Menu'.
Step 17-In source sheet area, select 'Menu' option under "Run action on".
Step 18- In Target sheet area, under ''Clearing the selection will" select Keep filtered values.
and click on OK.
Step 19- Again click on OK. It is given in below screenshot.
return to same sheet.
Step 20- And now what will happen is if I select Furniture on PC sales sheet, I will be getting the option here as sales1. Click on Sales1.
Sales1 is nothing but the name of the action After clicking on it, I will be jumping to this furniture itself.
So, the 'Menu' means you will be getting in the tool tip. Inside the tool tip you will be getting a hyperlink to perform the action and if you click on that the action would be performed again if I want to deselect this column either I will select it again or I can select it outside. So, the entire would be deselected. The menu itself It says nothing, but a hyperlink will be given where you can apply the action. So, if I click on Sales1 and the action would be applied at the furniture bar.
Conclusion- In this blog we learn about one of the types of Action named 'Action on filter' by exploring Select, Hover and Menu source side options practically.
Note: In my upcoming blog we will learn our rest two actions types. Those are 'Action on the URL' and 'Action on the Highlights.'