Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function Help
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function Help
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 "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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function Help
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function Help
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function Help
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function Help
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function Help
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Embed a countif function in subtotal function? | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |