Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested String Filename Variable
I have to make comparisons of data monthly with files whose data are in the
same positions within the worksheets, but whose values vary each month. I created a template with links that will read a cell in a particular file (i.e. ='[12-06 Ops Stmt.xls] Chicago"!$L$205) and if I manually copy and paste links I can create the comparisons I need. Every month the file name changes. I would like to create a cell in which I enter the new filename and modify the formulas so that the formulas in the cells pull that filename from that cell and populate my comparison sheet. I think I explained that right. Is it doable? Thanks. Ed |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested String Filename Variable
As long as the file is open, you could enter the filename in, say, cell
A1 and use: =INDIRECT("'[" & A1 & "] Chicago'!L205") In article , ed9213 wrote: I have to make comparisons of data monthly with files whose data are in the same positions within the worksheets, but whose values vary each month. I created a template with links that will read a cell in a particular file (i.e. ='[12-06 Ops Stmt.xls] Chicago"!$L$205) and if I manually copy and paste links I can create the comparisons I need. Every month the file name changes. I would like to create a cell in which I enter the new filename and modify the formulas so that the formulas in the cells pull that filename from that cell and populate my comparison sheet. I think I explained that right. Is it doable? Thanks. Ed |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested String Filename Variable
Thanks for the suggestion. It didn't work for me, though. The reference
file is open, but using that "INDIRECT" script returns an error in which A1 is blue. I surrounded the filename in A1 in quotes, and that didn't resolve it either. Ed "JE McGimpsey" wrote: As long as the file is open, you could enter the filename in, say, cell A1 and use: =INDIRECT("'[" & A1 & "] Chicago'!L205") In article , ed9213 wrote: I have to make comparisons of data monthly with files whose data are in the same positions within the worksheets, but whose values vary each month. I created a template with links that will read a cell in a particular file (i.e. ='[12-06 Ops Stmt.xls] Chicago"!$L$205) and if I manually copy and paste links I can create the comparisons I need. Every month the file name changes. I would like to create a cell in which I enter the new filename and modify the formulas so that the formulas in the cells pull that filename from that cell and populate my comparison sheet. I think I explained that right. Is it doable? Thanks. Ed |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested String Filename Variable
Works here - did you include ".xls" in the file name? Does your
worksheet name really start with 4 spaces? In article , ed9213 wrote: Thanks for the suggestion. It didn't work for me, though. The reference file is open, but using that "INDIRECT" script returns an error in which A1 is blue. I surrounded the filename in A1 in quotes, and that didn't resolve it either |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested String Filename Variable
Well, I did include the .xls in the filename in A1. I took the 4 spaces from
the format Excel uses when I do this: Go to a cell that i want to reference, click Copy, then paste a link to it in the comparison spreadsheet. The 4 spaces were part of the link formula. Thanks. "JE McGimpsey" wrote: Works here - did you include ".xls" in the file name? Does your worksheet name really start with 4 spaces? In article , ed9213 wrote: Thanks for the suggestion. It didn't work for me, though. The reference file is open, but using that "INDIRECT" script returns an error in which A1 is blue. I surrounded the filename in A1 in quotes, and that didn't resolve it either |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested String Filename Variable
Your question prompted me to search, and the sheet name does have spaces in
front of it, so the name would appear to slide right. It is a protected sheet, so I can't remove the spaces. The file name has many words, which is why I surrounded it with quotes, and tried parentheses, but with no success. I even renamed the file with a one-word filename, and that didn't help either. Thanks. "ed9213" wrote: Well, I did include the .xls in the filename in A1. I took the 4 spaces from the format Excel uses when I do this: Go to a cell that i want to reference, click Copy, then paste a link to it in the comparison spreadsheet. The 4 spaces were part of the link formula. Thanks. "JE McGimpsey" wrote: Works here - did you include ".xls" in the file name? Does your worksheet name really start with 4 spaces? In article , ed9213 wrote: Thanks for the suggestion. It didn't work for me, though. The reference file is open, but using that "INDIRECT" script returns an error in which A1 is blue. I surrounded the filename in A1 in quotes, and that didn't resolve it either |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested String Filename Variable
that would indicate that the spaces are part of the worksheet name. When
I set up two workbooks, one with a sheet named " Chicago". The filename in A1 should be exactly the filename, with extension, no quotes, no extra spaces. Like I said - works in my test books. What error do you get in the cell with the formula? In article , ed9213 wrote: Well, I did include the .xls in the filename in A1. I took the 4 spaces from the format Excel uses when I do this: Go to a cell that i want to reference, click Copy, then paste a link to it in the comparison spreadsheet. The 4 spaces were part of the link formula. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested String Filename Variable
The editor shows "INDIRECT(ref_test, [a1])" , and highlights the A1 in the
formula in blue, under the cell where the formula resides. I have tried to populate cell A1 with the filename alone, and with the full path, with no success. "JE McGimpsey" wrote: that would indicate that the spaces are part of the worksheet name. When I set up two workbooks, one with a sheet named " Chicago". The filename in A1 should be exactly the filename, with extension, no quotes, no extra spaces. Like I said - works in my test books. What error do you get in the cell with the formula? In article , ed9213 wrote: Well, I did include the .xls in the filename in A1. I took the 4 spaces from the format Excel uses when I do this: Go to a cell that i want to reference, click Copy, then paste a link to it in the comparison spreadsheet. The 4 spaces were part of the link formula. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested String Filename Variable
Not sure what "editor" you're referring to, nor what "ref_test" refers
to. INDIRECT takes two arguments - the first is the string comprising the reference, and the second is either TRUE (A1-style reference) or FALSE(R1C1-style reference). If there is no second argument, TRUE is assumed. You still don't say what "no success" or "doesn't work" means. Do you get a #REF error? A #VALUE! error? What is the exact entry you're making? In article , ed9213 wrote: The editor shows "INDIRECT(ref_test, [a1])" , and highlights the A1 in the formula in blue, under the cell where the formula resides. I have tried to populate cell A1 with the filename alone, and with the full path, with no success. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested String Filename Variable
The editor I mentioned is the one that Excel pops up to be helpful with
formula creation. When I typed the string you sent, a message box poped up letting me know that there was an error in my formula, and I could choose Help, or OK to continue, or if I wasn't really trying to write a formula, I should try again, or something like that. It also showed the syntax of the INDIRECT command, which is what I listed in my last message. A #REF error is left in the box, if I hit enter and Excel accepts it. Otherwise, it will not allow me to close the cell because there is an error, and usually it highlights the A1 part of the text in between the ampersands (& A1 &). I took that to mean the value in Cell A1, and I tried variations of the file name, with and without path, with and without single quotes, double quotes, parentheses, etc., the file was always open, and no success meant I never got the value of the cell I was naming to show up as the result of my formula. The only times that I have had the proper result was by copying and pasting a link. So, I said to myself, if the filename changes, why not just use CTRL-H and change all instances of the last filename to the new filename in the cells I had pasted the links. The result there was that a pop-up box appeared telling me that there was an incorrect value in a formula on this worksheet and I couldn't continue. I'm pretty sure this is a scenario that is a common one, and the only glitch should have been the possible padding of the worksheet names with spaces, but I created a test1.xls without spaces in the worksheet names, and that never worked either. I've had enough for tonight, and I'm going to sleep. Maybe something will be clearer tomorrow. Thanks for your help and for reading the tale of my frustration. Ed "JE McGimpsey" wrote: Not sure what "editor" you're referring to, nor what "ref_test" refers to. INDIRECT takes two arguments - the first is the string comprising the reference, and the second is either TRUE (A1-style reference) or FALSE(R1C1-style reference). If there is no second argument, TRUE is assumed. You still don't say what "no success" or "doesn't work" means. Do you get a #REF error? A #VALUE! error? What is the exact entry you're making? In article , ed9213 wrote: The editor shows "INDIRECT(ref_test, [a1])" , and highlights the A1 in the formula in blue, under the cell where the formula resides. I have tried to populate cell A1 with the filename alone, and with the full path, with no success. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help setting the worksheet header/Footer margins based on string height? | Excel Discussion (Misc queries) | |||
Entering a filename as a variable into cell similar as for footer | Excel Discussion (Misc queries) | |||
Browse File for Mac | Excel Discussion (Misc queries) | |||
Auto convert an alphanumeric string (CIS9638S) to numbers only? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |