Author 
Message 
Mike Boardma #1 / 8

Offset formula?
Evening everyone. A colleague has a problem defined as follows. I feel sure that an offset formula should do the trick, but I think the transposition from columns to rows is stopping me from coming up with an answer. I'ld appreciate anyone coming up with any solutions. TIA. Mike I have 2 worksheets and I want to be able to select information from every alternative column (i.e. B, D, F, H, J, L etc) from the first worksheet and transfer this information to the second worksheet displaying as 1 column. (i.e. the first entry from worksheet 1 from column B would be the first entry in worksheet 2 row 1 and the first entry in column D from worksheet 1 would be the second entry in worksheet 2 row 2 etc)

Sat, 19 Nov 2005 03:13:46 GMT 


Alan Beba #2 / 8

Offset formula?
If the functions in the file at http://home.pacbell.net/beban are available to your workbook, the following, array entered into A1:A[whatever] on Sheet2 will produce the sought result: =TRANSPOSE(ArrayAlternates(Sheet1!A1:L1,FALSE,FALSE)) Alan Beban Quote:
> Evening everyone. > A colleague has a problem defined as follows. I feel sure that an offset > formula should do the trick, but I think the transposition from columns to > rows is stopping me from coming up with an answer. I'ld appreciate anyone > coming up with any solutions. TIA. > Mike > I have 2 worksheets and I want to be able to select information from every > alternative column (i.e. B, D, F, H, J, L etc) from the first worksheet and > transfer this information to the second worksheet displaying as 1 column. > (i.e. the first entry from worksheet 1 from column B would be the first > entry in worksheet 2 row 1 and the first entry in column D from worksheet 1 > would be the second entry in worksheet 2 row 2 etc)

Sat, 19 Nov 2005 03:53:24 GMT 


Jason Mori #3 / 8

Offset formula?
Insert this into a new worksheet, cell A1, and fill down: =OFFSET(Sheet1!$B$1,,ROW()*22) This will pull in B1,D1,F1,H1,etc. from Sheet1. HTH Jason Atlanta, GA Quote: >Original Message >Evening everyone. >A colleague has a problem defined as follows. I feel sure that an offset >formula should do the trick, but I think the
transposition from columns to Quote: >rows is stopping me from coming up with an answer. I'ld appreciate anyone >coming up with any solutions. TIA. >Mike >I have 2 worksheets and I want to be able to select
information from every Quote: >alternative column (i.e. B, D, F, H, J, L etc) from the first worksheet and >transfer this information to the second worksheet
displaying as 1 column. Quote: >(i.e. the first entry from worksheet 1 from column B would be the first >entry in worksheet 2 row 1 and the first entry in column D from worksheet 1 >would be the second entry in worksheet 2 row 2 etc) >.

Sat, 19 Nov 2005 04:17:30 GMT 


Ron Rosenfel #4 / 8

Offset formula?
On Mon, 2 Jun 2003 20:13:46 +0100, "Mike Boardman" Quote:
>Evening everyone. >A colleague has a problem defined as follows. I feel sure that an offset >formula should do the trick, but I think the transposition from columns to >rows is stopping me from coming up with an answer. I'ld appreciate anyone >coming up with any solutions. TIA. >Mike >I have 2 worksheets and I want to be able to select information from every >alternative column (i.e. B, D, F, H, J, L etc) from the first worksheet and >transfer this information to the second worksheet displaying as 1 column. >(i.e. the first entry from worksheet 1 from column B would be the first >entry in worksheet 2 row 1 and the first entry in column D from worksheet 1 >would be the second entry in worksheet 2 row 2 etc)
If the data on Sheet1 is in A1:Z100, then in A1 enter the formula: =INDIRECT(ADDRESS(ROW(),COLUMN()*2,,TRUE,"Sheet1")) Fill right to M1, then fill down Row 1 to Row 100. ron

Sat, 19 Nov 2005 04:23:35 GMT 


Steve Andrew #5 / 8

Offset formula?
Mike, I think you are right about using OFFSET and here is one dirty approach. First off, we must know the number of rows of data in order to know how often to repeat. Suppose for now you put this number in cell H1 of the new sheet. Likewise suppose the data is is Sheet1. Copy the following formula into all cells (beginning with row 1) of the one column to be produced. =OFFSET(Sheet1!$A$1,MOD(ROW()1,$H$1),2*(ROW()MOD(ROW() 1,$H$1)+21)/$H$11) If you want an indeterminant number of rows without having to set H1 manually, there are ways to determine the first blank row. Steve Quote: >Original Message >Evening everyone. >A colleague has a problem defined as follows. I feel sure that an offset >formula should do the trick, but I think the
transposition from columns to Quote: >rows is stopping me from coming up with an answer. I'ld appreciate anyone >coming up with any solutions. TIA. >Mike >I have 2 worksheets and I want to be able to select
information from every Quote: >alternative column (i.e. B, D, F, H, J, L etc) from the first worksheet and >transfer this information to the second worksheet
displaying as 1 column. Quote: >(i.e. the first entry from worksheet 1 from column B would be the first >entry in worksheet 2 row 1 and the first entry in column D from worksheet 1 >would be the second entry in worksheet 2 row 2 etc) >.

Sat, 19 Nov 2005 04:25:47 GMT 


Ron Rosenfel #6 / 8

Offset formula?
On Mon, 2 Jun 2003 20:13:46 +0100, "Mike Boardman" Quote:
>Evening everyone. >A colleague has a problem defined as follows. I feel sure that an offset >formula should do the trick, but I think the transposition from columns to >rows is stopping me from coming up with an answer. I'ld appreciate anyone >coming up with any solutions. TIA. >Mike >I have 2 worksheets and I want to be able to select information from every >alternative column (i.e. B, D, F, H, J, L etc) from the first worksheet and >transfer this information to the second worksheet displaying as 1 column. >(i.e. the first entry from worksheet 1 from column B would be the first >entry in worksheet 2 row 1 and the first entry in column D from worksheet 1 >would be the second entry in worksheet 2 row 2 etc)
Oops, I misread. In A1, place the formula: =INDIRECT(ADDRESS(1,ROW()*2,,TRUE,"Sheet1")) Then fill down as far as necessary. If he has multiple rows of data on Sheet1 to be copied to multiple columns on Sheet2, then change the formula to read: =INDIRECT(ADDRESS(COLUMN(),ROW()*2,,TRUE,"Sheet1")) and Fill left for as many columns as there are rows on Sheet1. ron

Sat, 19 Nov 2005 04:27:01 GMT 


Mike Boardma #7 / 8

Offset formula?
Many thanks, folks. I'll give the suggestions a try tomorry. Mike
Quote: > On Mon, 2 Jun 2003 20:13:46 +0100, "Mike Boardman"
> >Evening everyone. > >A colleague has a problem defined as follows. I feel sure that an offset > >formula should do the trick, but I think the transposition from columns to > >rows is stopping me from coming up with an answer. I'ld appreciate anyone > >coming up with any solutions. TIA. > >Mike > >I have 2 worksheets and I want to be able to select information from every > >alternative column (i.e. B, D, F, H, J, L etc) from the first worksheet and > >transfer this information to the second worksheet displaying as 1 column. > >(i.e. the first entry from worksheet 1 from column B would be the first > >entry in worksheet 2 row 1 and the first entry in column D from worksheet 1 > >would be the second entry in worksheet 2 row 2 etc) > Oops, I misread. > In A1, place the formula: > =INDIRECT(ADDRESS(1,ROW()*2,,TRUE,"Sheet1")) > Then fill down as far as necessary. > If he has multiple rows of data on Sheet1 to be copied to multiple columns on > Sheet2, then change the formula to read: > =INDIRECT(ADDRESS(COLUMN(),ROW()*2,,TRUE,"Sheet1")) > and Fill left for as many columns as there are rows on Sheet1. > ron

Sat, 19 Nov 2005 06:59:13 GMT 


Mike Boardma #8 / 8

Offset formula?
Many thanks again, folks. Alan's solution seems to be what my colleague wants, but all suggestions greatly appreciated. Mike
Quote: > Many thanks, folks. I'll give the suggestions a try tomorry. > Mike
> > On Mon, 2 Jun 2003 20:13:46 +0100, "Mike Boardman"
> > >Evening everyone. > > >A colleague has a problem defined as follows. I feel sure that an offset > > >formula should do the trick, but I think the transposition from columns > to > > >rows is stopping me from coming up with an answer. I'ld appreciate anyone > > >coming up with any solutions. TIA. > > >Mike > > >I have 2 worksheets and I want to be able to select information from > every > > >alternative column (i.e. B, D, F, H, J, L etc) from the first worksheet > and > > >transfer this information to the second worksheet displaying as 1 column. > > >(i.e. the first entry from worksheet 1 from column B would be the first > > >entry in worksheet 2 row 1 and the first entry in column D from worksheet > 1 > > >would be the second entry in worksheet 2 row 2 etc) > > Oops, I misread. > > In A1, place the formula: > > =INDIRECT(ADDRESS(1,ROW()*2,,TRUE,"Sheet1")) > > Then fill down as far as necessary. > > If he has multiple rows of data on Sheet1 to be copied to multiple columns > on > > Sheet2, then change the formula to read: > > =INDIRECT(ADDRESS(COLUMN(),ROW()*2,,TRUE,"Sheet1")) > > and Fill left for as many columns as there are rows on Sheet1. > > ron

Sun, 20 Nov 2005 01:09:55 GMT 


