Copying rows values on one sheet to part of a formula in a column

[Manosh]

Hi all
Would appreciate some assistance on what appears to be a simple
request but may need a combination of expressions to fulfill
(hopefully not!).

I have on one sheet a series of data being generated on the same row
in different colums as a series. for example


10,5,30,20 etc and so on in a row in different colums.


On my other work sheet i need to use these values to perform
calculations but this table is in a column format.
ie
10*2
5*2
30*2
20*2
Problem:
- I wanted to copy the formula down the column but obv excel does not
understand that i would like to pick up the different columns, same
row values on one sheet whilst in the same column, different rows on
the other!
- I tried to transpose this by copy pasting but it gave me a ref
error.
- i read about address and indirect but couldn't get it to work :-(


My preferred solution would be a formula on the top line of the 2nd
worksheet which i could copy down depending on the number of entries
on the 1st sheet.


Many thanks in advance!
best
Manosh

 

[RagDyer]

Say your values are on Sheet1, in Row 5, starting in A5.

On Sheet2, place your multiplier in say B1.
This makes it easy to change, without having to change the formula itself.

In A1 of Sheet2 enter this formula:

=B$1*INDEX(Sheet1!$5:$5,ROWS($1:1))

And copy down as needed.

 

[Manosh]

Brill!
thnks a lot
m

Say your values are on Sheet1, in Row 5, starting in A5.

On Sheet2, place your multiplier in say B1.
This makes it easy to change, without having to change the formula itself.

In A1 of Sheet2 enter this formula:

=B$1*INDEX(Sheet1!$5:$5,ROWS($1:1))

And copy down as needed.

 

[RagDyeR]

You're welcome, and appreciate the feed-back.