Hi Experts,
I'm seeking some inspiration to a project I'm doing.
On the link at the bottom, I've created a sample file, so I better can explain my challenge.
I'm building a customer quotation tool. The end-user should be able to just put in a customer- and item number (marked with green cells).
In the yellow cells, a rebate should automatically be found in the table "fRebates".
In the table "tRebateModel" criteria's and priorities are stored and used for finding correct rebate.
E.g. Priority 1 says "CUNO" and "ITNO". It should then run through the rebate table, to se if there is a hit on Customer- and Item Number and then return the rebate. If there is no hit, then it should check if there is a hit on priority 2 and so on, all the way to priority 10.
My own thoughts, if possible:
- It would be nice if it automatically could find the needed fields, in case the criteria's in the rebate model will change. I'm thinking it might be possible with naming cells and use INDIRECT()
- A "smooth" solution would be preferable, instead of a lot of IFERROR(), which I think should be able to do the trick, in worst case.
Hoping someone can help with a solution, or point in a direction to make a smooth solution.
https://wetransfer.com/downloads/0b1ab6412c4ddbb0efed31d71f2acc1c20220928091620/e9fcfbdfa3b0ef3981238b74e0063a8520220928091620/330570
/Skovgaard
I'm seeking some inspiration to a project I'm doing.
On the link at the bottom, I've created a sample file, so I better can explain my challenge.
I'm building a customer quotation tool. The end-user should be able to just put in a customer- and item number (marked with green cells).
In the yellow cells, a rebate should automatically be found in the table "fRebates".
In the table "tRebateModel" criteria's and priorities are stored and used for finding correct rebate.
E.g. Priority 1 says "CUNO" and "ITNO". It should then run through the rebate table, to se if there is a hit on Customer- and Item Number and then return the rebate. If there is no hit, then it should check if there is a hit on priority 2 and so on, all the way to priority 10.
My own thoughts, if possible:
- It would be nice if it automatically could find the needed fields, in case the criteria's in the rebate model will change. I'm thinking it might be possible with naming cells and use INDIRECT()
- A "smooth" solution would be preferable, instead of a lot of IFERROR(), which I think should be able to do the trick, in worst case.
Hoping someone can help with a solution, or point in a direction to make a smooth solution.
https://wetransfer.com/downloads/0b1ab6412c4ddbb0efed31d71f2acc1c20220928091620/e9fcfbdfa3b0ef3981238b74e0063a8520220928091620/330570
/Skovgaard