Creating an exception price list

[RockDarkwater]

Hey all I need some genius,

I've been trying to find a way to swap an exception price with a generic item price for specific customers.
I have two large databases, one with item codes/descriptions/prices (CC8-3465, CC16- 3324, MSO2-1456, etc.), and one with customers (1002 - Customer 1, 3244 - Customer 2, etc.).

The generic item database is regularly updated through a copy and paste from an external program, so the formula can't be there.
I need to figure out a way to define a rule/rules for a discount/alternate item price i.e. OR(Right(itemcode, 4) = "3324", LEFT(itemcode, 2) = "CC") and then build another function that uses the alternate price instead of looking up the generic one when the rule returns TRUE.

I've tried using an EVALUATE UDF to change the text string into a function, but since the cell with the item code is dynamic, I've had to use an OFFSET or INDIRECT function, and the two don't mesh for some reason.

Any ideas?

 

[welshgasman]

I've done something similar.
I used a VLOOKUP to lookup a second column instead of the first column? That depended on your test like yours above.

HTH

 

[RockDarkwater]

I thought about that, but I would have to enter each alternate price on every applicable line (500+) for every customer. Every time the price list database updated I'd have to do it again.

 

[welshgasman]

I thought about that, but I would have to enter each alternate price on every applicable line (500+) for every customer. Every time the price list database updated I'd have to do it again.

Wouldn't the alternative price change anyway?

 

[RockDarkwater]

Wouldn't the alternative price change anyway?

The issue isn't the prices changing, it's that quantity and order of the items changes and the row that they're on doesn't necessarily stay the same. Every line of exception prices would have to be double checked every time it updated and the potential for error would be too great.

 

[welshgasman]

So what if you held the exception prices in a separate table, using the item as a key.?
That way it would not matter what order they were in?
Also you would only need to change the exception prices that need to be changed?, some might stay the same?

I must admit I'd approach this from a database aspect.