Hi all,

Simple subselect question :

I was asked to make an "Access-style" form to allow users to subselect data in an Excel file. The file has many sheets, all having the same field layout (e.g. street name is always at column G). People here are used to bringing Excel data to Access for this kind of work. I am not too keen on duplicating data just to get simple reporting functionalities, so I am trying to find a solution from within Excel. Ideally, I would like to point to the Excel file from another Excel file (e.g. form.xls) so as to avoid modifying the original Excel file, which can be seen as a read-only database file for my purposes (but which could also be modified by other users adding more records to it!). I could also add another sheet to the original Excel file though, if this is easier.

Searching for solutions in Excel, I tried list box (form toolbar), data/validation/list and a couple of DB functions. One recurring problem I have is that since my street names happen to be in many different sheets and that there are blank lines, I can't easily name a range and stuff that named range in my list box or dropdown list. Can I define a named table spreading over many sheets ?

I also tried the simple data/filter/autofilter way. This does about 80 % of what I want. If only I could have all my street names in the one filter dropdown instead of having my user scroll through sheets to find the name they want, that would be great. Also, if I could hide all but just a few fields, I would have almost exactly what I need.

Image link (related) question :

One of the data fields I want back from the above subquery is a planID (corresponding to a street name). I know that there is a corresponding image to that planID : it is either "planID.jpg" or "planID.tiff". What would be a simple VBA function that would create an extra column of type=hyperlink with the name of the image file? In pseudo-code : If 'planID.jpg' exists, then return 'planID.jpg' else return 'planID.tiff', take that string and make that as a hyperlink in a new column. My users could just click on that column and get to the image they want.

Thanx !