Table formulas not aitofilling correctly

[JumboCactuar]

Hi,
I have a table formula which uses a vlookup on the above cells, like:

=vlookup(F99,$A$2:C98,3,false)

When I resize the table, to example row 500 (default size is 100)

All the rows formulas are correct except row 100 which shows after resizing:

=vlookup(F99,$A$2:C500,3,false)

Obviously this is messing with calculations as it should only lookup below current row

Any fix to this?

Appreciate any help

 

[JumboCactuar]

Re: Table formulas not autofilling correctly

ROW99 =vlookup(F99,$A$2:C99,3,false)
ROW100 =vlookup(F100,$A$2:C500,3,false)
ROW101 =vlookup(F101,$A$2:C100,3,false)

 

[JumboCactuar]

Re: Table formulas not autofilling correctly

bump,
its actually just SUMIFS that is the problem


=SUMIFS(A$6:A99,D$6:D99,D99,F$6:F99,$B$1 <--- normal


=SUMIFS(A$6:A500,D$6:D500,D100,F$6:F500,$B$1 <---this is last row of table before i resize it to 500
the 500 are 100 before resizing, i need them to stay 100


=SUMIFS(A$6:A101,D$6:D101,D101,F$6:F101,$B$1 <--- back to normal