Forum Discussion

BillHull's avatar
BillHull
Copper Contributor
Oct 19, 2022

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

  • dscheikey's avatar
    dscheikey
    Bronze Contributor

    BillHull 

    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.

     

    • BillHull's avatar
      BillHull
      Copper Contributor
      Thank 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 ?
      • dscheikey's avatar
        dscheikey
        Bronze Contributor

        BillHull 

        Hello Bill, have you taken the trouble to look at my example document? There I have prepared a jump to another worksheet for you. If that's not it, then I don't understand the request well enough. Maybe you can create a concrete example. Then it will be easier.

Resources