Using a Tick box as part of my formula (also Cell Refernce issue)

[oneblondebrow]

Hi all,

Please find attached a test sheet, What it does is on the KEYWORD sheet you can type in a word and it lists the topics (with hyperlinks to their ranges) and find this from the TOPICS sheet, I am having a slight issue with invalid reference though on this for some reason that i wonder if you could firstly help with?

Once that is fixed (i think it is to do with the name of the worksheets), is there a way to integrate the Tick Boxes at the top of the KEYWORD page into the search, So it goes something like this..

Enter KEYWORD Whisky, Clicking the tick box The Offlicence then the keyword search will only return the search finds from the OFFLICENCE tabs (both of them), and the same to happen with the other 2.

is this even possible?

Thanks in advance.

[JohnTopley]

Why not simple use the KEYWORD (part off e.g Offlicence) to determine tabs?

You only return one item in your search: no "Russian Vodka" !

Make you "Subject" a drop down list (avoids typing and typing errors). I added one (named range TOPIC)to the attached but it does not list the documents associated with a keyword.

If you want use tick boxes then you need to add format controls and a table to identify which tick box was activated.

[oneblondebrow]

Why not simple use the KEYWORD (part off e.g Offlicence) to determine tabs?

You only return one item in your search: no "Russian Vodka" !

Make you "Subject" a drop down list (avoids typing and typing errors). I added one (named range TOPIC)to the attached but it does not list the documents associated with a keyword.

If you want use tick boxes then you need to add format controls and a table to identify which tick box was activated.

Thanks for the reply.


I probably should have mentioned what the original spreadsheet does, it allows the user to search all the tabs for topics to answer questions, so for instance if the user wanted to know what fuel type was in a specific car they could enter "ford" in the search box and it will hyperlink them to the named range with the relevant information, issue is I now have 3 documents for this because we cater for 3 different companies, same questions but different answers, I wanted to find a way of integrating these company all under the same workbook by allowing the user to first choose the company (by tick box or drop down) and the search will only return the topics for that company.

I hope that makes sense

[JohnTopley]

I haven't given this much thought by may be dependent drop downs would help. so the primary drop down is the company list ,,,,

Take look here ....

http://www.contextures.com/xlDataVal02.html

[JohnTopley]

It appears you can (will) have a very large range of topics: can these be broken down into sub-categories?

And how are sheets to organised (named): e.g Company & Category, Company & Sub-Category , Company & Sub-Category & Topic??

And are the "answers" a simple column (topic?) match i.e. B in your sample with description (answer) in column C.

Without the detail of your requirement another possible ( untested !) approach is to return the answer to the KEYWORD sheet rather than HYPERLINKing everywhere.

See example in Column H of attached.

This searches all tabs in "Sheetlist" .

You could have "Sheetlists" per company: See J6:J8