Copper Contributor

Hi,

I have a table that i'm entering figures into, i'd like to have the table reference a second table (ie: if i enter a value of 600, then that cell looks to the 'master table' and automatically takes the 600 value from that table. Is that possible ?

So when i put a figure in of say 1400 under Pressure PSI, it automatically looks to the chart on the right and populates the Torque and Estimate load cells from the chart to the right. Hope that makes sense !

2 Replies

# Re: Spreadsheet table auto populate

@JROZZ Formulas the provide default values are definitely doable, but there is a side effect.  See the attached workbook.

# Re: Spreadsheet table auto populate

Instead of reading the information from the existing list, there are two other options. With VLOOKUP() or XLOOKUP() you have the disadvantage that only the exact values that are present are found. You will not find intermediate values.

I have no idea how to convert PSI into the other values, but both seem to be linearly related. Therefore, there is also a formula with which you can calculate the values.

The factors appear to be PSI * 55.279 for Torque and PSI * 0.2377 for Load????

The second possibility with this linear relationship is to use the built-in FORECAST() function. This would look like this: (in my example, the value for PSI is in cell E3)

``````Torque
=IF(OR(E3="",E3<=0),#N/A,FORECAST(E3,{27639,99503},{500,1800}))