# INDEX row_num is not updating when adding a row to a named range

I created 2 sheets. One named Config and one named Data.

I have a named range `Config` for cells A2:C10.
In Data, my formula is `=INDEX(Config,5,2)`

I noticed if I add a row between row 3 and 4, the INDEX row_num (here `5`) doesn’t auto-fix itself like normal formulas. It stays `5` instead of changing for`6`.

Is this expected behaviour? I really hope there is something here I can do
Is there a way to help me fix the formulas that could be affected by this change?

Have a great day people and thanks for sharing your knowledge.

It will be scary if the number 5 suddenly turns into 6.
Try something like Row(A5).

1 Like

If you would reference the cell directly (=B6), the reference would follow the shifting cell. Your INDEX formula is a work-around when you don’t want to follow shifting references.

Ohhhh, yeahh… It’s quite an old document, so I mostly just transcribed everything over while making sure formulas were ok and up to date. Didn’t think for a second about why it was using INDEX and why I shouldn’t

Oh, I encountered a problem while thinking about replacing all the `INDEX` with direct references.

I see a lot of `INDEX(Config,3,B5)` and `INDEX(Config,3,B5+1)`.

1. If I reference the cell directly, I’ll get very long/hard to read formulas: `\$'Config-Insurance'.A2`
2. I won’t be able to refer to `Column + 1` as easily:

`=INDIRECT("'Config-Insurance'!" ADDRESS(2,COLUMN(\$'Config-Insurance'.A2)+1))`
`=INDEX(Config,3,B5+1)`

This is to reference either column C/D or E/F in the config, depending on some values present on the row itself. I tried making formulas simpler by breaking them down, but it’s still quite long with multiple IFs and SUM and ROUNDUP referencing data from the config.

I made a very basic demo to better illustrate. In `Data` someone create a new row/line, and define the type, here it’s either 1 or 2. In `Result`, we use the `value type` as a way to get proper data related to it from the `Config`.

Demo.ods (14.5 KB)

I arranged so the `Result` sheet have it’s `B5` value where mine is for the row’s data type/class.

I hope I’m making myself clear and sorry I didn’t elaborate sooner, I thought this was going to be much simpler. Any tips?

Look up your config values.
=VLOOKUP(name ; config_table ; 2 ; 0) returns the value from the 2nd column where a given name matches with the first column in config_table.