Copying formulae down while only changing certain aspects


I have been working on this for a while and have hit a wall, I have created a formulae that I want to copy down different cells but only change certain aspects of it. I need it copied to about 100 cells and hope there is an easier way than manually changing each one.

The formulae is

=IF(I5=Sheet2.G17, Sheet2.G22&Sheet2.G25&D5&Sheet2.G22&Sheet2.G25, IF(I5=Sheet2.G18, Sheet2.G23&Sheet2.G25&D5&Sheet2.G23&Sheet2.G25, IF(I5=Sheet2.G19, Sheet2.G24&Sheet2.G25&D3&Sheet2.G24&Sheet2.G25, D5)))

I only wish to change I5 and D5 to the corresponding row and want to keep the other values the same, is there any way to do this or will I have to do it manually?

Many thanks in advance,


Change the references you want to keep “sticky” to absolute references by prefixing the relevant part with a $ character. For example, in G22 both the column and row parts are relative, $G22 would have an absolute column reference part, G$22 would have an absolute row reference part, and $G$22 would have both, column and row, as absolute reference parts. Absolute references are not adapted when copying/pasting formula cells.

Also note that, unlike other spreadsheet implementations, LibreOffice distinguishes between relative and absolute sheet references, so when copying formula cells between sheets, Sheet2 and $Sheet2 make a difference.

Thank you for your answer, worked perfectly and saved me alot of time.