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

[WillWolf]

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 for6.

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.

[sokol92]

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

[Villeroy]

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.

[WillWolf]

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

[WillWolf]

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))
instead of
=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?

[Villeroy]

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.