Paste Special (or some other technique)

[Gina_Marano]

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

 

[Don Guillett]

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Jason Smith]

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

 

[Don Guillett]

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

 

[Dave Peterson]

One more:

=hyperlink("File:////" & $b$1 & "\" & a2)

(spaces added just to make it easier to read.)