Fill Down formula Help Please

[singlgl1]

I'm trying to fill the same formula (links) down a column on
worksheet.
I'm linking from the same cell from a months worth of files,only th
file reference is changing as shown below.(11-1-05,11-2-05,etc..)

Cell A1='C:\Reports\Nov\[11-1-05.xls]Production'!$Z$1
CellA2='C:\Reports\Nov\[11-2-05.xls]Production'!$Z$1
CellA3 ='C:\Reports\Nov\[11-3-05.xls]Production'!$Z$1

Thanks, Gre

 

[pinmaster]

Hi,
Try this:
In a helper column starting in row 1 type this:
="'C:\Reports\Nov\[11-"&ROW()&"-05.xls]Production'!$Z$1"
and copy down as needed.
then for your formula use:
=INDIRECT(B1)
B1 being your helper column

HTH
J

 

[Biff]

Hi!

The only way to do what you want requires that each of those files be open.
You don't want all those files to be open do you?

Biff

 

[singlgl1]

No, I can't open all of the files at once so I guess that's ruled out. I
Guess I could fill down as usual, then edit each formula to reflect the
file that I need referenced.I was hoping there was an easier way to
accomplish this.Thanks for any help on this

 

[Ragdyer]

The *easy* way is to use Indirect(), but that has it's shortcomings.
You must have all WBs *open* to use the data.

It'll take a little work in order to be able to create the formulas (links)
necessary so that you can access the data and *not* have to have the other
WBs *open*.
You'll start with a Text formula so it can increment, and then convert it to
a real formula to calculate (return data).

Start with an out of the way "helper" column, say AA.

Enter this formula in the row where you would like your data to start to
display in the main portion of your sheet, say AA20:

="='C:\Reports\Nov\[11-"&ROW(1:1)&"-05.xls]Production'!$Z$1"

Don't be concerned that what you see in the formula bar does *not* match
what you see in the cell.

Now, click the fill handle and drag down to copy as far as needed.

You'll see that the cells display your incremented WB names.

NOW, while all the cells are *still* selected from the copy, right click in
the selection and choose "Copy".

Navigate to the main portion of your sheet and *right* click in the first
cell that you wish to use for the data display and click on "Paste Special".
Click on "Values", then <OK>.

AGAIN, while the cells are *still* selected from the Paste Special, click on
<Edit> <Replace>
And in the "Find What" box enter
*=
And in the "Replace With" box enter
=
Then click "Replace All".

You should now have your links established where you can access your data
from open or closed WBs, as long as you update your links whenever you open
this main sheet.

 

[singlgl1]

Thanks ragdyer, your suggestion worked great!!

 

[Ragdyer]

Appreciate the feed-back.