| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i use multiple cells to create a formula ?
"Bernie Deitrick" wrote: Rob, One way is to create the referencing formulas with formulas, and then using a macro to convert to actual formulas. For example: In cell A2, put the folder path: C:\Excel\Folder\ In cell B2, put a filename Blah blah.xls In cell C2, put the sheetname: Sheet2 In cell D2, put the cell address: A2 in Cell E2, put the formula ="='"&A2 & "[" & B2 & "]" &C2 & "'!" &D2 You can do this for as many cell addresses as you need, or sheets, etc. with other columns, with one formula for each "link". Then copy those cells down as far as you need, and properly increment the filenames in column B. You can use a formula to do so, if they are regular enough - say, based on a date. Otherwise, you could use a macro to dump the names. Then select the cells in column E, and run this macro: Sub ConvStringToFormula() Dim myCell As Range For Each myCell In Selection myCell.Formula = myCell.Text Next End Sub HTH, Bernie MS Excel MVP "Rob Welsh (Austin)" <Rob Welsh wrote in message ... I am linking many different cells from over 100 seperate files. As the cells appear in the same location on each file I would like to know if I can join text in serveral cells to create an external link rather than going into every file and manually link each cell ? Thanks Rob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to copy and paste same formula in multiple cells? | Excel Worksheet Functions | |||
| apply a formula to multiple cells | Excel Discussion (Misc queries) | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| How do I protect formula cells on multiple sheets? | Excel Worksheet Functions | |||
| how do I create a formula to seperate name into two cells (last, . | Excel Discussion (Misc queries) | |||
Hybrid Mode
