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

[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

 

[JoelS]

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

one possible solution is:
on the 2nd worksheet that uses columns; enter the muliplication factor
(ie. 2) in 1 column 4 rows; then go to the first worksheet, highlight
the 10, 5, 30, 20 numbers, copy (control -C); return to the 2nd
worksheet and "Paste Special" using multiply AND transpose.

 

[Manosh]

one possible solution is:
on the 2nd worksheet that uses columns; enter the muliplication factor
(ie. 2) in 1 column 4 rows; then go to the first worksheet, highlight
the 10, 5, 30, 20 numbers, copy (control -C); return to the 2nd
worksheet and "Paste Special" using multiply AND transpose.- Hide quoted text -

- Show quoted text -

thanks joels,
this is useful.
in addition to this i was provided the following solution by Rag,
which also works quite well and i am sharing with everyone :-

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.