Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
I am attempting to concatenate a cell value (date) with a file name to create
a data reference to a value within an Excel file. If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", "Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
This might be what you are looking for:
=CONTATENATE("='C:\ConsolPeriod Report",TEXT(A3,"mm/dd/yy"),".xls' " & SData) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
If your string is evaluated to the right result then just put an INDIRECT
around it... For example if A1 has the string Sheet2!A1 =INDIRECT(A1) will evaluate to =Sheet2!A1 and give you the value in Sheet 2 A1 "Michael" wrote: I am attempting to concatenate a cell value (date) with a file name to create a data reference to a value within an Excel file. If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", "Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
Spelling - Change Contatenate to Concatenate.
"TomPl" wrote: This might be what you are looking for: =CONTATENATE("='C:\ConsolPeriod Report",TEXT(A3,"mm/dd/yy"),".xls' " & SData) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
Note that INDIRECT only works with open files. If you want to work
with closed files (which is implied by your use of the path) then you will need to get the free add-in morefunc and use INDIRECT.EXT. Note also that you need square brackets around the filename, and the TEXT function in Tom's reply should probably be: TEXT(A3,"m-d-yy") Also, there is a ! missing in the formula. Hope this helps. Pete On Oct 22, 4:14*pm, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote: If your string is evaluated to the right result then just put an INDIRECT around it... For example if A1 has the string Sheet2!A1 =INDIRECT(A1) will evaluate to =Sheet2!A1 and give you the value in Sheet 2 A1 "Michael" wrote: I am attempting to concatenate a cell value (date) with a file name to create a data reference to a value within an Excel file. *If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", *"Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group!- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. ==== ps, you can use this function for the date formatting: ....,text(a$3,"m-d-yy"),... pps. I find using the & operator easier to type than the =concatenate() function: =concatenate(a1,a2,a3) becomes =a1&a2&a3 ppps. Open the sending workbook and create a formula in the receiving workbook that retrieves the value from that cell. Then close the sending workbook and you'll see the syntax for the string you want to use in the =indirect.ext() function. Your existing syntax isn't going to work. Michael wrote: I am attempting to concatenate a cell value (date) with a file name to create a data reference to a value within an Excel file. If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", "Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group! -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
Dave, I must really be thick headed on this one. I downloaded and installed the function suite that includes the indirect.exe function. I did as you suggested (open the sending file, insert the formula and then close the sending file to get the correct path). I tried to use the indirect.ext function without attempting to build the file name and all I get is "#REF!" in the cell for a result. I don't really care if the workbook is open or closed (I can open workbooks as needed to update the cells). My problem seems to stem from the building the file name from a variable string that includes the date of the sending workbook. If I type in the file and path the cell value is updated from the referenced workbook. If I build the file name to include the date from the cell above, only the file name is displayed in the cell and the cell value is not pulled from the sending file. As far as I can see the the file reference is accurate but it just won't pull the data. Thanks in advance for your thoughts. Mike "Dave Peterson" wrote: The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. ==== ps, you can use this function for the date formatting: ....,text(a$3,"m-d-yy"),... pps. I find using the & operator easier to type than the =concatenate() function: =concatenate(a1,a2,a3) becomes =a1&a2&a3 ppps. Open the sending workbook and create a formula in the receiving workbook that retrieves the value from that cell. Then close the sending workbook and you'll see the syntax for the string you want to use in the =indirect.ext() function. Your existing syntax isn't going to work. Michael wrote: I am attempting to concatenate a cell value (date) with a file name to create a data reference to a value within an Excel file. If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", "Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group! -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
It works, of course you need to have a correct string
within that function to make it work. How does your formula that returns an error look? -- Regards, Peo Sjoblom "Michael" wrote in message ... Dave, I must really be thick headed on this one. I downloaded and installed the function suite that includes the indirect.exe function. I did as you suggested (open the sending file, insert the formula and then close the sending file to get the correct path). I tried to use the indirect.ext function without attempting to build the file name and all I get is "#REF!" in the cell for a result. I don't really care if the workbook is open or closed (I can open workbooks as needed to update the cells). My problem seems to stem from the building the file name from a variable string that includes the date of the sending workbook. If I type in the file and path the cell value is updated from the referenced workbook. If I build the file name to include the date from the cell above, only the file name is displayed in the cell and the cell value is not pulled from the sending file. As far as I can see the the file reference is accurate but it just won't pull the data. Thanks in advance for your thoughts. Mike "Dave Peterson" wrote: The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. ==== ps, you can use this function for the date formatting: ....,text(a$3,"m-d-yy"),... pps. I find using the & operator easier to type than the =concatenate() function: =concatenate(a1,a2,a3) becomes =a1&a2&a3 ppps. Open the sending workbook and create a formula in the receiving workbook that retrieves the value from that cell. Then close the sending workbook and you'll see the syntax for the string you want to use in the =indirect.ext() function. Your existing syntax isn't going to work. Michael wrote: I am attempting to concatenate a cell value (date) with a file name to create a data reference to a value within an Excel file. If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", "Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group! -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
The actual string is that I am working with is: =CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets") where A$3 = 10/7/08 The above string displays the file name correctly but doesn't retrieve the data. I added the indirect.exe function below to see if that would work and got "#REF!" in the cell: =INDIRECT.EXT("'F:\Documents\Daily Report 10-7-08.xls'SPallets") Then I added the cell reference to build the file name and got "#NAME?" =INDIRECT.EXE(CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets")) If type in the following into the cell the data is pulled from the file as expected: ='F:\Documents\Daily Report 10-7-08.xls'!SPallets Still beating my head against the wall...... "Peo Sjoblom" wrote: It works, of course you need to have a correct string within that function to make it work. How does your formula that returns an error look? -- Regards, Peo Sjoblom "Michael" wrote in message ... Dave, I must really be thick headed on this one. I downloaded and installed the function suite that includes the indirect.exe function. I did as you suggested (open the sending file, insert the formula and then close the sending file to get the correct path). I tried to use the indirect.ext function without attempting to build the file name and all I get is "#REF!" in the cell for a result. I don't really care if the workbook is open or closed (I can open workbooks as needed to update the cells). My problem seems to stem from the building the file name from a variable string that includes the date of the sending workbook. If I type in the file and path the cell value is updated from the referenced workbook. If I build the file name to include the date from the cell above, only the file name is displayed in the cell and the cell value is not pulled from the sending file. As far as I can see the the file reference is accurate but it just won't pull the data. Thanks in advance for your thoughts. Mike "Dave Peterson" wrote: The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. ==== ps, you can use this function for the date formatting: ....,text(a$3,"m-d-yy"),... pps. I find using the & operator easier to type than the =concatenate() function: =concatenate(a1,a2,a3) becomes =a1&a2&a3 ppps. Open the sending workbook and create a formula in the receiving workbook that retrieves the value from that cell. Then close the sending workbook and you'll see the syntax for the string you want to use in the =indirect.ext() function. Your existing syntax isn't going to work. Michael wrote: I am attempting to concatenate a cell value (date) with a file name to create a data reference to a value within an Excel file. If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", "Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group! -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
Watch how you build your formula.
This formula isn't correct: ='F:\Documents\Daily Report 10-7-08.xls'SPallets Try: ='F:\Documents\[Daily Report 10-7-08.xls]'SPallets (notice the []'s) Once you get that working, try: =indirect.ext("'F:\Documents\[Daily Report " & text(a3,"m-d-yy") & ".xls]'SPallets") and watch your typing. It's not indirect.exe Michael wrote: The actual string is that I am working with is: =CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets") where A$3 = 10/7/08 The above string displays the file name correctly but doesn't retrieve the data. I added the indirect.exe function below to see if that would work and got "#REF!" in the cell: =INDIRECT.EXT("'F:\Documents\Daily Report 10-7-08.xls'SPallets") Then I added the cell reference to build the file name and got "#NAME?" =INDIRECT.EXE(CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets")) If type in the following into the cell the data is pulled from the file as expected: ='F:\Documents\Daily Report 10-7-08.xls'!SPallets Still beating my head against the wall...... "Peo Sjoblom" wrote: It works, of course you need to have a correct string within that function to make it work. How does your formula that returns an error look? -- Regards, Peo Sjoblom "Michael" wrote in message ... Dave, I must really be thick headed on this one. I downloaded and installed the function suite that includes the indirect.exe function. I did as you suggested (open the sending file, insert the formula and then close the sending file to get the correct path). I tried to use the indirect.ext function without attempting to build the file name and all I get is "#REF!" in the cell for a result. I don't really care if the workbook is open or closed (I can open workbooks as needed to update the cells). My problem seems to stem from the building the file name from a variable string that includes the date of the sending workbook. If I type in the file and path the cell value is updated from the referenced workbook. If I build the file name to include the date from the cell above, only the file name is displayed in the cell and the cell value is not pulled from the sending file. As far as I can see the the file reference is accurate but it just won't pull the data. Thanks in advance for your thoughts. Mike "Dave Peterson" wrote: The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. ==== ps, you can use this function for the date formatting: ....,text(a$3,"m-d-yy"),... pps. I find using the & operator easier to type than the =concatenate() function: =concatenate(a1,a2,a3) becomes =a1&a2&a3 ppps. Open the sending workbook and create a formula in the receiving workbook that retrieves the value from that cell. Then close the sending workbook and you'll see the syntax for the string you want to use in the =indirect.ext() function. Your existing syntax isn't going to work. Michael wrote: I am attempting to concatenate a cell value (date) with a file name to create a data reference to a value within an Excel file. If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", "Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group! -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
For one thing remove the equal sign before the F hard drive letter.
Can I assume that this particular file is called Daily Report 10-7-08.xls and that it is in a folder called Documents ? and SPallets is the name of a single cell? This works for me =INDIRECT.EXT("'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&TEXT(A$3,"yy")&".xls'!SPallets") note that I don't use CONCATENATE since it makes it harder IMHO So to display just the string it would look like ="'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&TEXT(A$3,"yy")&".xls'!SPallets" Note that I changed one function, I removed the right part in this case it might not matter I find that it easier to use the TEXT function Using RIGHT and YEAR it would look like =INDIRECT.EXT("'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2)&".xls'!SPallets") single string ="'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2)&".xls'!SPallets" -- Regards, Peo Sjoblom "Michael" wrote in message ... The actual string is that I am working with is: =CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets") where A$3 = 10/7/08 The above string displays the file name correctly but doesn't retrieve the data. I added the indirect.exe function below to see if that would work and got "#REF!" in the cell: =INDIRECT.EXT("'F:\Documents\Daily Report 10-7-08.xls'SPallets") Then I added the cell reference to build the file name and got "#NAME?" =INDIRECT.EXE(CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets")) If type in the following into the cell the data is pulled from the file as expected: ='F:\Documents\Daily Report 10-7-08.xls'!SPallets Still beating my head against the wall...... "Peo Sjoblom" wrote: It works, of course you need to have a correct string within that function to make it work. How does your formula that returns an error look? -- Regards, Peo Sjoblom "Michael" wrote in message ... Dave, I must really be thick headed on this one. I downloaded and installed the function suite that includes the indirect.exe function. I did as you suggested (open the sending file, insert the formula and then close the sending file to get the correct path). I tried to use the indirect.ext function without attempting to build the file name and all I get is "#REF!" in the cell for a result. I don't really care if the workbook is open or closed (I can open workbooks as needed to update the cells). My problem seems to stem from the building the file name from a variable string that includes the date of the sending workbook. If I type in the file and path the cell value is updated from the referenced workbook. If I build the file name to include the date from the cell above, only the file name is displayed in the cell and the cell value is not pulled from the sending file. As far as I can see the the file reference is accurate but it just won't pull the data. Thanks in advance for your thoughts. Mike "Dave Peterson" wrote: The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. ==== ps, you can use this function for the date formatting: ....,text(a$3,"m-d-yy"),... pps. I find using the & operator easier to type than the =concatenate() function: =concatenate(a1,a2,a3) becomes =a1&a2&a3 ppps. Open the sending workbook and create a formula in the receiving workbook that retrieves the value from that cell. Then close the sending workbook and you'll see the syntax for the string you want to use in the =indirect.ext() function. Your existing syntax isn't going to work. Michael wrote: I am attempting to concatenate a cell value (date) with a file name to create a data reference to a value within an Excel file. If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", "Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group! -- Dave Peterson |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
Peo,
I don't know how to thank you but it worked! I was trying different combinations of your suggestions and this one finally pulled the data: =INDIRECT.EXT("'F:\Documents\Daily Report "&MONTH(D$3)&"-"&DAY(D$3)&"-"&TEXT(D$3,"yy")&".xls'!SPallets") This one only displayed the file name: ="'F:\Documents\Daily Report "&MONTH(D$3)&"-"&DAY(D$3)&"-"&TEXT(D$3,"yy")&".xls'!SPallets" and this one showed "#NAME?" in the cell" ='F:\Documents\Daily Report "&MONTH(D$3)&"-"&DAY(D$3)&"-"&TEXT(D$3,"yy")&".xls'!SPallets I thought I was copying everything that you suggested but I must have entered something wrong. Thank you for your help! Mike "Peo Sjoblom" wrote: For one thing remove the equal sign before the F hard drive letter. Can I assume that this particular file is called Daily Report 10-7-08.xls and that it is in a folder called Documents ? and SPallets is the name of a single cell? This works for me =INDIRECT.EXT("'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&TEXT(A$3,"yy")&".xls'!SPallets") note that I don't use CONCATENATE since it makes it harder IMHO So to display just the string it would look like ="'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&TEXT(A$3,"yy")&".xls'!SPallets" Note that I changed one function, I removed the right part in this case it might not matter I find that it easier to use the TEXT function Using RIGHT and YEAR it would look like =INDIRECT.EXT("'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2)&".xls'!SPallets") single string ="'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2)&".xls'!SPallets" -- Regards, Peo Sjoblom "Michael" wrote in message ... The actual string is that I am working with is: =CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets") where A$3 = 10/7/08 The above string displays the file name correctly but doesn't retrieve the data. I added the indirect.exe function below to see if that would work and got "#REF!" in the cell: =INDIRECT.EXT("'F:\Documents\Daily Report 10-7-08.xls'SPallets") Then I added the cell reference to build the file name and got "#NAME?" =INDIRECT.EXE(CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets")) If type in the following into the cell the data is pulled from the file as expected: ='F:\Documents\Daily Report 10-7-08.xls'!SPallets Still beating my head against the wall...... "Peo Sjoblom" wrote: It works, of course you need to have a correct string within that function to make it work. How does your formula that returns an error look? -- Regards, Peo Sjoblom "Michael" wrote in message ... Dave, I must really be thick headed on this one. I downloaded and installed the function suite that includes the indirect.exe function. I did as you suggested (open the sending file, insert the formula and then close the sending file to get the correct path). I tried to use the indirect.ext function without attempting to build the file name and all I get is "#REF!" in the cell for a result. I don't really care if the workbook is open or closed (I can open workbooks as needed to update the cells). My problem seems to stem from the building the file name from a variable string that includes the date of the sending workbook. If I type in the file and path the cell value is updated from the referenced workbook. If I build the file name to include the date from the cell above, only the file name is displayed in the cell and the cell value is not pulled from the sending file. As far as I can see the the file reference is accurate but it just won't pull the data. Thanks in advance for your thoughts. Mike "Dave Peterson" wrote: The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. ==== ps, you can use this function for the date formatting: ....,text(a$3,"m-d-yy"),... pps. I find using the & operator easier to type than the =concatenate() function: =concatenate(a1,a2,a3) becomes =a1&a2&a3 ppps. Open the sending workbook and create a formula in the receiving workbook that retrieves the value from that cell. Then close the sending workbook and you'll see the syntax for the string you want to use in the =indirect.ext() function. Your existing syntax isn't going to work. Michael wrote: I am attempting to concatenate a cell value (date) with a file name to create a data reference to a value within an Excel file. If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", "Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group! -- Dave Peterson |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
Peo, One final comment. When I transitioned from my test files to my actual file references I noticed that there is a limitation as to how many sub directories the function will navigate. My test case was one and my actual files are buried seven levels deep in my file system. I tried to use a shortcut link as a pointer but that didn't work either. If my files were only 5 levels deep the syntax worked fine. Perhaps that was my problem all along. Do you know of this limitation in pointing to sub directories in a file system? Mike "Peo Sjoblom" wrote: For one thing remove the equal sign before the F hard drive letter. Can I assume that this particular file is called Daily Report 10-7-08.xls and that it is in a folder called Documents ? and SPallets is the name of a single cell? This works for me =INDIRECT.EXT("'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&TEXT(A$3,"yy")&".xls'!SPallets") note that I don't use CONCATENATE since it makes it harder IMHO So to display just the string it would look like ="'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&TEXT(A$3,"yy")&".xls'!SPallets" Note that I changed one function, I removed the right part in this case it might not matter I find that it easier to use the TEXT function Using RIGHT and YEAR it would look like =INDIRECT.EXT("'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2)&".xls'!SPallets") single string ="'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2)&".xls'!SPallets" -- Regards, Peo Sjoblom "Michael" wrote in message ... The actual string is that I am working with is: =CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets") where A$3 = 10/7/08 The above string displays the file name correctly but doesn't retrieve the data. I added the indirect.exe function below to see if that would work and got "#REF!" in the cell: =INDIRECT.EXT("'F:\Documents\Daily Report 10-7-08.xls'SPallets") Then I added the cell reference to build the file name and got "#NAME?" =INDIRECT.EXE(CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets")) If type in the following into the cell the data is pulled from the file as expected: ='F:\Documents\Daily Report 10-7-08.xls'!SPallets Still beating my head against the wall...... "Peo Sjoblom" wrote: It works, of course you need to have a correct string within that function to make it work. How does your formula that returns an error look? -- Regards, Peo Sjoblom "Michael" wrote in message ... Dave, I must really be thick headed on this one. I downloaded and installed the function suite that includes the indirect.exe function. I did as you suggested (open the sending file, insert the formula and then close the sending file to get the correct path). I tried to use the indirect.ext function without attempting to build the file name and all I get is "#REF!" in the cell for a result. I don't really care if the workbook is open or closed (I can open workbooks as needed to update the cells). My problem seems to stem from the building the file name from a variable string that includes the date of the sending workbook. If I type in the file and path the cell value is updated from the referenced workbook. If I build the file name to include the date from the cell above, only the file name is displayed in the cell and the cell value is not pulled from the sending file. As far as I can see the the file reference is accurate but it just won't pull the data. Thanks in advance for your thoughts. Mike "Dave Peterson" wrote: The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. ==== ps, you can use this function for the date formatting: ....,text(a$3,"m-d-yy"),... pps. I find using the & operator easier to type than the =concatenate() function: =concatenate(a1,a2,a3) becomes =a1&a2&a3 ppps. Open the sending workbook and create a formula in the receiving workbook that retrieves the value from that cell. Then close the sending workbook and you'll see the syntax for the string you want to use in the =indirect.ext() function. Your existing syntax isn't going to work. Michael wrote: I am attempting to concatenate a cell value (date) with a file name to create a data reference to a value within an Excel file. If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", "Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group! -- Dave Peterson |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a file by concatenating cell variables
A formula can be 1024 characters long, is it possible
that you hit this limit? Besides that I don't know what limits this particular function has. I rarely use these functions mainly because I don't have any need for them. -- Regards, Peo Sjoblom "Michael" wrote in message ... Peo, One final comment. When I transitioned from my test files to my actual file references I noticed that there is a limitation as to how many sub directories the function will navigate. My test case was one and my actual files are buried seven levels deep in my file system. I tried to use a shortcut link as a pointer but that didn't work either. If my files were only 5 levels deep the syntax worked fine. Perhaps that was my problem all along. Do you know of this limitation in pointing to sub directories in a file system? Mike "Peo Sjoblom" wrote: For one thing remove the equal sign before the F hard drive letter. Can I assume that this particular file is called Daily Report 10-7-08.xls and that it is in a folder called Documents ? and SPallets is the name of a single cell? This works for me =INDIRECT.EXT("'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&TEXT(A$3,"yy")&".xls'!SPallets") note that I don't use CONCATENATE since it makes it harder IMHO So to display just the string it would look like ="'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&TEXT(A$3,"yy")&".xls'!SPallets" Note that I changed one function, I removed the right part in this case it might not matter I find that it easier to use the TEXT function Using RIGHT and YEAR it would look like =INDIRECT.EXT("'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2)&".xls'!SPallets") single string ="'F:\Documents\Daily Report "&MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2)&".xls'!SPallets" -- Regards, Peo Sjoblom "Michael" wrote in message ... The actual string is that I am working with is: =CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets") where A$3 = 10/7/08 The above string displays the file name correctly but doesn't retrieve the data. I added the indirect.exe function below to see if that would work and got "#REF!" in the cell: =INDIRECT.EXT("'F:\Documents\Daily Report 10-7-08.xls'SPallets") Then I added the cell reference to build the file name and got "#NAME?" =INDIRECT.EXE(CONCATENATE("='F:\Documents\Daily Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SPallets")) If type in the following into the cell the data is pulled from the file as expected: ='F:\Documents\Daily Report 10-7-08.xls'!SPallets Still beating my head against the wall...... "Peo Sjoblom" wrote: It works, of course you need to have a correct string within that function to make it work. How does your formula that returns an error look? -- Regards, Peo Sjoblom "Michael" wrote in message ... Dave, I must really be thick headed on this one. I downloaded and installed the function suite that includes the indirect.exe function. I did as you suggested (open the sending file, insert the formula and then close the sending file to get the correct path). I tried to use the indirect.ext function without attempting to build the file name and all I get is "#REF!" in the cell for a result. I don't really care if the workbook is open or closed (I can open workbooks as needed to update the cells). My problem seems to stem from the building the file name from a variable string that includes the date of the sending workbook. If I type in the file and path the cell value is updated from the referenced workbook. If I build the file name to include the date from the cell above, only the file name is displayed in the cell and the cell value is not pulled from the sending file. As far as I can see the the file reference is accurate but it just won't pull the data. Thanks in advance for your thoughts. Mike "Dave Peterson" wrote: The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. ==== ps, you can use this function for the date formatting: ....,text(a$3,"m-d-yy"),... pps. I find using the & operator easier to type than the =concatenate() function: =concatenate(a1,a2,a3) becomes =a1&a2&a3 ppps. Open the sending workbook and create a formula in the receiving workbook that retrieves the value from that cell. Then close the sending workbook and you'll see the syntax for the string you want to use in the =indirect.ext() function. Your existing syntax isn't going to work. Michael wrote: I am attempting to concatenate a cell value (date) with a file name to create a data reference to a value within an Excel file. If I use the absolute file name the function works but if I build the file name by concatenating the date the only thing the spreadsheet does is display the file name an not capture the referenced data cell. The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08, 2/4/08, 2/5/08, etc. The formula in cells B3, B4, B5, B6, etc. is: =CONTATENATE("='C:\ConsolPeriod Report ",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData") Where the reports are stored as "Period Report 2-2-08", "Period Report 2-3-08", "Period Report 2-4-08", etc. Any suggestions on how to get the formula to grab the data in the cell named "SData" in each of the reports instead of just displaying the name of the report? Thanks for the brain power of the group! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax for using variables in a cell formula to reference paths/fi | Excel Worksheet Functions | |||
Building a reference by concatenating components | Excel Discussion (Misc queries) | |||
Vary variables in a formula via reference to another cell | Excel Discussion (Misc queries) | |||
Concatenating cells to produce a cell ref from another excel file | Excel Worksheet Functions | |||
Creating Linked Workbook file name using cell variables | Excel Worksheet Functions |