Formula help

[Luaptar]

Hi,

Good Morning!
Need you assistance to tweak this formula to work.

Formula in CELL B8
=IF("DOCUMENT",(INDEX(INDIRECT(A1&"!C7:L16"),H5,H7)),IF(B3<=20,INDEX(INDIRECT(A1&"!C20:L59"),D5,D7),B3*INDEX(INDIRECT(A1&"!C62:L68"),LOOKUP(B3,{0,45,71,100,300,500,1000},{1,2,3,4,5,6,7}),SUMIF(INDIRECT(A1&"!$C$61:$L$61"),B6,INDIRECT(A1&"!$C$60:$L$60")))))

The highlighted formula is the new formula I added.


[B]Excel 2007[/B][TABLE]
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]EXPORT[/TD]
[TD="align: center"]SERVICE[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]NON-DOCUMENT[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]WEIGHT:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"] 15.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]DECLARED VALUE:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]DESTINATION:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"] China (EXCLUDE CHINA SOUTH) [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]ZONING:[/TD]
[TD="align: center"] Zone 2 [/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]EXCHANGE RATE:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"] ? 50.96 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]PUBLISHED RATE:[/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]DISCOUNT ON FREIGHT:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]50%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: right"]######[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]FREIGHT FEE:[/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]DECLARED VALUE SURCHARGE(INSURANCE)[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] ? - [/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]EXTENDED AREA SURCHARGE[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] ? - [/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]REMOTE AREA SURCHARGE[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] ? - [/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]RESIDENTIAL SURCHARGE[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] ? - [/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]SUBTOTAL:[/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]12% VAT:[/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]TOTAL[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]#VALUE![/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]#VALUE![/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]RATE CALCULATOR[/B][/COLOR][/CENTER]

 

[Scott Huish]

Not sure what you are trying to do but IF("DOCUMENT" isn't going to do anything, except evaluate to an error, there is no expression that will evaluate to TRUE/FALSE there.

 

[James006]

Hi,

Any comment on your previous question ... ???

https://www.mrexcel.com/forum/excel-questions/1041636-indirect-function-inquiry.html