| 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()*2-2)
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$1-1)
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 |
|
| |
|
