Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste Special (or some other technique)
Hey Guys.
I want to paste a formula but I only want one of the cells to be dynamic. example (I am building a list of URLS) A B 1 RootDir c: 2 file1.txt =HYPERLINK(CONCATENATE(B1,"\",A2)) 3 file2.txt =HYPERLINK(CONCATENATE(B1,"\",A3)) 4 file3.txt I want the formual to alway refer to B1 and I want to iterate A2...A(N). I am trying to cheat here because I have a lot of these to do and don't want to keep having to edit the formula. Any thoughts? Gina_M |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste Special (or some other technique)
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste Special (or some other technique)
"Gina_Marano" wrote: Hey Guys. I want to paste a formula but I only want one of the cells to be dynamic. example (I am building a list of URLS) A B 1 RootDir c: 2 file1.txt =HYPERLINK(CONCATENATE(B1,"\",A2)) 3 file2.txt =HYPERLINK(CONCATENATE(B1,"\",A3)) 4 file3.txt I want the formual to alway refer to B1 and I want to iterate A2...A(N). I am trying to cheat here because I have a lot of these to do and don't want to keep having to edit the formula. Any thoughts? Gina_M Put a "$" in front of B and another one between "B" and "1". That will freeze B1 but allow your other reference to move around |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste Special (or some other technique)
Why not just type in the name of the file such as
file1 and try this by pasting into the sheet code module of the sheet where the filename is typed in. I use this from a menu page for .xls files Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.DisplayAlerts = False If ActiveCell.Value = "" Then Exit Sub workbookname = ActiveCell.Value On Error GoTo OpenWorkbook Windows(workbookname & ".txt").Activate Exit Sub OpenWorkbook: Workbooks.Open(workbookname & ".txt").RunAutoMacros xlAutoOpen Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Gina_Marano" wrote in message ... Hey Guys. I want to paste a formula but I only want one of the cells to be dynamic. example (I am building a list of URLS) A B 1 RootDir c: 2 file1.txt =HYPERLINK(CONCATENATE(B1,"\",A2)) 3 file2.txt =HYPERLINK(CONCATENATE(B1,"\",A3)) 4 file3.txt I want the formual to alway refer to B1 and I want to iterate A2...A(N). I am trying to cheat here because I have a lot of these to do and don't want to keep having to edit the formula. Any thoughts? Gina_M |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste Special (or some other technique)
One mo
=hyperlink("File:////" & $b$1 & "\" & a2) (spaces added just to make it easier to read.) Gina_Marano wrote: Hey Guys. I want to paste a formula but I only want one of the cells to be dynamic. example (I am building a list of URLS) A B 1 RootDir c: 2 file1.txt =HYPERLINK(CONCATENATE(B1,"\",A2)) 3 file2.txt =HYPERLINK(CONCATENATE(B1,"\",A3)) 4 file3.txt I want the formual to alway refer to B1 and I want to iterate A2...A(N). I am trying to cheat here because I have a lot of these to do and don't want to keep having to edit the formula. Any thoughts? Gina_M -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
'paste special', 'paste link' formatting transfer | Excel Discussion (Misc queries) | |||
PASTE LINK option not available when I select PASTE SPECIAL to link an image in Excel to a Word document. | Links and Linking in Excel | |||
In Excel: add a Paste-Special Option to paste IN REVERSE ORDER. | Excel Worksheet Functions | |||
Paste and Paste Special command are not enabled in Excel | Excel Worksheet Functions |