Cut/Paste Range, one cell has formula, two cells do not

[Peptide]

Hello,
Looking to be able to cut/paste a range of cells with one of the cells having a formula. I want to be able to cut and past A1:A3 to B1:B3, C1:C3 etc.

Example of a range of data
A1= Joe, A2=Apple, A3=Orange

NOTE: A1 formula is +D1, A2 and A3 are text Apple and Orange

D1=Joe, D2=Bob, D3=Sam, D4=Tom


Result
A1=Joe, A2=Apple, A3=Orange
A2=Bob, A2=Apple, A3=Orange
A3=Sam, A2=Apple, A3=Orange
A4=Tom, A2=Apple, A3=Orange

 

[Joe4]

So, what exactly is the issue?

Is it that you want to maintain the value shown in A1 when you paste it elsewhere?
If so, use Copy -> Paste Special -> Values to convert the formula to hard-coded.
Then, if you want to remove the original, you can just delete it afterwards.

 

[Peptide]

Joe4 - sorry, my bad. Please note the update in RED

Example of a range of data
A1= Joe, A2=Apple, A3=Orange

NOTE: A1 formula is +D1, A2 and A3 are text Apple and Orange

D1=Joe, E1=Bob, F1=Sam, G1=Tom


Result
A1=Joe, A2=Apple, A3=Orange
A2=Bob, A2=Apple, A3=Orange
A3=Sam, A2=Apple, A3=Orange
A4=Tom, A2=Apple, A3=Orange

 

[Joe4]

So, are you trying to populate cells D1:G1, or A1:A4?

 

[Peptide]

Trying to populate A1:A4 with D1:G1

My example only gave 4 lines (A1:D1) but the spreadsheet actually has 100's. I want to be able to cut/paste A1:A3 down hundreds of lines and allow thef formula in A1 to change from D1 to E1 to F1 to G1 etc.

Make sense? Thanks in advance for your help.

 

[Joe4]

Just place this formula in cell A1 and copy down as far as you need to go:

=OFFSET(D$1,0,ROW()-1)

 

[Peptide]

That is brilliant!

One more question if I may push my luck. I have a similar issue on another file where the data is column apart as noted below:

If the current D1=Joe, E1=Bob, F1=Sam, G1=Tom where now D1=Joe, G1=Bob, J=Sam, M1=Tom (3 columns apart) how would your formula change?

I VERY much appreciate your help!!

 

[Joe4]

Try:

=OFFSET(D$1,0,(ROW()-1)*3)

 

[Peptide]

Joe4 - 2nd piece of brilliance! I know that you never meet people who ask for help and some may never thank you. I just want to say thank you for you help and patience on this. People like yourself have been very kind and helpful and I am very truly grateful for you insight and solution!

Bob

 

[Joe4]

You are welcome!
And thank you for the kind words!