0
(Personal Note: Never, but NEVER, organize stuff like you did in the beginning. The functions I designed are incredibly ugly and in no way are they practical or optimized. You have been warned).
Ranting aside, the OFFSET
and INDEX
are useful features, although that I would have to use an even more convoluted way to then get the needed values. Here is my solution. I'm assuming that the 2nd picture is a different sheet (and that the first sheet is named Sheet1. So, for the formulas in Sheet2:
A2:
=IF(MOD(ROW(A2)-2;4)=0;INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/4;0)+2;1;;;"Sheet1"));INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/4;0)+2;9+MOD(ROW(A2)-3;4)*2;;;"Sheet1")))
B2
=IF(MOD(ROW(B2)-2;4)=0;INDIRECT(ADDRESS(FLOOR((ROW(B2)-2)/4;0)+2;2;;;"Sheet1"));INDIRECT(ADDRESS(FLOOR((ROW(B2)-2)/4;0)+2;10+MOD(ROW(B2)-3;4)*2;;;"Sheet1")))
C2
=INDIRECT(ADDRESS(FLOOR((ROW(C2)-2)/4;0)+2;3;;;"Sheet1"))
D2
=INDIRECT(ADDRESS(FLOOR((ROW(C2)-2)/4;0)+2;4;;;"Sheet1"))
E2
=INDIRECT(ADDRESS(FLOOR((ROW(C2)-2)/4;0)+2;6;;;"Sheet1"))
F2
=INDIRECT(ADDRESS(FLOOR((ROW(C2)-2)/4;0)+2;7;;;"Sheet1"))
G2
=INDIRECT(ADDRESS(FLOOR((ROW(C2)-2)/4;0)+2;8;;;"Sheet1"))
After inputting the formulas, select the 2
row and drag down. It should work (as I've tested it). I'm not (currently) in the mood to explain every detail of the functions, but they hang on very simple math operations that end up mapping the rows on Sheet2
to the correct columns on Sheet1
.
1 - I use ;
as the argument separator, which is due to a regional setting. Will reformat later.
2http://i.imgur.com/BLRKMLQ.png – Jon – 2014-06-28T20:07:39.573
It should be accessible now - Let me know if its not - i made it view-able to anyone with the link. – user339565 – 2014-06-30T03:44:58.427