not sure which function to use for this summary

[Guest]

I have 5 workooks that correspond to five business deals. Each deal has a
segment on "Capitalization" of the form

Source Available capital Take option rate grace amount
capitalized

some of the capital availabe for any given project comes from non-loan
sources. The ones that are loans are reported in a 6th worksheet, where an
if command pulls in the ones that have a positve interest rate.
"if(interest rate>0, ___, "")" is the outline of my if command, where ____ ->
whatever cell I want it to grab from the corresponding loan.

the problem I have is that, because some of the rows in the Capitalization
forms are non-loan, the if command leaves some blank rows when it summarizes
my outstanding loans.

More concretly, say one of the projects looks like this

Source Available capital Take option rate grace amount
capitalized
mom $20 yes 5% 12m
$20
bill $500 yes 4% 6m
$500
jane $400 no 17% 4m
$0
reserves $20,000 yes 0 0
$20,000
bank $1,500 yes 8% 12m
$1,500

the if command on my workbook designed to summarize my outstanding loans
would report

Source rate grace outstanding principal
mom 5% 12m $20
bill 4% 6m $500
jane 17% 4m $0

bank 8% 12m $1,500

where there is a blank row on the non-loan capitalization (reserves), and
the loan i didn't accept is still reported (albiet with a zero outstanding
principal)

is there any way that I can tell excel to search an array of potential
capital sources on another worksheet and report the ones that (A) I accept
and (B) to report them in such a way as to not have any blank spaces where
the non-loan capital was raised?

thank you for your help!
Evan

 

[Franz Verga]

I have 5 workooks that correspond to five business deals. Each deal
has a segment on "Capitalization" of the form

Source Available capital Take option rate grace
amount capitalized

some of the capital availabe for any given project comes from non-loan
sources. The ones that are loans are reported in a 6th worksheet,
where an if command pulls in the ones that have a positve interest
rate. "if(interest rate>0, ___, "")" is the outline of my if command,
where ____ -> whatever cell I want it to grab from the corresponding
loan.

the problem I have is that, because some of the rows in the
Capitalization forms are non-loan, the if command leaves some blank
rows when it summarizes my outstanding loans.

More concretly, say one of the projects looks like this

Source Available capital Take option rate grace
amount capitalized
mom $20 yes 5%
12m $20
bill $500 yes 4%
6m $500
jane $400 no 17%
4m $0
reserves $20,000 yes 0
0 $20,000
bank $1,500 yes 8%
12m $1,500

the if command on my workbook designed to summarize my outstanding
loans would report

Source rate grace outstanding principal
mom 5% 12m $20
bill 4% 6m $500
jane 17% 4m $0

bank 8% 12m $1,500

where there is a blank row on the non-loan capitalization (reserves),
and the loan i didn't accept is still reported (albiet with a zero
outstanding principal)

is there any way that I can tell excel to search an array of potential
capital sources on another worksheet and report the ones that (A) I
accept and (B) to report them in such a way as to not have any blank
spaces where the non-loan capital was raised?

thank you for your help!
Evan

Hi Evan,

I think it would be better if you could upload a small example file to
www.savefile.com


--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy