software

I have been reading all the advice and tips posted on this group and it
is fantastic!! I now have a query of my own:

I have one sheet where these calculation I am doing go down onto forty
rows say:

Sheet1
A
1 0.1
2 0.4
3 0.8
4 1
5 1.5
6 2
7 4

Than I want to reference that list in another sheet but they should
move along columns so:
A B C D E
F
1 0.1 0.4 0.8 1 1.5 2

So far i tried by writiing

=Sheet1!$A1 and then dragging it along the other columns with the
hope it fills all the others up and changes accordingly. Unfortunatly,
it doesn't change ! If Ii drag it downwards (along rows) it does work
so I assume its not mean to do it but I wonder if there is a way?

Can someone please help with any advice!!!

Thanks

Deba
Maybe you could try copy,paste special, transpose to columns

if you need to keep the cell references you may have to do another
step
such as
highlite the range
edit,find
find =
replace with quot;
then copy and transpose to columns
then edit,find
find quot;
replace with =

your references are still intact--
davesexcel------------------------------------------------------------------------
davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=539666Try this:

=INDEX(Sheet1!$A:$A,COLUMNS($A:A))

And drag across columns.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Debaquot; gt; wrote in message oups.com...
gt; I have been reading all the advice and tips posted on this group and it
gt; is fantastic!! I now have a query of my own:
gt;
gt; I have one sheet where these calculation I am doing go down onto forty
gt; rows say:
gt;
gt; Sheet1
gt; A
gt; 1 0.1
gt; 2 0.4
gt; 3 0.8
gt; 4 1
gt; 5 1.5
gt; 6 2
gt; 7 4
gt;
gt; Than I want to reference that list in another sheet but they should
gt; move along columns so:
gt; A B C D E
gt; F
gt; 1 0.1 0.4 0.8 1 1.5 2
gt;
gt; So far i tried by writiing
gt;
gt; =Sheet1!$A1 and then dragging it along the other columns with the
gt; hope it fills all the others up and changes accordingly. Unfortunatly,
gt; it doesn't change ! If Ii drag it downwards (along rows) it does work
gt; so I assume its not mean to do it but I wonder if there is a way?
gt;
gt; Can someone please help with any advice!!!
gt;
gt; Thanks
gt;
gt; Deba
gt;Hi=Sheet1!A$1
, and drag to right

Arvi Laanemets

quot;Debaquot; gt; wrote in message oups.com...
gt; I have been reading all the advice and tips posted on this group and it
gt; is fantastic!! I now have a query of my own:
gt;
gt; I have one sheet where these calculation I am doing go down onto forty
gt; rows say:
gt;
gt; Sheet1
gt; A
gt; 1 0.1
gt; 2 0.4
gt; 3 0.8
gt; 4 1
gt; 5 1.5
gt; 6 2
gt; 7 4
gt;
gt; Than I want to reference that list in another sheet but they should
gt; move along columns so:
gt; A B C D E
gt; F
gt; 1 0.1 0.4 0.8 1 1.5 2
gt;
gt; So far i tried by writiing
gt;
gt; =Sheet1!$A1 and then dragging it along the other columns with the
gt; hope it fills all the others up and changes accordingly. Unfortunatly,
gt; it doesn't change ! If Ii drag it downwards (along rows) it does work
gt; so I assume its not mean to do it but I wonder if there is a way?
gt;
gt; Can someone please help with any advice!!!
gt;
gt; Thanks
gt;
gt; Deba
gt;
This can be done with Copy and then using Paste Special instead of regular
Paste.

First select the cells on Sheet1 to be copied, then go to the location on
the second sheet where you want them to start and use
Edit | Paste Special
check the box next to [Transpose] and hit the [OK] button. Voila!

If you want to do it with a formula on the second sheet, use something like
this:
=OFFSET(Sheet1!$A$1,COLUMN(A1)-1,0)
That formula would go in cell A1 on the 2nd sheet and presumes your data
starts in cell A1 of Sheet1.

quot;Debaquot; wrote:

gt; I have been reading all the advice and tips posted on this group and it
gt; is fantastic!! I now have a query of my own:
gt;
gt; I have one sheet where these calculation I am doing go down onto forty
gt; rows say:
gt;
gt; Sheet1
gt; A
gt; 1 0.1
gt; 2 0.4
gt; 3 0.8
gt; 4 1
gt; 5 1.5
gt; 6 2
gt; 7 4
gt;
gt; Than I want to reference that list in another sheet but they should
gt; move along columns so:
gt; A B C D E
gt; F
gt; 1 0.1 0.4 0.8 1 1.5 2
gt;
gt; So far i tried by writiing
gt;
gt; =Sheet1!$A1 and then dragging it along the other columns with the
gt; hope it fills all the others up and changes accordingly. Unfortunatly,
gt; it doesn't change ! If Ii drag it downwards (along rows) it does work
gt; so I assume its not mean to do it but I wonder if there is a way?
gt;
gt; Can someone please help with any advice!!!
gt;
gt; Thanks
gt;
gt; Deba
gt;
gt;

Thank you so much! Used your option and worked perfectly !

I was also wondering if you can make it work when the value of the cell
you want to reference is every 10 rows. By this i mean

A
1 100
2
:
10 200
:
20 300

Than you want to make the refrence on the other worksheet but

A
1 100
2 200
3 300

So it would be jumping every 10 rows rather than one on one

Not sure if this is possible but then I also thought you couldnt do
many other thingsThanks a lot!

DebaEvery 10 rows, starting at Row1 would be:
1 - 11 - 21 - 31
OR, starting at Row10, would be:
10 - 20 - 30 - 40

=INDEX(Sheet1!$A:$A,COLUMNS($A:A)*10-9)
OR
=INDEX(Sheet1!$A:$A,COLUMNS($A:A)*10)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Debaquot; gt; wrote in message oups.com...
gt; Thank you so much! Used your option and worked perfectly !
gt;
gt; I was also wondering if you can make it work when the value of the cell
gt; you want to reference is every 10 rows. By this i mean
gt;
gt; A
gt; 1 100
gt; 2
gt; :
gt; 10 200
gt; :
gt; 20 300
gt;
gt; Than you want to make the refrence on the other worksheet but
gt;
gt; A
gt; 1 100
gt; 2 200
gt; 3 300
gt;
gt; So it would be jumping every 10 rows rather than one on one
gt;
gt; Not sure if this is possible but then I also thought you couldnt do
gt; many other things
gt;
gt;
gt; Thanks a lot!
gt;
gt; Deba
gt;HElp!!

Thank you so much for the INDEX code but I have never used this before
and it is explained very superficially in excel HElp. I tried your
suggestion above and it works fine when I do the drag onto columns but
for this case I need to drag down the rows.

This is what I have in the first sheet:

M
7 0.25
..
..
52 0.50
..
..
97 0.75And in the second sheet I want

D
1 0.25
2 0.50
3 0.75

So it is jumping every 45 rows, that is the frequency with which I want
to reference!

Great if you can help out with this!

REgardsYou can enter this formula *anywhere*, and copy down, and it will return the
cells you requested from Sheet1, Column M:

=INDEX(Sheet1!M:M,45*ROWS($1:1)-38)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------quot;Debaquot; gt; wrote in message ups.com...
gt; HElp!!
gt;
gt; Thank you so much for the INDEX code but I have never used this before
gt; and it is explained very superficially in excel HElp. I tried your
gt; suggestion above and it works fine when I do the drag onto columns but
gt; for this case I need to drag down the rows.
gt;
gt; This is what I have in the first sheet:
gt;
gt; M
gt; 7 0.25
gt; .
gt; .
gt; 52 0.50
gt; .
gt; .
gt; 97 0.75
gt;
gt;
gt; And in the second sheet I want
gt;
gt; D
gt; 1 0.25
gt; 2 0.50
gt; 3 0.75
gt;
gt; So it is jumping every 45 rows, that is the frequency with which I want
gt; to reference!
gt;
gt; Great if you can help out with this!
gt;
gt; REgards
gt;