Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula series (newbie)
Hi,
In ( ) are the ref cells in sheet2 that I've entered the formula manually sheet 1 A B C 1 (m125) (m126) (m127) 2 (n125) (n126) (m128) 3 (o125) (n127) (m129) I want to avoid entering all the formulas manually. when I drag and fill the formula down ='sheet2'!m125 I get 1 m125 2 m126 3 m127 when I drag and fill the formula across ='sheet2'!m125 I get 1 m125 o125 what am I doing wrong? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula series (newbie)
You're not doing anything wrong ... that's exactly the way that XL
increments cell references when formulas are copied across columns and down rows. I don't know exactly what you're looking for though! I believe you've got some typo's in your example. You're showing Column M to go along Row1, and have the rows increment as they cross the columns. Row2 and Row 3 examples don't match the pattern of the Row1 example. Are you looking for Row2 to have the same pattern with Column N, and Row3 to have the same pattern with Column O? In other words, have Row125 in Column A, 126 in Column B, 127 in Column C, .... etc. And Column M in Row1, Column N in Row2, Column O in Row3, ... etc. If so, try this formula: =INDEX(Sheet2!$M$125:$Z$250,COLUMNS($A:A),ROWS($1: 1)) Copy across and then down. I set the boundaries of the range to be copied from M125 to Z250. Also, this formula can be entered *anywhere*, and will still return the referenced range as it's copied. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sam" wrote in message ... Hi, In ( ) are the ref cells in sheet2 that I've entered the formula manually sheet 1 A B C 1 (m125) (m126) (m127) 2 (n125) (n126) (m128) 3 (o125) (n127) (m129) I want to avoid entering all the formulas manually. when I drag and fill the formula down ='sheet2'!m125 I get 1 m125 2 m126 3 m127 when I drag and fill the formula across ='sheet2'!m125 I get 1 m125 o125 what am I doing wrong? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula series (newbie)
I did have typos. Let me try this again. In ( ) are ref to sheet2
A B C 1 m3 m4 m5 2 n3 n4 n5 3 o3 o4 o5 I just want to drag and fill the reference formula down from row 1 to row 3. My data in sheet 2 is not in the same row/col for every month. If I can't drag and fill I'll ref the cell in sheet 2 manually. Thanks for you help. "Ragdyer" wrote: You're not doing anything wrong ... that's exactly the way that XL increments cell references when formulas are copied across columns and down rows. I don't know exactly what you're looking for though! I believe you've got some typo's in your example. You're showing Column M to go along Row1, and have the rows increment as they cross the columns. Row2 and Row 3 examples don't match the pattern of the Row1 example. Are you looking for Row2 to have the same pattern with Column N, and Row3 to have the same pattern with Column O? In other words, have Row125 in Column A, 126 in Column B, 127 in Column C, .... etc. And Column M in Row1, Column N in Row2, Column O in Row3, ... etc. If so, try this formula: =INDEX(Sheet2!$M$125:$Z$250,COLUMNS($A:A),ROWS($1: 1)) Copy across and then down. I set the boundaries of the range to be copied from M125 to Z250. Also, this formula can be entered *anywhere*, and will still return the referenced range as it's copied. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sam" wrote in message ... Hi, In ( ) are the ref cells in sheet2 that I've entered the formula manually sheet 1 A B C 1 (m125) (m126) (m127) 2 (n125) (n126) (m128) 3 (o125) (n127) (m129) I want to avoid entering all the formulas manually. when I drag and fill the formula down ='sheet2'!m125 I get 1 m125 2 m126 3 m127 when I drag and fill the formula across ='sheet2'!m125 I get 1 m125 o125 what am I doing wrong? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula series (newbie)
Then the formula I suggested *will work* ... BUT ... *only* within the
boundaries of the range that the formula indexes. Don't forget, that the range in the INDEX() is *relative*, and referenced as 1st row, 1st column by the actual cell locations of the referenced range. Can you revise the formula to your actual needs? If not, post back with *exactly* the range of cells you wish to reference. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sam" wrote in message ... I did have typos. Let me try this again. In ( ) are ref to sheet2 A B C 1 m3 m4 m5 2 n3 n4 n5 3 o3 o4 o5 I just want to drag and fill the reference formula down from row 1 to row 3. My data in sheet 2 is not in the same row/col for every month. If I can't drag and fill I'll ref the cell in sheet 2 manually. Thanks for you help. "Ragdyer" wrote: You're not doing anything wrong ... that's exactly the way that XL increments cell references when formulas are copied across columns and down rows. I don't know exactly what you're looking for though! I believe you've got some typo's in your example. You're showing Column M to go along Row1, and have the rows increment as they cross the columns. Row2 and Row 3 examples don't match the pattern of the Row1 example. Are you looking for Row2 to have the same pattern with Column N, and Row3 to have the same pattern with Column O? In other words, have Row125 in Column A, 126 in Column B, 127 in Column C, .... etc. And Column M in Row1, Column N in Row2, Column O in Row3, ... etc. If so, try this formula: =INDEX(Sheet2!$M$125:$Z$250,COLUMNS($A:A),ROWS($1: 1)) Copy across and then down. I set the boundaries of the range to be copied from M125 to Z250. Also, this formula can be entered *anywhere*, and will still return the referenced range as it's copied. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Sam" wrote in message ... Hi, In ( ) are the ref cells in sheet2 that I've entered the formula manually sheet 1 A B C 1 (m125) (m126) (m127) 2 (n125) (n126) (m128) 3 (o125) (n127) (m129) I want to avoid entering all the formulas manually. when I drag and fill the formula down ='sheet2'!m125 I get 1 m125 2 m126 3 m127 when I drag and fill the formula across ='sheet2'!m125 I get 1 m125 o125 what am I doing wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
"Unable to set the Formula property of the Series class" with a tw | Charts and Charting in Excel | |||
Chart -- Source Data... -- Series dialog window | Charts and Charting in Excel | |||
Series Formula | Charts and Charting in Excel | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions |