Greetings, hopefully you are the one who can answer this.
I have 1,000’s of .ods files from converted .pdf files.
A part of every one of these files needs a formula that moves
5 values: A2 to B1, A3 to C1, A4 to D1, A5 to E1 and A6 to F1.
Thanks
Am looking for a formula I can drag down the sheet to accomplish
the above.
AFAIK all formulas can be pulled down…
Hello
put into cell B1: =OFFSET($A$1;COLUMN()-1;0;1;1) and drag through F1
But to be absolutely precise: This is not move of cell content, but a reference to cell content.
Both answers work when pulling to the right. I messed up but I believe I can alter either one so that they pull down by referencing 5 column cells in each row cell and the formula would go in F1. Will let you know. No matter, I will be back to get more help or close as solved Thanks
No can do…my memory failed me. Many years ago I had a formula that would have sufficed. It was structured so that cell B1 referenced A1, C1 referenced A2; D1:A3; E1:A4; F1:A5 and G1:A6
When the formula is entered in G1 it can be pulled down to populate many pages in one move. Hopefully this is what you needed to know and it doesn’t matter what the formula is that does the trick. Thanks…
In cell A2: =INDIRECT("R1C"&ROW();0)
Drag formula in other cells of column A.
EDIT
As @JohnSUN remarked, I messed the axis. For copying into the first row, use =INDIRECT("R"&COLUMN()&"C1";0) in B1 and drag to the right.
To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!
In case you need clarification, edit your question (not an answer which is reserved for solutions) or comment the relevant answer.
Did you mean in B1 =INDIRECT("R"&COLUMN()&"C1",0) and drag on the first row? Why not {=TRANSPOSE(A2:A6)}?
@JohnSUN: thanks for noticing my confusion between row and column. Rereading OQ, I realise I messed everything. Your other suggestion is a good one but assumes OQ knows how to enter a matrix formula (Shift+Ctrl+Enter).
Yes, user preparedness can be a problem. Even when using Crl+X, Ctrl+Shift+V - Transpose
My apologies folks, I was not precise enough. I thought I could get a formula to populate what I had and then drag down the page to to repeat the formula in ascending rows. This formula does that.
To test, in case you ever need something like this, put a 1 in B1, drag down 50 lines. put the formula in D1.
it will output B1. Drag to I1, then drag down…
Thanks…
=INDEX($B1:$B$50,COLUMNS(D1:$D1))