INDIRECT help for colums only so i can copy formula down and row number changes per row

Hello All,

please can you help and point out where i am going wrong with this "F"&ROW in the INDIRECT formula


=IF(INDIRECT("F"&ROW($F2))="","",(ROW(A1)))

every time i insert a new columns before F the formula changes to

=IF(INDIRECT("F"&ROW($G2))="","",(ROW(A1)))

why is my Cell $F2 changing to $G2 with the inserted column


this works fine, but i am unable to copy the formula down the rows as F2 will stay static and i need it to change to F3, F4, F5 and so on.
=IF(INDIRECT($F2)="","",(ROW(A1)))

i am also using this in another formula:
=MATCH(INDIRECT("F"&ROW($F2)),INDIRECT("G2:G20"),0)))
again INDIRECT("G2:G20") this indirect is fine and does not change when adding a new column before F

when i add a column before F to add the team rankings for another time period.
the ("F"&ROW($F2)) again changes to ("F"&ROW($G2))




########

Also in sheet 2 i would like to rearrange the data so that the rank is value in the table, i can then use this data to build a line graph. can you please tell me the best way to do this? vlookup? match and index?



thanks in advance for any help offered.

Rather than tell us what's not working, explain what you are trying to do. We often see members come up with really impressive formulas that either dont really do what they want, could be simplified down to a fraction of the original formula or is not even the right formula

Sure, spoken just like me at work, lol. I don't want to hear what is not working so tell me why. tell me what you are trying to accomplish?

i am building a ranking spreadsheet for football (soccer if you will) teams. i would also like a line graph displaying position changes in the league.

all i want is to be able to add more data in to column F when data is available. days well time period will be amount of games actually but sample spreadsheet attached shows days. into column F. when i add new column to F to add new data formula/function (indirect) changes and breaks my match. column F must stay static but row must change as i want to copy formula down the rows for other teams. as it is calculating the position changes when new data is added. so must column G stay static but that is working.

my trouble really is here: ("F"&ROW(F2)) F2 changes when a new column is added to F for new rankings, so i need F column INDIRECT but row can change as i copy formula down rows.

oh and all of these formulas should only display values if there is team name in column F for that row. but that to is working

=IF(INDIRECT("E"&ROW(E2))="","",(MATCH(INDIRECT("F"&ROW($F2)),INDIRECT("G2:G20"),0)))

(INDIRECT("E"&ROW(E2)) & (INDIRECT("F"&ROW(F2)), fail, when new column is added before relevant column. F2 changes to G2. but should remain F2 for match to work as desired to calculate the rankings change.

In sheet 2 i then want to do a index match to get teams and time period to look up rank on that day for that team.

hope that makes sense. many thanks for any help. hopefully one of you excel gurus can see my mistake. fyi i should point out that i am using libre calc and not excel. but spreadsheet is xlsv hope its not calc is problem and formula is working in excel.