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

Paste Special (or some other technique)
 

--
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

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

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

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