Excel hyperlink / navigating to different sheet

0
votes

Currently I have two sheets in my spreadsheet.

On Sheet 1, I have 3 cells setup for how many tests passed, failed or received an NA (Actually labeling in Sheet 2 is- Pass, Fail and NA). On Sheet 2 (Cells V through AB), it contains PASS, Fail, and NA comments.

Sheet 1 picture:

enter image description here

Sheet 2 Picture:

enter image description here

I want to setup what is called I think a hyperlink but it does not function the way I need it too.

What I am trying to do:

For example, when clicking the cell B4 underneath UAT Passed in sheet 1, I want it to navigate to sheet 2 and either highlight or display only the cells that have the word Pass in them (with all the row details as well). I want to apply this to Fail and NA as well. Along with this, in cells B4 through D4 on Sheet 1, I would want it show to the # of "Pass, Fail and NA" that exists through cells V through AB.

If I am not clear please let me know.

Here is my current setup/formula:

enter image description here

2
excelvbaexcel-formula
An example for just one cell would be great. That way I can learn and apply the rest to the remaining cells (unless its much more complex than that) – LOZ
I was able to figure the majority out on my own. I now have the totals that I want and hyperlink that will take me to sheet two and highlight the V2:AB67 range. What is left is how do I have it display only the ones that have passed, if the pass hyperlink is clicked. And so on... – LOZ

2 Answers

0
votes

Use the Follow_Hyperlink event which has range called Target as a parameter, which is the cell containing the hyperlink. Use the range to set up your logic e.g:

If Not Application.Intersect(Target, Range("B4")) Is Nothing Then
     Sheets("Sheet2").Activate
End if
0
votes

Figured out the answers on my own. The following formula worked best for me: 

=CONCATENATE(COUNTIF(Script!V2:AB67,"PASS"))

=CONCATENATE(COUNTIF(Script!V2:AB67,"NA"))

=CONCATENATE(COUNTIF(Script!V2:AB67,"FAIL"))