Formula to lock row number when inserting rows

[Shadkng]

Hi, in the formula below when I insert a row the reference F6 changes to row 7. I would like to lock row 6 but have F change to G,H, etc. I see online a few ways to do it with indirect or maybe row function but I can't get it right. Any help would be appreciated. Thanks

=VLOOKUP(F$5,ARRAY!$A$22:$Z$38,6,FALSE)*F6

 

[James006]

Hi,

You do not mention in which cell your formula is located ...

Using the Indirect function ... you could lock the row number ...

Hope this will help

 

[Shadkng]

The formula is in row 4.

 

[James006]

You do not mention in which cell your formula is located ...

Row 4 ... 50% of the address ...

which Column ...???

 

[Shadkng]

Column F

 

[James006]

You can replace F6 by

INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"6")

HTH

 

[Shadkng]

Hi, thanks it seems to work. But I don't understand the formula but I'll go online to learn. Can you help with one more?

I need to have $C$6 locked as well so inserting rows will not affect it. Thanks

=IFERROR(IF(PO!$C$6="FLEX",INDEX(PO!$E$5:$U$28,2,MATCH(B8,PO!$E$5:$U$5,0))*VLOOKUP(B8,ARRAY!$A$22:$E$38,5,FALSE),""),"")

 

[Shadkng]

Disregard last post. I was able to solve the issue. Thanks

 

[James006]

You are welcome ...