How to call a page tab from a variable

[Wilma]

Hi there, Ive going to supper death by google soon..

I have a formula ='D:\Documents and Settings\s.wilks\Desktop\working-folder\[2010 Sales Stats.xls]JAN'!$K$6

notice the sheet name it referrs to as "jan" I have multiple sheets on a differnt spreadsheet I want to link.

I want to type in on one cell (or a input box) a month value of say "MAR" then all the formulas in that page change to this new referance.

Im my looking so far I see moslty the opposite to what im trying to do ie - =MID(CELL("filename"),(FIND("]",CELL("filename"))+1),50) but i dont want this.

Ive even trying the VB route but even this dosent work
Function Simonvar(rAnyCell)
Application.Volatile
Simonvar = "'D:\Documents and Settings\s.wilks\Desktop\working-folder\[2010 Sales Stats.xls]JAN'!$K$3"
End Function

any pointers apreciated

 

[SkipVought]

hi,

Let's assume that MAR is in A1...
[tt]
=[red]INDIRECT("[/red]'D:\Documents and Settings\s.wilks\Desktop\working-folder\[2010 Sales Stats.xls][red]"&A1&"[/red]'!$K$6[red]")[/red]
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Note that INDIRECT only works on external workbooks when those workbooks are open at the same time as the formula workbook.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Wilma]

Splendid, Have tested & this is just what i had been looking for

Many thanks

Si