|
refering to specific cell in the formula
='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]1'!$A$1
this is a formula in the specific cell but i have more than 150 files.xlsx and i have to merge it in one sheet can any one give me a way to make this idea possiable a b c d e 1 1xlsx 2.xlsx 3.xlsx 4.xlsx 5.xlsx 2 ='C:\Documents and Settings\VENUS\Desktop\[A1]1'!$A$1 3 ='C:\Documents and Settings\VENUS\Desktop\[B21]1'!$A$1 4 ='C:\Documents and Settings\VENUS\Desktop\[B3]1'!$A$1 that mean i will refer the name of the file in the furmula to the cell A1 B1 C1 etc. i have tried many ways but it doesn't run so kindly and expert tell me the way to do that with the file name and the sheet that i give (1) for it names also in the same way |
refering to specific cell in the formula
Try this:
Enter this formula in A2: ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1" Copy across as needed. This will result is a TEXT string that looks like a formula. ='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]'!$A$1 Select all the cells that hold these formulas Goto EditCopy Then, EditPaste SpecialValues Then, EditReplace Find what: = Replace with: = Replace All That's correct. You want to replace the = with an =. -- Biff Microsoft Excel MVP "need a help" wrote in message ... ='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]1'!$A$1 this is a formula in the specific cell but i have more than 150 files.xlsx and i have to merge it in one sheet can any one give me a way to make this idea possiable a b c d e 1 1xlsx 2.xlsx 3.xlsx 4.xlsx 5.xlsx 2 ='C:\Documents and Settings\VENUS\Desktop\[A1]1'!$A$1 3 ='C:\Documents and Settings\VENUS\Desktop\[B21]1'!$A$1 4 ='C:\Documents and Settings\VENUS\Desktop\[B3]1'!$A$1 that mean i will refer the name of the file in the furmula to the cell A1 B1 C1 etc. i have tried many ways but it doesn't run so kindly and expert tell me the way to do that with the file name and the sheet that i give (1) for it names also in the same way |
refering to specific cell in the formula
Ooops!
I left out the sheet name. Enter this formula in A2: ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1" Formula should be: ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]1'!$A$1" -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: Enter this formula in A2: ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1" Copy across as needed. This will result is a TEXT string that looks like a formula. ='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]'!$A$1 Select all the cells that hold these formulas Goto EditCopy Then, EditPaste SpecialValues Then, EditReplace Find what: = Replace with: = Replace All That's correct. You want to replace the = with an =. -- Biff Microsoft Excel MVP "need a help" wrote in message ... ='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]1'!$A$1 this is a formula in the specific cell but i have more than 150 files.xlsx and i have to merge it in one sheet can any one give me a way to make this idea possiable a b c d e 1 1xlsx 2.xlsx 3.xlsx 4.xlsx 5.xlsx 2 ='C:\Documents and Settings\VENUS\Desktop\[A1]1'!$A$1 3 ='C:\Documents and Settings\VENUS\Desktop\[B21]1'!$A$1 4 ='C:\Documents and Settings\VENUS\Desktop\[B3]1'!$A$1 that mean i will refer the name of the file in the furmula to the cell A1 B1 C1 etc. i have tried many ways but it doesn't run so kindly and expert tell me the way to do that with the file name and the sheet that i give (1) for it names also in the same way |
refering to specific cell in the formula
|
refering to specific cell in the formula
i have forgeten to tell you that i use excel 2007 is that mean something that
doesn't work with me it will come after i enter it in the cell like this ='C:\Documents and Settings\VENUS\Desktop\["&A1&"]1'!$A$1" as a text and without the ="" we enter in the first and the last do me a help make me a file and send it to me by email i will be thankfull for you my email pls do it for me "T. Valko" wrote: Well, it does work for me. This is something not easily demonstrated in a sample file since your referencing several other files. ??? -- Biff Microsoft Excel MVP "need a help" wrote in message ... i done with the sheet name before but it dosn't work can you send me an excel sheet with same you done to see how it will work great thanks for you "T. Valko" wrote: Ooops! I left out the sheet name. Enter this formula in A2: ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1" Formula should be: ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]1'!$A$1" -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: Enter this formula in A2: ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1" Copy across as needed. This will result is a TEXT string that looks like a formula. ='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]'!$A$1 Select all the cells that hold these formulas Goto EditCopy Then, EditPaste SpecialValues Then, EditReplace Find what: = Replace with: = Replace All That's correct. You want to replace the = with an =. -- Biff Microsoft Excel MVP "need a help" wrote in message ... ='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]1'!$A$1 this is a formula in the specific cell but i have more than 150 files.xlsx and i have to merge it in one sheet can any one give me a way to make this idea possiable a b c d e 1 1xlsx 2.xlsx 3.xlsx 4.xlsx 5.xlsx 2 ='C:\Documents and Settings\VENUS\Desktop\[A1]1'!$A$1 3 ='C:\Documents and Settings\VENUS\Desktop\[B21]1'!$A$1 4 ='C:\Documents and Settings\VENUS\Desktop\[B3]1'!$A$1 that mean i will refer the name of the file in the furmula to the cell A1 B1 C1 etc. i have tried many ways but it doesn't run so kindly and expert tell me the way to do that with the file name and the sheet that i give (1) for it names also in the same way |
refering to specific cell in the formula
i have forgeten to tell you that i use excel 2007
It doesn't matter. The only thing different is the location of the menu (ribbon) commands. EditCopy In Excel 2007: Right clickCopy EditPaste SpecialValues In Excel 2007: Right clickPaste SpecialValues EditReplace In Excel 2007: Home tabEditing groupFind & Select (the binocular icon)Replace A sample will not accomplish anything. The formulas would already be converted which defeats the purpose of the steps I've described. Trust me. This works, you just have to get the path correct. -- Biff Microsoft Excel MVP "need a help" wrote in message ... i have forgeten to tell you that i use excel 2007 is that mean something that doesn't work with me it will come after i enter it in the cell like this ='C:\Documents and Settings\VENUS\Desktop\["&A1&"]1'!$A$1" as a text and without the ="" we enter in the first and the last do me a help make me a file and send it to me by email i will be thankfull for you my email pls do it for me "T. Valko" wrote: Well, it does work for me. This is something not easily demonstrated in a sample file since your referencing several other files. ??? -- Biff Microsoft Excel MVP "need a help" wrote in message ... i done with the sheet name before but it dosn't work can you send me an excel sheet with same you done to see how it will work great thanks for you "T. Valko" wrote: Ooops! I left out the sheet name. Enter this formula in A2: ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1" Formula should be: ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]1'!$A$1" -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: Enter this formula in A2: ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1" Copy across as needed. This will result is a TEXT string that looks like a formula. ='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]'!$A$1 Select all the cells that hold these formulas Goto EditCopy Then, EditPaste SpecialValues Then, EditReplace Find what: = Replace with: = Replace All That's correct. You want to replace the = with an =. -- Biff Microsoft Excel MVP "need a help" wrote in message ... ='C:\Documents and Settings\VENUS\Desktop\[1.xlsx]1'!$A$1 this is a formula in the specific cell but i have more than 150 files.xlsx and i have to merge it in one sheet can any one give me a way to make this idea possiable a b c d e 1 1xlsx 2.xlsx 3.xlsx 4.xlsx 5.xlsx 2 ='C:\Documents and Settings\VENUS\Desktop\[A1]1'!$A$1 3 ='C:\Documents and Settings\VENUS\Desktop\[B21]1'!$A$1 4 ='C:\Documents and Settings\VENUS\Desktop\[B3]1'!$A$1 that mean i will refer the name of the file in the furmula to the cell A1 B1 C1 etc. i have tried many ways but it doesn't run so kindly and expert tell me the way to do that with the file name and the sheet that i give (1) for it names also in the same way |
| All times are GMT +1. The time now is 01:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com