Forum Discussion
reports from multiable sheets
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.
- 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 ?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.
- I can see what you are suggesting , however to do that , i will have to link every sheet individually, and if the report changes because of changes in the sheet , will the update report need to be re- linked.?
eg. I have a report that shows items that need purchasing , the sheets each change as the levels drop or increase from "purchase" to "not required" , if the cell value on the sheet says "purchase "it will appear in the purchase report query, every entree to a sheet changes the report at the same time
cheers
