Try this:

=IF(INDIRECT("Lead"&ROW(A1)&"!F4")="","",INDIRECT( "Lead"&ROW(A1)&"!F4"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"acctexecutive" wrote in message
...
I work in the sales department for a computer software company and I'm
building a Territory Pipeline Tool. Currently we have 4 Inside reps that
support 9 Outside reps. That Inside team is designed to fill out a Lead
Sheet and pass it to the Outside rep. So I have one Workbook for each of
the
9 reps and each book contains 20 tabs labled Lead1, Lead2...etc. When the
lead is passed to the rep all the information on that sheet is automatically
linked to the Inside Reps Territory Pipeline Tool.

I was able to create a document which fills in the proper fields and
everything calculates just fine. What I'm looking to do is have the formula
listed below autofill down the master sheet and increase by Lead1,
Lead2..etc. I know I can do it by cell when I remove the $ around the F and
4. I've also tried formulas with Indirect and Index, but I could not get
them to work.

=IF('C:\Documents and Settings\cbass\Desktop\[Denise Cothern_Dealer Profile
Sheet.xls]Lead1'!$F$4="", "", 'C:\Documents and
Settings\cbass\Desktop\[Denise Cothern_Dealer Profile Sheet.xls]Lead1'!$F$4)

Please help me out, I'm at a loss!