OFFSET Function

  • Thread starter Thread starter |Janed
  • Start date Start date
J

|Janed

Hi there,
I am having problems trying to get a 53 row * 87 col
worksheet to be displayed as a single column.

I have been using the following formula =OFFSET(Sheet1!
$A$1,INT((ROW()-1)/53),MOD(ROW()-1,53)) but his has not
captured all of the data in a single row...ie. Sheet 2
Column A should have 4600 rows approx but only has 2500
approx.

Any suggestions??
 
Try changing the "53" to "87"
i.e. use the # of columns instead, viz.:

=OFFSET(Sheet1!$A$1,INT((ROW()-1)/87),MOD(ROW()-1,87))

--
Rgds
Max
xl 97
 
Hi
depens on how you want your target data to be displayed: (from left to
right or from top to bottom in respect to the source data. That is:
process first row #1 or process first column #1)
The formula below won't do. Try
=OFFSET(Sheet1!$A$1,INT((ROW()-1)/87),MOD(ROW()-1,87))
for left to right processing

or
=OFFSET(Sheet1!$A$1,MOD(ROW()-1,53),INT((ROW()-1)/53))
for top-down processing
 
Try changing the "53" to "87"
i.e. use the # of columns instead, viz.:

=OFFSET(Sheet1!$A$1,INT((ROW()-1)/87),MOD(ROW()-1,87))

--
Rgds
Max
xl 97
 
Back
Top