Automatically change cell reference in formulas, based off of cell updating

Question

I have a couple of formulas that are currently being manually updated every time someone wants to change the row that the formula is referencing. The current formula is:

="Call raised for a "&November!F4&" at "&TEXT(November!K4,"dd/mm hh:mm")&". Engineer attended site "&TEXT(November!L4,"dd/mm hh:mm")&" and departed "&TEXT(November!M48,"hh:mm")&". The engineer"

The team are currently doing "ctrl + h" and changing the row reference this way, but it keeps being tempramental. There is a table in the sheet with the above formula in F2:G3:

Is there a way to make the reference in the above formula update to the month and row referenced in the table above? It seems like something that should be possible but I haven't been able to find a solution after trawling online for around an hour.

I've tried searching on here and other forums but cannot seem to find an answer

Answer 1

I would setup the index and finds the row number in the November sheet where the value in cell A2 matches the values in column A of the November sheet, using the formula in F2 to something like:

="Call raised for a "&INDEX(November!$F$2:$F$100,MATCH(A2,November!$A$2:$A$100,0))&" at "&TEXT(INDEX(November!$K$2:$K$100,MATCH(A2,November!$A$2:$A$100,0)),"dd/mm hh:mm")&". Engineer attended site "&TEXT(INDEX(November!$L$2:$L$100,MATCH(A2,November!$A$2:$A$100,0)),"dd/mm hh:mm")&" and departed "&TEXT(INDEX(November!$M$2:$M$100,MATCH(A2,November!$A$2:$A$100,0)),"hh:mm")&". The engineer"