If function Help

[Guest]

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

 

[Guest]

Try
=INDIRECT("'[filename.xls]sheet"&CellAddress&"'!$A$1")

replace filename.xls with the name of your file
replace CellAddress with the address of the cell containing the digit of
interest

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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