selection of correct sheet and price according to quantity.

[smisar]

Hello...

I have two price lists for the same items. Price as per sheet A and B.
On first page, if I select price list type A and fill the description, then the price column should refer & get correct price according to price list type and quantity.

Please refer sample sheet for your ease of understanding.

Please help me with right solution...

thanks in advance...

regards
Sanjay

 

[bosco_yip]

Try,

In "PL Sheet" E4, formula copy down :

=IF(B4="","",IFERROR(LOOKUP(D4,N(OFFSET(INDIRECT("'"&B4&"'!A$1"),MATCH(C4,INDIRECT("'"&B4&"'!A$2:A$51"),0),{1,4,7})),N(OFFSET(INDIRECT("'"&B4&"'!A$1"),MATCH(C4,INDIRECT("'"&B4&"'!A$2:A$51"),0),{2,5,8}))),"not in list"))

Regards
Bosco

 

[smisar]

Try,

In "PL Sheet" E4, formula copy down :

=IF(B4="","",IFERROR(LOOKUP(D4,N(OFFSET(INDIRECT("'"&B4&"'!A$1"),MATCH(C4,INDIRECT("'"&B4&"'!A$2:A$51"),0),{1,4,7})),N(OFFSET(INDIRECT("'"&B4&"'!A$1"),MATCH(C4,INDIRECT("'"&B4&"'!A$2:A$51"),0),{2,5,8}))),"not in list"))

Regards
Bosco

Hello Bosco,

Thanks. It works well...
Now I have one more small request...

When I am filling the data in description to get pricing Say "AS568-041 (90.92 x 1.78) V9000AA, when I start typing AS568-041, is it possible that rest of the description gets automatically filled in to avoid mistake?

Please try your best and help me...
regards
Sanjay...

 

[bosco_yip]

Hello Bosco,
..............When I am filling the data in description to get pricing Say "AS568-041 (90.92 x 1.78) V9000AA, when I start typing AS568-041, is it possible that rest of the description gets automatically filled in to avoid mistake?.............
regards
Sanjay...

Hi,

1] Try to use "Data Validation" to restrict the data entry or pick up data from the Dropdown List.

2] In "PL Sheet" select C4:C6 >> Insert >> Data Validation >>
Validation Criteria :

Allow, choose from the list : List

Source, enter :

=A!$A$2:$A$51

>> OK >> Finish

3] Please refer to the attachment.

Regards
Bosco

 

[smisar]

Great & Thanks, Bosco... I used it correctly... it works...

 

[smisar]

Hello Bosco,
Good Morning...

IS have one more small question...
I have added short description column C...
If I type AS-048 in C4, is it possible to have full description in D4?

 

[bosco_yip]

Hello Bosco,
Good Morning...

IS have one more small question...
I have added short description column C...
If I type AS-048 in C4, is it possible to have full description in D4?

Hi,

1] You can set-up a short/long description Lookup Table, as example adding in "Sheet A" column K and L.

2] Then, using Vlookup function to return the long description,

formula as in D4 copy down :

=IF(C4="","",VLOOKUP(C4,A!$K$2:$L$51,2,0))

In addition,

3] You can put wildcard inside the Price formula in respect of the Short Description,

formula as in F4 copy down :

=IF(B4="","",IFERROR(LOOKUP(E4,N(OFFSET(INDIRECT("'"&B4&"'!A$1"),MATCH(LEFT(C4,2)&"???"&RIGHT(C4,4)&"*",INDIRECT("'"&B4&"'!A$2:A$51"),0),{1,4,7})),N(OFFSET(INDIRECT("'"&B4&"'!A$1"),MATCH(LEFT(C4,2)&"???"&RIGHT(C4,4)&"*",INDIRECT("'"&B4&"'!A$2:A$51"),0),{2,5,8}))),"not in list"))

4] Please see attachment

Regards
Bosco

 

[smisar]

Thanks Bosco... I will try this... if need more help, I will revert...

 

[smisar]

HELLO BOSCO,

I HAVE ONE MORE SMALL ISSUE TO BE SOLVED.
WHEN F1 IS LOWER THAN MOQ (B51), THEN I WANT TO HAVE 220 (B51) AS ANSWER IN G1. THEN APPLY WILD CARD FORMULA CONSIDERING G1 AND GET PRICE... PLEASE HELP...

 

[bosco_yip]

HELLO BOSCO,

I HAVE ONE MORE SMALL ISSUE TO BE SOLVED.
WHEN F1 IS LOWER THAN MOQ (B51), THEN I WANT TO HAVE 220 (B51) AS ANSWER IN G1. THEN APPLY WILD CARD FORMULA CONSIDERING G1 AND GET PRICE... PLEASE HELP...

Hi,

Please read the forum rule :

Please Don't

• PLEASE DON'T SHOUT! (All Caps in the above thread = SHOUT!) We have big ears and will hear you just the same.

Always

• Please be respectful to all people, regardless of race, gender, sexuality or skill level.

p.s. Please click "Edit" in the bottom line of the thread and change your post lettering to normal style.

Regards
Bosco

 

[smisar]

Hello Mr. Bosco,

I have one more small issue to be solved.
When F1 is lower than MOQ (B51), then I want to have 220 (B51) as answer in G1. Then apply wild card formula considering G1 and get price. Please help...