Hi All,
I am trying to move the amount values in one column to the corrosponding date matched column.
I have tried different variations of the offset command, but i am either using it wrong or it is not the right formuala
Also, I had wanted to be able to drag down as there are a lot more than the values shown
If you upload your beautiful example in .ods format, then help will come much faster. 
So, the formulas for cells E2 and F2:
=INDEX($A$1:$B$10000;(ROW(E2)+6)/4;1)
=INDEX($A$1:$B$10000;(ROW(F2)+6)/4;2)
Next, select the range E2:F5 and drag it down.
2 Likes
Or maybe you are looking for something like
=IF(MOD(ROW()-2;4);"";OFFSET($A$2;INT(ROW()/4);0))
for E2 and
=IF(E2;SUMPRODUCT(A:A=E2;B:B);"")
for F2?
1 Like
Thankyou for the help , its sorted out now both examples worked , but as I only needed one copy of amount in each cell i used john suns example, But have learnt from both ,Thanks again
It looks like you missed this part:
Not E2:F2 but E2:F5
Please try this
1 Like
Hi Thanks for the reply, I was doing it wrong e2:f2 instead of e2:f5 it now works,
However when I try it in my main spreadsheet I cannot convert it , It is basically the same except the data is in another sheet and there are more values. So for the first row i needed i use the formula
=INDEX($A$1:$B$10000;(ROW(E2)+6)/4;1)
and changed it to
=INDEX($January.$A$4:$January.$A$33,(ROW(A3)+6/4,1))
I do not understand the values +6/4,1, I am guessing they need to be changed to work with my formula
,also office changes my semicolons to commas
Thanks
You have changed the start of the ranges from your sample document. How does this affect the formula? Your proposed formula:
=INDEX($January.$A$4:$January.$A$33,(ROW(A3)+6/4,1))
From Help
Index Function =INDEX(Reference [; Row [; Column [; Range]]])
Reference is typically a range
Row refers to the row number of the Reference range. Row 1 refers to the first row in the range, not to the ROW number.
Column refers to the row number of the Reference range. Same as for Row
First, fix the missing closing parenthesis for Row and remove the extra one at the end.
=INDEX($January.$A$4:$January.$A$33,(ROW(A3)+6)/4,1)
The /4 is because there are four rows in your result for every row in the Reference range so every row in the result needs to be evenly divided by 4 to find the reference row.
The +6 was an offset to make the result row evenly divisible by four. The new data to be copied starts in sheet January on cell A4 and the results start on cell A3 so that offset is no longer valid. Because the data starts at the first cell of the reference range that is row 1. Multiply that by 4 and then subtract the row number (3) of the first result cell (Result.A3) to get the new offset of 1
So with those changes we get =INDEX(January.$A$4:$B$33,(ROW(A3)+1)/4,1) and we use the same formula but change the final 1 for the column index to 2 for the Amount column
In image below I have calculated the offset of 1 using formula shown next to cell
Note if Cell A4 of January contains the Heading and the data doesn’t start until cell A5 (second row of the reference range) then you will need to calculate 2 x 4 before subtracting the row number of the first result cell from that calculation and making the total the offset.
I hope this explanation helps
BTW depending on your language settings, LibreOffice will change your separator from ; to , The separators can be manually set in Tools > Options > LibreOffice Calc > Formula
2 Likes
HI EarnestAl,
Thanks for the explanation, It is very informative.it is now working, and I will now use your explanation for the rest of the spreadsheet. I think I should be able to work it out now.
Thanks again