Hyperlink to view all the rows associated to the value

[Abhimanyu]

Dear Sir,

I am new to this forum and new to using excel as well.

I would need your help in creating a hyperlink, which when clicked goes to the sheet from where the data is being extracted (1), shows only those cells from where the no. is being derived (2, almost similar to a filter). Lets elaborate;

I have the following headers:

Department, Position, Request Date, Target Date, Current Status, TAT

This is a hiring department sheet, which tracks the department it is hiring for, the position hiring for, when was the request placed by the Business Unit (several business units putting requests to recruit for a different positions), target date is when the recruitment should be closed, current status will have whether it is in process or position offered, TAT is whether the job is 'In time' or "Out of time", meaning within the target date or crossed the date.

So in the presentation page, I have used countifs formula to extract data for every department based on the following criteria's:

1. Under "In time", how many are in process and how many have positions offered. Similarly I have this for "Out of Time".

Now, say a department named ER has 8 names under "In Time">in process. I need, that when I click on the value 8, it takes me to all those 8 positions which are "in process", so as to identify the exact positions which are still in process.

I would be delighted to cross this hurdle.

Sorry for being so lengthy.

Regards,
Abhi

 

[Luke M]

Hi Abhi,

Welcome to the forum, and the world of Excel.

Rather than go about creating all those COUNTIF formulas, and then trying to hyperlink to all the records, I think we should go another route. It sounds like your data is setup in a nice table, which is perfect. Now, if we create a PivotTable (PT) from this data, we'll have two advantages.
First, the PT cna automatically setup the COUNTIF functions, preparing a nice summary breakdown of each process for each department.
Second, and the primary benefit, is that you can double-click on any of the numbers in the PT, and it will open a new sheet showing those records!

As you're new to Excel, I know the above may sound a little intimidating, but fear not. First, here's a tutorial on how to setup a PT:
http://www.contextures.com/CreatePivotTable.html

After you've got the PT setup, try double-clicking on a number, and you'll see what I mean about the new sheet appearing.

 

[Abhimanyu]

Hi Abhi,

Welcome to the forum, and the world of Excel.

Rather than go about creating all those COUNTIF formulas, and then trying to hyperlink to all the records, I think we should go another route. It sounds like your data is setup in a nice table, which is perfect. Now, if we create a PivotTable (PT) from this data, we'll have two advantages.
First, the PT cna automatically setup the COUNTIF functions, preparing a nice summary breakdown of each process for each department.
Second, and the primary benefit, is that you can double-click on any of the numbers in the PT, and it will open a new sheet showing those records!

As you're new to Excel, I know the above may sound a little intimidating, but fear not. First, here's a tutorial on how to setup a PT:
http://www.contextures.com/CreatePivotTable.html

After you've got the PT setup, try double-clicking on a number, and you'll see what I mean about the new sheet appearing.

Dear Luke Sir,

Thanks for the super fast response.

I agree with you about the usage of Pivot Table. I did complete that process and showed it to the concerned person (manager), but he does not enjoy the fact, that when you click on a value, it creates a sheet and shows the data. What he wants me to do is reinvent the wheel and create a stylish way of looking at the data; which means create a hyperlink for values so it does the same thing as a pivot table would do on a double click; though not on new sheets.

Waiting for your response.

Once again thank u and best wishes

Regards,
Abhi

 

[Luke M]

Hi Abhi,

Like you (probably), I'm not quite sure what you boss is expecting. He was to see the records, yet he doesn't want to see the records. I can't even visualize what it is he's wanting to happen.

Okay, here's a shot...we could write a script that when you double click on a cell, the macro figures out what category/group you are in. It then takes you to the data sheet, applies an AutoFilter, and filters down to the records in question. We could further add a macro that returning back to the PT unfilters the data table.
It gets a bit complicated, but its doable, I suppose. TO do this, would definitely need an example workbook showing the exact layout of your data and PT (can fill with dummy info, layout is what's important).