Formula shows #value! error when source cell is empty

Please take a look at the attached and help me with several issues.

1. I want the totals in rows 7-11 to calculate even when a row does not have a SSN for a soldier keyed (such as rows 5-6). Take a look at cells AA5 and AB5 (and across to the right) where I have a #value! error and a small value, respectively. These values are interfering with the calculations in rows 7-11 and I cannot figure out how to make those cells work properly so that they do not have the error and remain blank when no SSN is keyed.

2. I need to explain column Z (the 1199 field). It is the sum of the soldiers Basic Allowance for Subsistence (BAS) (on sheet Paydata) and the soldiers BAH (on sheet BAH Table TN). Additionally, with it being the new year in JAN, the BAH and BAS gains a percentage increase which is keyed in B3 and C3, respectively.

You will notice that I have cells E3-H3 performing the individual calculations and in cell Z3 the sum(e3,h3) formula. I tried using the formula in cell Q3 to perform these calculations and the result was incorrect. Here is what I tried in cell Z3 that resulted in several hundred dollars more than the actual amount:

=IF(ISBLANK(D3),"",O3+(O3*$B$3))+(IF(LEFT(J3)="E",PayData!$D$30,IF(OR(LEFT(J3)={"O","W"}),PayData!$D$29,""))+(IF(LEFT(J3)="E",PayData!$D$30,IF(OR(LEFT(J3)={"O","W"}),PayData!$D$29,""))*$C$3))

3. You will notice on sheet PERS OCT 2012 that column M (TIS = Time In Service) is determining the number of years that a soldier has been in the military. This is important to determine his base pay amount (on sheet PayData) based upon his rank. Column N (Payrate), looks at column M and references range [paytable1] to determine the amount.

First, is there a better method (formula) to determine the soldiers base pay rate? If so, please share your thoughts as this rate is then pulled into the main sheet PTAE FY 13 Budget in cell P3 and Y3.

If not, I want to know a better, more functional method, for populating the months of NOV, DEC, and FEB through SEP with the data calculated in OCT and JAN. To explain, OCT is the new fiscal year and my budget planning begins there and will be the same for NOV and DEC. If a pay and allowances increase is passed by Congress, then in JAN those base amounts have to be recalculated with the percentages keyed into cells A3, B3,and C3. Then, the cells for FEB through SEP have to reflect the new amounts shown for JAN.

4. One final piece requires you to look at sheet PayData. You will notice that every two years a soldier will move into a new base pay amount and the spreadsheet currently does not take that into account. How do I make each individual month look at the soldiers TIS and if, at that month, the soldier moves into a new pay bracket make the new calculations? Then, every subsequent month will have to show the new amounts and not the amounts calculated in OCT and JAN alone.

So, lets eat this elephant one bite at a time... where would any of you smart people like to begin? First and foremost, thanks for your help.

Tony
=======================

What I would like to have is:

1. Cell Z to perform all of the calculations without the need for columns E-H.
2. When a soldiers SSN is not keyed in column I, I want the formulas across the sheet to remain empty (while keeping the formulas) so that the sheet totals can calculate.