Referencing columns to rows

[Deba]

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

 

[davesexcel]

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 "
then copy and transpose to columns
then edit,find
find "
replace with =

your references are still intact

 

[Ragdyer]

Try this:

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

And drag across columns.

 

[Arvi Laanemets]

Hi


=Sheet1!A$1
, and drag to right

Arvi Laanemets

 

[JLatham]

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.

 

[Deba]

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 things


Thanks a lot!

Deba

 

[Ragdyer]

Every 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)

 

[Deba]

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.75


And 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!

REgards

 

[Ragdyer]

You 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)