Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying rows values on one sheet to part of a formula in a column
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying rows values on one sheet to part of a formula in a column
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Manosh" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying rows values on one sheet to part of a formula in a col
Brill!
thnks a lot :-) m "RagDyer" wrote: 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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Manosh" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying rows values on one sheet to part of a formula in a col
You're welcome, and appreciate the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Manosh" wrote in message ... Brill! thnks a lot :-) m "RagDyer" wrote: 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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Manosh" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to name many rows by values in a column on the same sheet at o | Excel Discussion (Misc queries) | |||
How to name many rows by values in a column on the same sheet at o | Excel Worksheet Functions | |||
Copying Part of a row down part of a column | Excel Discussion (Misc queries) | |||
Same formula in each cell of column; insert rows w/o copying down | Excel Discussion (Misc queries) | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions |