PaulAsaran
New Member
- Joined
- Feb 11, 2019
- Messages
- 22
I'm currently working on a project intended to simplify the user interface for a table. This table can run in the hundreds of rows and as-is these need to be entered manually line-by-line. We want to create a system in which the table will fill most of its lines based on the information of a smaller initial table.
The smaller table has a column of ascending values and appropriate known data for those values:
<tbody>
</tbody>
What I want is for the new table to have built-in calculations that build the values based on the above table. The key element is to have each table iterate its values based upon what is seen above:
<tbody>
</tbody>
Right now I plan to use a simple VLOOKUP function to determine iterations and data. The problem I'm running into is getting the iterations into the first column. Row B needs to look at Row A and know where that value falls in the first table (i.e., 300 is between 0 and 500, so iterate by 100 and raise the data by 3; 600 is between 500 and 1000, so iterate by 50 and don't raise the data). I could use a series of IF statements to make the comparison, but the first table is going to be 20 rows long. That's a lot of IF/VLOOKUP statements and tons of room for error, plus updating it in the future would be a chore. Also bear in mind that the values in the first table will change from project to project, so the conditions in the statement need to be cell-based, not constant.
Is there a simple method to do this without a wall of IF statements?
The smaller table has a column of ascending values and appropriate known data for those values:
From | Iteration | Data |
0 | 100 | 3 |
500 | 50 | 0 |
1000 | 100 | 3 |
<tbody>
</tbody>
What I want is for the new table to have built-in calculations that build the values based on the above table. The key element is to have each table iterate its values based upon what is seen above:
Distance | Data |
0 | 3 |
100 | 6 |
200 | 9 |
300 | 12 |
400 | 15 |
500 | 18 |
550 | 18 |
600 | 18 |
650 | 18 |
<tbody>
</tbody>
Right now I plan to use a simple VLOOKUP function to determine iterations and data. The problem I'm running into is getting the iterations into the first column. Row B needs to look at Row A and know where that value falls in the first table (i.e., 300 is between 0 and 500, so iterate by 100 and raise the data by 3; 600 is between 500 and 1000, so iterate by 50 and don't raise the data). I could use a series of IF statements to make the comparison, but the first table is going to be 20 rows long. That's a lot of IF/VLOOKUP statements and tons of room for error, plus updating it in the future would be a chore. Also bear in mind that the values in the first table will change from project to project, so the conditions in the statement need to be cell-based, not constant.
Is there a simple method to do this without a wall of IF statements?