Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Indirect
Excel 2007 Win XP Professional Current Setup A: Date (m/dd/yyyy) C: Amount (needs to be pulled from the appropriate file based on the date) F: Path (\\server\folder1\folder2\[filename part 1 G: Variable (date in mmddyy format) H: Part III (remainder of filename]Sheet!) I: Column ($AP) - this will be a variable also. J: Cell ($6) So, if I concatenate in Column K (=F5&G5&H5&I5&J5) I get \\NTS141\KEGCommon\KE - Jasper\Inventory Projection Detail\[Inventory Projection - KE Jasper wk092208 rev4 with KETL change.xls]Input Planned Sales!$AP$6 What I want is the value that is in the referenced cell. I thought I was going to need to use INDIRECT but I haven't been able to get my head around it. If I need to set up in a different manner or if INDIRECT is not the way to go please advise. Thank you in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Indirect
INDIRECT will not work with workbooks that are closed. You might like
to download a free add-in, morefunc (do a google search), which has the function INDIRECT.EXT which you could try instead. Hope this helps. Pete On Nov 25, 2:22*pm, Tanya M wrote: Excel 2007 Win XP Professional Current Setup A: * * *Date (m/dd/yyyy) C: * * *Amount (needs to be pulled from the appropriate file based on the date) F: * * *Path (\\server\folder1\folder2\[filename part 1 G: * * *Variable (date in mmddyy format) H: * * *Part III (remainder of filename]Sheet!) I: * * *Column ($AP) - this will be a variable also. J: * * *Cell ($6) So, if I concatenate in Column K (=F5&G5&H5&I5&J5) I get \\NTS141\KEGCommon\KE - Jasper\Inventory Projection Detail\[Inventory Projection - KE Jasper wk092208 rev4 with KETL change.xls]Input Planned Sales!$AP$6 What I want is the value that is in the referenced cell. *I thought I was going to need to use INDIRECT but I haven't been able to get my head around it. If I need to set up in a different manner or if INDIRECT is not the way to go please advise. Thank you in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Indirect
I meant to add that I knew the function would not work with closed workbooks,
for now that is OK... I have looked into the morefunc add-in and would like to try to avoid that for now... I would just really like to try to understand how the indirect function is supposed to work as I cannot get it to do what I think it is supposed to... "Pete_UK" wrote: INDIRECT will not work with workbooks that are closed. You might like to download a free add-in, morefunc (do a google search), which has the function INDIRECT.EXT which you could try instead. Hope this helps. Pete On Nov 25, 2:22 pm, Tanya M wrote: Excel 2007 Win XP Professional Current Setup A: Date (m/dd/yyyy) C: Amount (needs to be pulled from the appropriate file based on the date) F: Path (\\server\folder1\folder2\[filename part 1 G: Variable (date in mmddyy format) H: Part III (remainder of filename]Sheet!) I: Column ($AP) - this will be a variable also. J: Cell ($6) So, if I concatenate in Column K (=F5&G5&H5&I5&J5) I get \\NTS141\KEGCommon\KE - Jasper\Inventory Projection Detail\[Inventory Projection - KE Jasper wk092208 rev4 with KETL change.xls]Input Planned Sales!$AP$6 What I want is the value that is in the referenced cell. I thought I was going to need to use INDIRECT but I haven't been able to get my head around it. If I need to set up in a different manner or if INDIRECT is not the way to go please advise. Thank you in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Indirect
Well, if you open that file that you are trying to get the data from
at the same time as your other file, then you could use: =INDIRECT(K5) assuming K5 is your concatenated reference. You will need to incorporate apostrophes around the path, filename and sheet name to take care of the spaces that you have, so that your formula in K5 might be: ="'"&F5&G5&H5&"'!"&I5&J5 where you should remove the ! at the end of H5. Of course, you don't really need the full path if the file is already open !! Hope this helps. Pete On Nov 25, 2:39*pm, Tanya M wrote: I meant to add that I knew the function would not work with closed workbooks, for now that is OK... I have looked into the morefunc add-in and would like to try to avoid that for now... *I would just really like to try to understand how the indirect function is supposed to work as I cannot get it to do what I think it is supposed to... "Pete_UK" wrote: INDIRECT will not work with workbooks that are closed. You might like to download a free add-in, morefunc (do a google search), which has the function INDIRECT.EXT which you could try instead. Hope this helps. Pete On Nov 25, 2:22 pm, Tanya M wrote: Excel 2007 Win XP Professional Current Setup A: * * *Date (m/dd/yyyy) C: * * *Amount (needs to be pulled from the appropriate file based on the date) F: * * *Path (\\server\folder1\folder2\[filename part 1 G: * * *Variable (date in mmddyy format) H: * * *Part III (remainder of filename]Sheet!) I: * * *Column ($AP) - this will be a variable also. J: * * *Cell ($6) So, if I concatenate in Column K (=F5&G5&H5&I5&J5) I get \\NTS141\KEGCommon\KE - Jasper\Inventory Projection Detail\[Inventory Projection - KE Jasper wk092208 rev4 with KETL change.xls]Input Planned Sales!$AP$6 What I want is the value that is in the referenced cell. *I thought I was going to need to use INDIRECT but I haven't been able to get my head around it. If I need to set up in a different manner or if INDIRECT is not the way to go please advise. Thank you in advance!- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Indirect
I see what you're saying about the path now...(I think I've been staring at
it too long) I did download/install morefunc and with your help and the INDIRECT.EXT function I did get the results I wanted. THANK YOU! For Reference this is what I did: A: Date (m/dd/yyyy) C: Amount (needs to be pulled from the appropriate file based on the date) F: Path (\\server\folder1\folder2\) G: Filename (first part, non-variable) H: Filename (middle part, variable) I: Filename (last part, non-variable) J: Worksheet name (non-variable) K: Column (variable) L: Cell (non-variable) and the formula is (in C5)... =INDIRECT.EXT("'"&F5&"["&G5&H5&I5&"]"&J5&"'!"&K5&L5) "Pete_UK" wrote: Well, if you open that file that you are trying to get the data from at the same time as your other file, then you could use: =INDIRECT(K5) assuming K5 is your concatenated reference. You will need to incorporate apostrophes around the path, filename and sheet name to take care of the spaces that you have, so that your formula in K5 might be: ="'"&F5&G5&H5&"'!"&I5&J5 where you should remove the ! at the end of H5. Of course, you don't really need the full path if the file is already open !! Hope this helps. Pete On Nov 25, 2:39 pm, Tanya M wrote: I meant to add that I knew the function would not work with closed workbooks, for now that is OK... I have looked into the morefunc add-in and would like to try to avoid that for now... I would just really like to try to understand how the indirect function is supposed to work as I cannot get it to do what I think it is supposed to... "Pete_UK" wrote: INDIRECT will not work with workbooks that are closed. You might like to download a free add-in, morefunc (do a google search), which has the function INDIRECT.EXT which you could try instead. Hope this helps. Pete On Nov 25, 2:22 pm, Tanya M wrote: Excel 2007 Win XP Professional Current Setup A: Date (m/dd/yyyy) C: Amount (needs to be pulled from the appropriate file based on the date) F: Path (\\server\folder1\folder2\[filename part 1 G: Variable (date in mmddyy format) H: Part III (remainder of filename]Sheet!) I: Column ($AP) - this will be a variable also. J: Cell ($6) So, if I concatenate in Column K (=F5&G5&H5&I5&J5) I get \\NTS141\KEGCommon\KE - Jasper\Inventory Projection Detail\[Inventory Projection - KE Jasper wk092208 rev4 with KETL change.xls]Input Planned Sales!$AP$6 What I want is the value that is in the referenced cell. I thought I was going to need to use INDIRECT but I haven't been able to get my head around it. If I need to set up in a different manner or if INDIRECT is not the way to go please advise. Thank you in advance!- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Indirect
Glad to hear that you got it to work, Tanya - thanks for feeding back.
Pete On Nov 25, 3:46*pm, Tanya M wrote: I see what you're saying about the path now...(I think I've been staring at it too long) I did download/install morefunc and with your help and the INDIRECT.EXT function I did get the results I wanted. * THANK YOU! For Reference this is what I did: A: * * *Date (m/dd/yyyy) C: * * *Amount (needs to be pulled from the appropriate file based on the date) F: * * *Path (\\server\folder1\folder2\) G: * * *Filename (first part, non-variable) H: * * *Filename (middle part, variable) I: * * *Filename (last part, non-variable) J: * * *Worksheet name (non-variable) K: * * *Column (variable) L: * * *Cell (non-variable) and the formula is (in C5)... =INDIRECT.EXT("'"&F5&"["&G5&H5&I5&"]"&J5&"'!"&K5&L5) "Pete_UK" wrote: Well, if you open that file that you are trying to get the data from at the same time as your other file, then you could use: =INDIRECT(K5) assuming K5 is your concatenated reference. You will need to incorporate apostrophes around the path, filename and sheet name to take care of the spaces that you have, so that your formula in K5 might be: ="'"&F5&G5&H5&"'!"&I5&J5 where you should remove the ! at the end of H5. Of course, you don't really need the full path if the file is already open !! Hope this helps. Pete On Nov 25, 2:39 pm, Tanya M wrote: I meant to add that I knew the function would not work with closed workbooks, for now that is OK... I have looked into the morefunc add-in and would like to try to avoid that for now... *I would just really like to try to understand how the indirect function is supposed to work as I cannot get it to do what I think it is supposed to... "Pete_UK" wrote: INDIRECT will not work with workbooks that are closed. You might like to download a free add-in, morefunc (do a google search), which has the function INDIRECT.EXT which you could try instead. Hope this helps. Pete On Nov 25, 2:22 pm, Tanya M wrote: Excel 2007 Win XP Professional Current Setup A: * * *Date (m/dd/yyyy) C: * * *Amount (needs to be pulled from the appropriate file based on the date) F: * * *Path (\\server\folder1\folder2\[filename part 1 G: * * *Variable (date in mmddyy format) H: * * *Part III (remainder of filename]Sheet!) I: * * *Column ($AP) - this will be a variable also. J: * * *Cell ($6) So, if I concatenate in Column K (=F5&G5&H5&I5&J5) I get \\NTS141\KEGCommon\KE - Jasper\Inventory Projection Detail\[Inventory Projection - KE Jasper wk092208 rev4 with KETL change.xls]Input Planned Sales!$AP$6 What I want is the value that is in the referenced cell. *I thought I was going to need to use INDIRECT but I haven't been able to get my head around it. If I need to set up in a different manner or if INDIRECT is not the way to go please advise. Thank you in advance!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4) | Excel Discussion (Misc queries) | |||
Please help w/ INDIRECT | Excel Worksheet Functions | |||
INDIRECT.EXT | Excel Worksheet Functions | |||
INDIRECT | Excel Discussion (Misc queries) |