You can try
1) creating the formula so it work properly for a single tab
2) perform a search & replace when you need to change the formula

Still problematic, but may be the only approach, given your limitations

"Stan Halls" wrote:

Thanks for that but unfortunatly i am not allowed to download add-in's as i
am on a company network and the file is closed so i will have to think of
another way round this problem

"Duke Carey" wrote:

Forgot to mention that INDIRECT() requires the linked file to be open in
order to retrieve the data

If you need to link to a CLOSED file, google for the INDIRECT.EXT() function
add-in

"Stan Halls" wrote:

i have 2 files, one file has 6 tabs named week1 - 6 filled with data,
the 2nd file has a fixed cell that has a number from 1 - 6 ,
in a cell on file 2 i want to check the fixed cell and depending on the
number get the results from the corrosonding tab on the other file
so if the result in the fixed cell is 1 then sum filename sheet1:a1 if 2
then filename sheet2:a1 and so on ,
any ideas
Thanks in advance for any help