Formula Help: Find Value in 1st Row in Same Column, without using Absolute References

NeilKY

I'm trying to create a formula that gives me the value of the first row in the same column. [Find This Value].

There are a few obstacles:

1. The formula is placed in the same column, BELOW value we're trying to find,
   1. in [Find This Value]13:[Find This Value]21.
      1. So it needs to be a cell formula and not a column formula
      2. And my INDEX formula won't work on more than one row because I get a circular reference.
2. There is a form attached to the sheet and new entries are added to the top row.
   1. Thus absolute cell references don't work. [Find This Value]$1 becomes [Find This Value]$2 when the new row is added above it.
      
3. After I get a working formula, I need to use this same formula in MULTIPLE columns, so adding a helper column for each instance is not feasible.
   1. (I'll be looking up the first value in the next column, and the next, column, etc.)
4. Yes, it MUST reference the same column and not another one.
5. I have an automation that copies the rows with the formulas when they are changed by the values in the new row. So the formula created has to be an instantaneous calculation.
   1. So I do not want to use plugins like datamesh, data shuttle, etc.
6. Note: The data currently in rows 13:21 will not change (except for the formula values) and will get pushed down a row number each time a form is submitted.

Read-only HTML Version of this sheet: https://publish.smartsheet.com/85f97b3713e6487086a616dab7cf3d94

Here's an IMAGE of the sheet.

Here is sample data for you to copy to a sheet:

—

Auto Number MAX Auto Number Find This Value

21 =MAX([Auto Number]:[Auto Number]) Test A
20 =MAX([Auto Number]:[Auto Number]) Test D
19 =MAX([Auto Number]:[Auto Number]) Test C
18 =MAX([Auto Number]:[Auto Number]) Test B
17 =MAX([Auto Number]:[Auto Number]) Test A
16 =MAX([Auto Number]:[Auto Number]) Test D
15 =MAX([Auto Number]:[Auto Number]) Test C
14 =MAX([Auto Number]:[Auto Number]) Test B
13 =MAX([Auto Number]:[Auto Number]) Test A
12 =MAX([Auto Number]:[Auto Number]) Test A
11 =MAX([Auto Number]:[Auto Number]) Test C

9 =MAX([Auto Number]:[Auto Number])
8 =MAX([Auto Number]:[Auto Number])
7 =MAX([Auto Number]:[Auto Number])
6 =MAX([Auto Number]:[Auto Number])
5 =MAX([Auto Number]:[Auto Number])
4 =MAX([Auto Number]:[Auto Number])
3 =MAX([Auto Number]:[Auto Number])
2 =MAX([Auto Number]:[Auto Number])
1 =MAX([Auto Number]:[Auto Number])

Jason Tarpinian

I don't think that's possible to do with all of the parameters you've set. Your #5 mentions no premium apps, but is there a reason you can't build these formulas on a second sheet? You could then reference any ranges without worry or circular errors, the calculations are quick but not necessarily instantaneous, you could still set up the copy rows automation, and if you wanted to see the data in the same view, you could build a stacked report.