Formula help please

[Luaptar]

Hi,

Good Morning!
I need your help to tweak this formula. I want to create a drop-down list that when ever I chose EXPORT, the details used will be in EXPORT sheet. I have different set of formula for every sheet and now I wanted it to be done on a single sheet even if the references will be based on what I choose in the list.

I saw a post about vlookup indirect but I can't manage to include it to my formula.

=IF(B2<=20,INDEX(EXPORT!C20:L59,E4,E6),B2*INDEX(EXPORT!C62:L68,LOOKUP(B2,{0,45,71,100,300,500,1000},{1,2,3,4,5,6,7}),SUMIF(EXPORT!$C$61:$L$61,B5,EXPORT!$C$60:$L$60)))


I hope somebody can help me with this.

 

[sheetspread]

If A1 contains the list then:

=IF(B2<=20,INDEX(INDIRECT(A1&"!C20:L59"),E4,E6),B2*INDEX(INDIRECT(A1&"!C62:L68"),LOOKUP(B2,{0,45,71,100,300,500,1000},{1,2,3,4,5,6,7}),SUMIF(INDIRECT(A1&"!$C$61:$L$61"),B5,INDIRECT(A1&"!$C$60:$L$60"))))

(those relative references won't change unless you include an offset)

 

[Luaptar]

If A1 contains the list then:

=IF(B2<=20,INDEX(INDIRECT(A1&"!C20:L59"),E4,E6),B2*INDEX(INDIRECT(A1&"!C62:L68"),LOOKUP(B2,{0,45,71,100,300,500,1000},{1,2,3,4,5,6,7}),SUMIF(INDIRECT(A1&"!$C$61:$L$61"),B5,INDIRECT(A1&"!$C$60:$L$60"))))

(those relative references won't change unless you include an offset)

Hi Sheetspread!

Thanks for your assistance! It works out well!
hahaha

 

[Luaptar]

I'm sorry but I guess I missed out something. B2 value lower than 20 is not working. The formula only works for 21 and above value of Cell B2.
How can it be done?

 

[sheetspread]

It's difficult to be sure without seeing everything, but are you dragging the formula across and/or down? The references in quotes will not change (because of indirect), you would have to enclose each in an offset function.

 

[Luaptar]

The formula is in a single cell only. The index function for values <=20 is not working. Maybe this will help to compare and know what is wrong with the formula.

Original formula
=IF(B2<=20,INDEX(EXPORT!C20:L59,E4,E6),B2*INDEX(EXPORT!C62:L68,LOOKUP(B2,{0,45,71,100,300,500,1000},{1,2,3,4,5,6,7}),SUMIF(EXPO RT!$C$61:$L$61,B5,EXPORT!$C$60:$L$60)))

New formula
=IF(B2<=20,INDEX(INDIRECT(A1&"!C20:L59"),E4,E6),B2*INDEX(INDIRECT(A1&"!C62:L68"),LOOKUP(B2,{0,45,71,100,300,500,1000},{1,2,3,4,5,6,7}),SUMIF(INDIRECT(A1&"!$C$61:$L$61"),B5,INDIRECT(A1&"!$C$60:$L$60"))))

 

[sheetspread]

A1 contains the sheet name right?

 

[Luaptar]

Yes it does.

 

[sheetspread]

Can you show me the data?

 

[Luaptar]

I was able to tweak the formula and make it work.

Now, wanted to have 2 options. The first one is this formula of getting the details using indirect. now on those sheets I still have 2 rates which is for Document and Non Document. How can I make it work? what formula shall I use?

Export Sheet = Document Rate & Non document rate ** rates are completely different and is from difference cell range. It is also the same on the Import Sheet