Hi Stan,

The following nested if formula worked for me, with the following
assumptions:

C:\Users\Peggy\Desktop = path for first file.
testit = filename of first file.
A:A = range to sum in first file.
A1 = fixed cell in second file.

If the fixed cell in your second file is not 1-6, then the results in the
cell with the formula will be blank.

=IF(A1=1,SUM('C:\Users\Peggy\Desktop\[testit.xls]Week
1'!A:A),IF(A1=2,SUM('C:\Users\Peggy\Desktop\[testit.xls]Week
2'!A:A),IF(A1=3,SUM('C:\Users\Peggy\Desktop\[testit.xls]Week
3'!A:A),IF(A1=4,SUM('C:\Users\Peggy\Desktop\[testit.xls]Week
1'!A:A),IF(A1=5,SUM('C:\Users\Peggy\Desktop\[testit.xls]Week
5'!A:A),IF(A1=6,SUM('C:\Users\Peggy\Desktop\[testit.xls]Week 6'!A:A),""))))))

If you need help with changing your path, file, or range names - let me know.

Let me know if this helps.

Thanks,
Peggy

"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