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!

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!

Since I am working with separate workbooks [Denise Cothern_Dealer Profile
Sheet.xls] is a detailed view of the Account/Lead and then I have a Master
Pipeline Tool that I am writing this Formula on. Also, the Cell I'm starting
in is A25 and it's merged with A26, A27 and A28, does that make any
difference?

Thanks!

"RagDyeR" wrote:

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!

This formula will *not* work with merged cells, since the row reference sets
the sheet reference, and copying down within merged cells will "skip" the
intended consecutive references.

Also, Indirect will *not* work on closed WBs.

Don't know what a "Master Pipeline Tool" is.

See if this link can help with referencing closed WBs.

http://tinyurl.com/2c62u
--
HTH,

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




"acctexecutive" wrote in message
...
Since I am working with separate workbooks [Denise Cothern_Dealer Profile
Sheet.xls] is a detailed view of the Account/Lead and then I have a Master
Pipeline Tool that I am writing this Formula on. Also, the Cell I'm
starting
in is A25 and it's merged with A26, A27 and A28, does that make any
difference?

Thanks!

"RagDyeR" wrote:

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!