I have a formula:
=CONCAT( "Gave ", $GV8,", Received ",F$206)
when i copy down I would expect the next cell down to contain:
=CONCAT( "Gave ", $GV9,", Received ",G$206)
The first reference is updated but the second remains as 'F$206.
I have also tried with this version:
= "Gave "& $GV6 &", Received "& D$206
What am I doing wrong?
Hello Mike, Thank you for your reply. I’m afraid I still don’t understand, if one of my refs changes on copy then why doesn’t the other. I can’t see that it’s consistent?
@Lizat, The addition is always in the same direction, vertically the line and horizontally the column. I have a solution see Answer.
Learn about the difference between absolute and relative cell references in this help.
Here F$206 uses relative column referencing and with the $ prefix absolute row referencing, hence the column number is adjusted and the row number stays the same when copying the formula cell around. In $GV9 it is the opposite, absolute column referencing and relative row referencing, hence the column stays the same and the row is adjusted.
So probably, if you want to adjust the row while copying down and keep the same column copying horizontally, you want the reference to be $F206 instead. Or just F206 if it should adjust in both axes.
Of course, I do understand relative and absolute but wasn’t thinkinh clearly, of course the row wouldn’t change when copying down!!! How silly of me!
@Lizat, It is possible with the creation of an additional column, see example.
ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.
If the answer met your need, please click on the ball 
to the left of the answer, to finish the question.
In fact I am in the process of making all the formulas ‘independent’ which involves using Indirect quite a lot. I don’t know why I didn’t spot the obvious, but thank you!
