Any help here would be appreciated. We have moved to the online subscription of Excel and my previous EVALUATE function doesn't seem to exist. I do not have any background in VBA, so I'm unsure of the possible solutions to my problem which appear in searches.
I have some very large sheet that has to be as automated as possible for dissemination to other analysts and our sales group. Keeping the sales people out of the formulas is pretty key. I'm trying to pull formulas that are saved separately on a different sheet and then run them with a value on a different table (example below). I can of course retrieve the text string but have not way to run that as a formula. Bear in mind that we will likely be pulling 4+ of these items so the values would be mixed. Any ideas? (Preferably without VBA, but I'm open to suggestions!)
Table 1:
Formula in Value:
=IFERROR(INDEX(Table2,MATCH([@FeeCode],Table2[Fee],0),MATCH([@Vendor],Table2[#Headers],0)),0)
<tbody>
</tbody>
Table2:
<tbody>
</tbody>
I'm sure I've missed something fantastically simple or easy here. Thanks in advance for any help you can give!
I have some very large sheet that has to be as automated as possible for dissemination to other analysts and our sales group. Keeping the sales people out of the formulas is pretty key. I'm trying to pull formulas that are saved separately on a different sheet and then run them with a value on a different table (example below). I can of course retrieve the text string but have not way to run that as a formula. Bear in mind that we will likely be pulling 4+ of these items so the values would be mixed. Any ideas? (Preferably without VBA, but I'm open to suggestions!)
Table 1:
Formula in Value:
=IFERROR(INDEX(Table2,MATCH([@FeeCode],Table2[Fee],0),MATCH([@Vendor],Table2[#Headers],0)),0)
Weight | Vendor | FeeCode | Value |
100 | V1 | Fee1 | |
250 | V2 | Fee2 | |
500 | V1 | Fee3 | |
1000 | V2 | Fee4 | |
1500 | V1 | Fee5 | |
2500 | V2 | Fee6 | |
5000 | V2 | Fee7 | |
7500 | V1 | Fee8 | |
9000 | V2 |
<tbody>
</tbody>
Table2:
Fee | V1 | V2 |
Fee1 | 75 | 50 |
Fee2 | MIN(MAX(([@Weight)/100)*5,50),250) | MIN(MAX(([@Weight)/100)*10,100),500) |
Fee3 | 50 | 25 |
Fee4 | MAX(([@Weight)/100)*10,100) | MIN(MAX(([@Weight)/100)*10,100),500) |
Fee5 | MIN(MAX(([@Weight)/100)*5,50),250) | MIN(MAX(([@Weight)/100)*10,100),500) |
Fee6 | 25 | 50 |
Fee7 | MAX(([@Weight)/100)*5,50) | MAX(([@Weight)/100)*5,50) |
Fee8 | 50 | 100 |
<tbody>
</tbody>
I'm sure I've missed something fantastically simple or easy here. Thanks in advance for any help you can give!