I'm creating a spreadsheet and need to find a way to change a formula based upon certain selection criteria. The user needs to select one of 4 options. The options are Class: 4, 5, 6 or 7.
Based upon the class selected, calculations further down are then required to look at different cell, the current spreadsheet has them all in order and moved down by a single cell. I'm using RC nomenclature in the spreadsheet.
The formula that I've come up with is as follows:
=(R[-259]C+R[-249 + vlookup(R[-394]C,Spline,2,FALSE)]C)/2
Now clearly I can't use this as it wont accept the equation are valid. Is there a way that this can be done. The spreadsheet can't use macro unfortunately. The vlookup would ideally be a block mover for the formula, so if the spline class is 5 rather than 4 then the formula is looking for cell that is 248 above rather than 249. I've had spent some time online and can't find a answer to my question, maybe its not possible or I'm not searching for the correct information.
For details, the table called "spline" can be seen below.
| Spline Class | Value |
|---|---|
| 4 | 0 |
| 5 | 1 |
| 6 | 2 |
| 7 | 3 |
I hope that makes sense?
Thanks.
