OFFSET Formula

[Kiley]

I am working with an OFFSET formula that was provided to me to help move
vertical data to horizontal data. In the original formula,
=OFFSET($F$1,ROW()*6-6+COLUMN()-18,) , I was moving 6 sets of vertical data.
Now I have 8 and the formula is not working. I am not sure how to
manipulate the numbers. I am assuming the "*6-6 refers to the 6 sets of data
and so I should change that to "*8-8", but I am not sure what the "-18"
stands for.

 

[Bob Phillips]

Where is the data that is being transposed, and where are the formulas?

 

[Mike H]

Kiley,

=OFFSET($F$1,ROW()*6-6+COLUMN()-18,)

Column()-18

If the formula is in Column R (Which is col 18) then the above returns zero.
If the formula is in any cell to the left of col R it will return #REF
because it will be a minus value. Basically this tells the formula where to
start in the column of data. make it evaluate to zero and it will start on
row 1.

So a reasonable alternative to the formula would be



I prefer this because it can now go in any column or row to return F1 and if
we drag down 1 row it becomes

=OFFSET($F$1,ROW(A2)*6-6+COLUMN(R2)-18,)

and returns F7

If we wanted the formula to start on row 2 we would use

=OFFSET($F$1,ROW(A1)*6-6+COLUMN(R1)-17,)

So I think you now want

=OFFSET($F$1,ROW(A1)*8-8+COLUMN(R1)-18,)

Which returns f1 then f9 when dragged down


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Kiley]

Mike,
Thank you so much. That worked.

Kiley,

=OFFSET($F$1,ROW()*6-6+COLUMN()-18,)

Column()-18

If the formula is in Column R (Which is col 18) then the above returns zero.
If the formula is in any cell to the left of col R it will return #REF
because it will be a minus value. Basically this tells the formula where to
start in the column of data. make it evaluate to zero and it will start on
row 1.

So a reasonable alternative to the formula would be



I prefer this because it can now go in any column or row to return F1 and if
we drag down 1 row it becomes

=OFFSET($F$1,ROW(A2)*6-6+COLUMN(R2)-18,)

and returns F7

If we wanted the formula to start on row 2 we would use

=OFFSET($F$1,ROW(A1)*6-6+COLUMN(R1)-17,)

So I think you now want

=OFFSET($F$1,ROW(A1)*8-8+COLUMN(R1)-18,)

Which returns f1 then f9 when dragged down


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Glad I could help
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Kiley]

If I may ask you another question. If I have a set of veritcal data for
each employee, but each set of data could range from 1 to 4 vertical values,
is there a way to change the following formulas to accomodate moving the data
from horizontal to vertical?:
=INDEX($A$2:$A$1690,1+(8*(ROWS(H$1:H1)-1)))
=OFFSET($G$2,ROW(A1)*8-8+COLUMN(R2)-18,)

Data Sample:
ID Data
275 Capitalize on Technical and Professional Know-How
275 Foster Teamwork
275 Value Others
933 Demonstrate Agility
933 Drive for Results
933 Team Leadership
933 Use Technical/Functional Expertise
658 Capitalize on Technical and Professional Know-How
658 Foster Teamwork
658 Manage Execution
1113 Demonstrate Drive & Commitment
1113 Leading Innovation
1113 Maximize Empowerment
147 Align with Strategy & Direction
147 Foster Teamwork
147 Manage for Results
1685 Analyze/Solve Problems
1685 Demonstrate Drive & Commitment
1685 Meet Customer Needs
880 Foster Teamwork
880 Manage for Results
880 Value Others
1130 Commit to Quality
1130 Develop Oneself
1130 Prepare Written Communication
227 Demonstrate Adaptability
227 Foster Open Communication
227 Innovate
267 Coach Others
267 Lead Boldly
267 Leading Change
253 Make Sound Decisions
253 Manage for Results
1521 Build Relationships