Forum Discussion
BillHull
Oct 19, 2022Copper Contributor
reports from multiable sheets
Using multi able sheet reporting , can a cell in a row in the report be made active so they take me to the sheet containing the reported table .
hope this makes sense
cheers
Bill
- dscheikeyBronze Contributor
Hello Bill, if I have understood you correctly then "yes" there is such a possibility. You can use the HYPERLINK() function to create a link to the table where the data comes from. The link to the table must be structured as follows:
=HYPERLINK(#SHEETNAME!CELLREFERENCE, DISPLAY TEXT) =HYPERLINK("#Sheet2!B2","klick here")
I have created a small example document for you. In Sheet1 are the links that refer to Sheet2-4.
Now you only have to determine how the data comes into the function.
Possibility 1: You have a column where the sheetnames are to be referred to. Or possibility 2: You get the sheetname from one of the formulas in the row that refers to the corresponding worksheet. I have done this using the example of a very simple function that fetches a cell content from the worksheet. The FORMULATEXT() function is used to filter out the sheet name. In any case, this must be adapted for you, as you probably do not use such a simple function as I have used.
- BillHullCopper ContributorThank you , however I think we might be going in different directions, I am looking for the ability ,
to use a query made from multiple sheets , choose a cell on the query report which will ,, lets say hyper jump from the query to the sheet that cell originally came from , similar to what happens when using find all in search, you can choose a result and jump straight to the sheet from there ,
cheers and thanks you , any ideas ?