Calculated Filename linking

[0ddball]

Hi there. I have a problem and I don't have the skills to fix it yet. I think the answer lies in VB, but I've never used the language, so its about time I started

Here's the problem:

I would like to create a links to two workbooks, both containing different data regarding my subject. All the workbooks have conventional names along the lines of:

<cohort> ::= <year><year>
<test> ::= "SATS"|"SPAR"
<filename> ::= <cohort><test>".xls"

(ie "20002007SATS.xls") generated by a piece of software. I would like my users to type the cohort into a cell and have the sheet link to the correct workbooks and sheets therein.

Looking at it - sounds quite hard actually ;S

Gods, my BNF is appaling too - I'm not having a good day

 

[hopelessliar]

I think you may need to explain this again - personally I'm definitely struggling to understand what you are trying to do.

And what do you mean by cohort?

 

[fumei]

Well I think I get cohort, but I concur that I am confused. Cohort appears to have two variables. Could you explain a little more? The user inputs what? From the filename example, it seems that two cohorts are in the filename, or is one cohort = 20002007?

Gerry

 

[GlennUK]

Have a look at this Developer Tip by John Walkenbach:

http://j-walk.com/ss/excel/tips/tip82.htm

The last example will do exactly what you want.

Cheers, Glenn.