Autofill with multiple filenames

[someoneelse]

Hi

I've attached a sample spreadsheet with my problem included...

I need to fill a formula down hundreds of rows. Each cell refers to
different filename, and I don’t know how to increase the value of th
filename with one value each time without physically changing eac
value… Please help

Attachment filename: help1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=68377

 

[Arvi Laanemets]

Hi

When you need a reference to 'C:\My Documents\[MyFile1.xls]Sheet1'!A1 in
cell A2, reference to 'C:\My Documents\[MyFile2.xls]Sheet1'!A1 in cell A3
etc, then:
1. Into A2 enter the formula
="_='C:\My Documents\[MyFile" & ROW()-1 & ".xls]Sheet1'!A1"
Copy the formla down for as much rows as you have files.
Copy the range with formulas, and PasteSpecial values (be sure cell formats
are general)
Activate Replace window, enter _ into search field, and press ReplaceAll.

It's done

 

[someoneelse]

Thanks for your help, but I'm not getting it right. Could you use my th
values in attached help1.xls in your example? it gives me a REF erro
when doing the first step

 

[Arvi Laanemets]

Hi

Sorry, but I never visite ExcelForum. Better try in plain text here. A
couple of examples of manually entered working links, and an explanation
about file naming rules, will do.

You get an #REF error on first step? You have something entered wrong
there - on first step only 2 strings and a number are concantenated. Try it
step-by step:
C2="_='C:\My Documents\[MyFile"
it returns a string (_='C:\My Documents\[MyFile)
D2=ROW()-1
it returns number (1)
E2=".xls]Sheet1'!A1"
it returns a string (.xls]Sheet1'!A1)

Then into A2 enter
=C2 & D2 & E2
and after that replace cell references with according formulas.