Fill Down formula

[singlgl1]

I'm trying to fill the same formula (links) down a column on a worksheet.
I'm linking from the same cell from a months worth of files,only the 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, Greg

[pinmaster]

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

HTH
JG

[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" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to fill the same formula (links) down a column on a
> worksheet.
> I'm linking from the same cell from a months worth of files,only the
> 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, Greg
>
>
> --
> singlgl1
> ------------------------------------------------------------------------
> singlgl1's Profile:
> http://www.excelforum.com/member.php...o&userid=26389
> View this thread: http://www.excelforum.com/showthread...hreadid=478606
>

[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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"singlgl1" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to fill the same formula (links) down a column on a
> worksheet.
> I'm linking from the same cell from a months worth of files,only the
> 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, Greg
>
>
> --
> singlgl1
> ------------------------------------------------------------------------
> singlgl1's Profile:
http://www.excelforum.com/member.php...o&userid=26389
> View this thread: http://www.excelforum.com/showthread...hreadid=478606
>

[singlgl1]

Thanks ragdyer, your suggestion worked great!!

[Ragdyer]

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"singlgl1" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks ragdyer, your suggestion worked great!!
>
>
> --
> singlgl1
> ------------------------------------------------------------------------
> singlgl1's Profile:
> http://www.excelforum.com/member.php...o&userid=26389
> View this thread: http://www.excelforum.com/showthread...hreadid=478606
>

[dgentry]

I want to fill this formula down in 500 other cells:

=IF(C2=Sheet1!A2:A458,"complete","incomplete")
but, I only want the first cell reference to change with each row:

=IF(C2=Sheet1!A2:A458,"complete","incomplete")
=IF(C3=Sheet1!A2:A458,"complete","incomplete")
and so on...

Fill down tries to do this:

=IF(C2=Sheet1!A2:A458,"complete","incomplete")
=IF(C3=Sheet1!A3:A459,"complete","incomplete")
=IF(C4=Sheet1!A4:A460,"complete","incomplete")

Fill down changes the range that I am looking in to return a value if matched (complete) or no match(incomplete). Is there a way to keep this formula from changing the range when using Fill Down, or is there a much better formula altogether?

**edit** Here is what I am trying to accomplish:
I have a list of employee User IDs, and I am trying to use those values to check a report that will list the employee's ID if they completed a training. In short, I want to use the Employee ID to reference a range of IDs on another sheet. If the employee ID match is found, I want the cell to say "complete". If the employee ID was not found in the range, I want the cell to say "incomplete".

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then start your own thread.