Out of Ideas, need help with Auto Fill-able to create a document

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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
message
> > news:[email protected]...
> > 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!
> >
> >
> >
> >