If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Out of Ideas, need help with Auto Fill
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Auto fill color change | Dave | General Discussion | 4 | June 15th, 2005 05:45 PM |
auto fill | Art | Using Forms | 1 | April 23rd, 2005 05:27 PM |
"auto fill" of field when typing into table | Russell-stanely | General Discussion | 1 | February 15th, 2005 05:27 PM |
How to auto fill info. from a table to a form? | pennylj | Using Forms | 1 | August 31st, 2004 07:27 PM |
Auto fill criteria/questions on filling formulas down | JAnderson | General Discussion | 2 | July 19th, 2004 09:45 PM |