Hi,

Best to use the indirect function, however it doesnot always work for
external references, it would be better to just work with one file

Think of the indirect as a 3 dimensional reference with x as rows, y as
columns and z as sheetname

a typical formula is =indirect( "'" & z & "'!" & y & x)

On the target sheet make row 1 the inputs for y
make Column A the inputs for x

In this case z is what I call a keystone, put it at the cell 1 up and 1 left
of the top left corner of your data say d4

the cell left of the keystone is where you enter the number. In the
keystone cell enter ="sheet"&c4

so for one the keystone value is sheet1

For a given piece of info on the target sheet the formula should read e.g.
for cell e5

indirect( "'" &$d$4 & "'!" & $e$1 & $a5)

where b5 = source sheet name, sheet1, e1 = target sheet row eg 72 & A5 =
target sheet column eg J

This resolves to

indirect( 'sheet1'!J72)

if you type 2 in c4 then

indirect( 'sheet2'!J72) and so on

cheers

"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