Linking Cell value to text box per row dynamically

[Asaf345]

Hi

I have a problem I'm trying to solve for quite some time. solving it will help me a lot, saving much time.
I'll try to describe it to a simple use case and if there a solution is found i'll be able to use the principle for the more complex need.

Here it goes,


I have a table with two columns. In Cell A1 there is a numeric value and in cell B1 there is a text box.
To link the text box in B1 to A1 value the text box is selected , then I'm selecting the formula bar and enter e.g. "=A1"

Then I want to duplicate row 1 to row 2 to 100 (with the text box) and would like that the value in the text boxes on B2 to B100 will get the adjacent values (A2 to B2, A3 to B3, etc.)
However, when doing this replication and changing the values in A2:A100 the new text boxes in B2, B3...B100 are still linked to the value in A1 (instead of A2 , A3, A4 etc.)

Maybe Excel does not support that and maybe it is and there is a certain way to do it which I'm not aware of yet

anyway, any assistance will be much appreciated.

Thanks,

Asaf