Hopefully this is what you mean....
In Sheet2, In column J add the appropriate tax rates for each city code.
Then sort columns H:J by column H in ascending alphabetic order.
Now formula in D8 of sheet1:
=IF(ISNUMBER(MATCH(D1,Sheet2!$L$1:$L$5,0)),SUM(D56)*VLOOKUP(D1,Sheet2!$L$1:$M$13,2,0)+(SUMPRODUCT(LOOKUP(Table4[City Code],Cities,Sheet2!$I$1:$I$62),LOOKUP(Table4[City Code],Cities,Sheet2!$J$1:$J$62))),"")
Thank you again, I tried playing with this, but I am not getting the results I was looking for, I just think their are too many variables. So I am thinking a different approach may the way to go. I have attached an updated spread sheet, and here is my thought and I am hoping you can help. I am thinking series of Dynamic drop downs with different calculation options for each row. If you check sheet 2 I have added the possible situations and the tax rates that could be applied. So first drop down would be Yes/No (does this tax apply, sheet 2 column K), second drop down would be the different situations that the tax could be applied (Sheet 2 Column L), 3rd Drop down would be the tax rate that applies to the given situation and the forth would be for the I/B SQ tax, for when the tax rates on the SQ's are different (Sheet 2 Columns O and P).
So for each tax their are 4 or 5 possible ways that each tax could be applied,
RC TAX (Row 7) 13%14%,15%
1. Tax doesn't' apply to the reservation
2. Tax applies to everything (D4+D5+d6 x tax rate, show result D7)+(E4+E5+E6 x Tax rate, Show result in E7) + (F4+F5+F6 x Tax rate, show result in f7)
3. Tax applies to almost everything (D4+D5+E6 x tax rate, show result in D7)+(E4+E5+E6 x tax rate, show result in E7)+(F4+F5 x tax rate, show result in F7)
4. Tax apples I/B SQ only (F6 x tax rate, show result D7 and F7)
5. Tax rates are different (( D4+D5+E6 x tax rate 1) +( D4+D5+F6 x tax rate 2) add both totals into D7)+(E4+E5+E6 X O/B Tax rate 1, show results in E7) + (F4+F5+F6 X I/B Tax rate 2, show results in F7)
XG TAX (Row 8) 5%
1. Tax doesn't' apply
2. Tax applies to everything (D4+D5+D6 X Tax Rate, how result in D8) + (E4+E5+E6 X Tax rate, show result in E8) +(F4+F5+F6 X Tax rate, show result in F8)
3. Tax applies to almost everything (D4+D5+E6 x Tax Rate show result in D8)+ (E4+E5+E6 x tax rate, show result in E8)+(F4+F5 x tax rate, show result in F8)
4. Tax apples I/B SQ only (F6 x tax rate, show result D8 and F8)
XQ TAX (Row 9) 9.975%
1. Tax doesn't apply
2. Tax applies to everything (D4+D5+D6 x Tax Rate, show results in D9) + (E4+E5+E6 x Tax Rate, show result in E9)+ (F4+F5+F6 x Tax Rate, show result in F9)
3. Tax applies to almost everything (D4+D5+E6 x Tax Rate, show results in D9) + (E4+E5+E6 X Tax rate, show result in E9) + (F4+F5 x Tax Rate, show result in F9)
4. Tax applies to I/B SQ Only (F6 x Tax Rate, show result in D9 and F9)
So long story short, is there a way to do that?? Thank you for all your help, I will have to buy you a virtual beer!!