Enlisting two columns into a new based on terms

[AlirezaSharyan]

Dear all
I have a problem with function "Address", but I'm not even sure about picking the right function.
As you can see in image 1, in column "A", I have some data which is dynamic (can vary. In the sample here, data finishes in "A35"), and I have some other data in column "O" which are dynamic too.
I need to list these data in column "A" in another sheet (Image 2), the way when the first data list ends, the second data list to be added to the same column. My formula works for the first cell and adds the value of cell "O3" of the image 1 to cell "A35" in image 2. But after that, the same data is repeated in the next cells. I guess there is something wrong with what I wrote in parameters of the function "Address".

May someone check the issue?

Best regards

 

[Sergius]

Hello! Replace the third argument in the Address with "4" on "3".

 

[AlirezaSharyan]

Hello there.
Done before. Surprisingly nothing changed.

 

[kvsrinivasamurthy]

Column B and C data of image 2 is not available in Image1.

 

[AlirezaSharyan]

Column B and C data of image 2 is not available in Image1.

I will fill out other columns in Image "B" sheet as soon as the problem in column "A" is solved

 

[Sergius]

If the second list is constantly in the "O" column, then you can contact directly, without the "address" function. Or is it not for you?

 

[AlirezaSharyan]

If the second list is constantly in the "O" column, then you can contact directly, without the "address" function. Or is it not for you?

Done. Thank you very much.

 

[kvsrinivasamurthy]

Try. In A2 of SHEET2. Copy down.

=IF(ROWS($A$2:$A2)<=COUNTA(Sheet1!$A$1:$A$1000),Sheet1!A3,INDEX(Sheet1!$O$3:$O$1000,ROWS($A$2:$A2)-COUNTA(Sheet1!$A$1:$A$1000)))

 

[kvsrinivasamurthy]

Try this. In A2 of Sheet2.

=IF(ROWS($A$2:$A2)<=COUNTA(Sheet1!$A$3:$A$1000),Sheet1!A3,IF(ROWS($A$2:$A2)<=(COUNTA(Sheet1!$A$3:$A$1000)+COUNTA(Sheet1!$O$3:$O$1000)),INDEX(Sheet1!$O$3:$O$1000,ROWS($A$2:$A2)-COUNTA(Sheet1!$A$1:$A$1000)),""))

 

[AlirezaSharyan]

Try this. In A2 of Sheet2.

=IF(ROWS($A$2:$A2)<=COUNTA(Sheet1!$A$3:$A$1000),Sheet1!A3,IF(ROWS($A$2:$A2)<=(COUNTA(Sheet1!$A$3:$A$1000)+COUNTA(Sheet1!$O$3:$O$1000)),INDEX(Sheet1!$O$3:$O$1000,ROWS($A$2:$A2)-COUNTA(Sheet1!$A$1:$A$1000)),""))

THIS ONE IS JUST FINE. TAHNK YOU VERY MUCH