# Excel template - need help with formula design and IF statement

I need some help with the attached template please.  Code/formulas required to:

1. Auto line count formula from cell A2 in the worksheet "Fund ref database"

2. In cell C66 of the "Fund Template", I need the fund limit to be programmed to automatically show 5%, 7% or 10% depending on if cell C26 has either "Money Market", "Fixed Income" or "Equity" selected respectively.  I cannot remember how to write the IF statement for this task.

Thanks.
Template-design.xls
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EngineerCommented:
Enter this in A2 and copy down to as far as required for the counting

=IF(D2="","",A1+1)

Use this for the values

=VLOOKUP(C26,{"Money Market",0.05;"Fixed Income",0.07;"Equity",0.1},2,0)
Commented:
2:
=IF(C26="Money Market";"5%";IF(C26="Fixed Income";"7%";IF(C26="Equity";"10%";"")))
Author Commented:
ssaqibh,
Both your suggestions work, thanks.  However for 1, can the formula be amended so that if one of the rows are deleted or a new one added, it automatically adjusts itself.

I should have made more clearer perhaps that the line count doesnt have to be dependent on col D the app reference.

Basically what I am trying to achieve (over many EE questions), is the ability to build a template with macros from a form when completed.  The Fund ref database worksheet will simply be the output of that result i.e. extra lines will be generated .  Of course I understand that th eline count needs a reference point in which to know when to stop counting so that makes it a little more tough.  How about a simple line count that when I drag the rows after inserting a new row, all the numbers remain sequential still?

ozzeczek,
Unfortunately your formula did not work.  It halts on the word Market in the formula.

Thanks.
Author Commented:
I forgot to also add, when the spreadsheet is first opened, cell C26 will be blank until an item is selected from the drop down.  Until then, the formula in C66 will display #N/A.  Until a value above is entered, can we show that as blank rather than #NA?
Commented:
Really? i copied it again to your .xnl and it works.
f you choose Money market c66 show 5%....
EngineerCommented:
=MAX(INDIRECT("A1:A"&ROW()-1))+1

=IF(ISERROR(VLOOKUP(C26,{"Money Market",0.05;"Fixed Income",0.07;"Equity",0.1},2,0)),"",VLOOKUP(C26,{"Money Market",0.05;"Fixed Income",0.07;"Equity",0.1},2,0))

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

EngineerCommented:
The reason why ozzeczek's solution does not work is that his system uses semicolons(;) instead of commas(,).

Change all the semicolons to commas and it will work.
Finance AnalystCommented:
To help to make future proof the lookup for the % rates could be linked to a small table of variables:

A                       B

Money Market  5%
Fixed Income    7%
Equity               10%

Then change the lookup to refer to the range rather than being hardcoded within the formula, assuming table above is A2 to B4:

=VLOOKUP(C26,\$A\$2:\$B\$4,2,FALSE)

Future changes can then be made simply by changing the value in the table.

To allow for C26 being blank, you can either use the IF(ISERROR(...) syntax as above or:

=IF(C26="","",VLOOKUP(C26,\$A\$2:\$B\$4,2,FALSE))

If using 2007 or later you can also use:

=IFERROR( formula , false result )

rather than having to use

=IF(ISERROR( formula ), false result , formula )

Thanks
Rob H
Author Commented:
Excellent, thanks this works now for both suggestions.

Please feel free to look at my next question which is an extension of this one:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27673211.html
Author Commented:
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.