Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Fill Handle Across Columns
How do I get cell references to change the number (column) not letter (row) when I use the fill handle across rows? e.g. If I have a cell reference in Sheet 1 that refers to A2 in Sheet 2 and I want to use the fill handle to take values from A3, A4, A5 of Sheet 2 and run them as headings across the columns of Sheet 1 - how do I do this? If you just drag the fill handle it uses A2, B2, C2, D2 Thanks -- SenojNW ------------------------------------------------------------------------ SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958 View this thread: http://www.excelforum.com/showthread...hreadid=393746 |
#2
|
|||
|
|||
You need a formula
=OFFSET(Sheet1!$A$1,COLUMN(A:A)-1,) you can also copy and paste special and select transpose -- Regards, Peo Sjoblom (No private emails please) "SenojNW" wrote in message ... How do I get cell references to change the number (column) not letter (row) when I use the fill handle across rows? e.g. If I have a cell reference in Sheet 1 that refers to A2 in Sheet 2 and I want to use the fill handle to take values from A3, A4, A5 of Sheet 2 and run them as headings across the columns of Sheet 1 - how do I do this? If you just drag the fill handle it uses A2, B2, C2, D2 Thanks -- SenojNW ------------------------------------------------------------------------ SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958 View this thread: http://www.excelforum.com/showthread...hreadid=393746 |
#3
|
|||
|
|||
Hi!
Try this: =INDIRECT("Sheet2!A"&COLUMN(B:B)) Evaluates to =Sheet2!A2 As you drag copy across the COLUMN() argument will increment leading to: =Sheet2!A2.....=Sheet2!A3.....=Sheet2!A4.....=Shee t2!A5.....etc Biff "SenojNW" wrote in message ... How do I get cell references to change the number (column) not letter (row) when I use the fill handle across rows? e.g. If I have a cell reference in Sheet 1 that refers to A2 in Sheet 2 and I want to use the fill handle to take values from A3, A4, A5 of Sheet 2 and run them as headings across the columns of Sheet 1 - how do I do this? If you just drag the fill handle it uses A2, B2, C2, D2 Thanks -- SenojNW ------------------------------------------------------------------------ SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958 View this thread: http://www.excelforum.com/showthread...hreadid=393746 |
#4
|
|||
|
|||
The INDIRECT function seems to work well... Can you explain the formula a bit more for me - I like to understand what it is doing... :) -- SenojNW ------------------------------------------------------------------------ SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958 View this thread: http://www.excelforum.com/showthread...hreadid=393746 |
#5
|
|||
|
|||
Hi!
=INDIRECT("Sheet2!A"&COLUMN(B:B)) The Indirect function takes TEXT representations of references and "converts" them to useable formula references. In this example the TEXT string "Sheet2!A" is concatenated with the result of the COLUMN() function to produce the TEXT representation that INDIRECT can use as a formula reference. The COLUMN() function returns the column NUMBER of it's argument. In this case column B is the second column so: COLUMN(B:B) = 2 You could also express that as: COLUMN(B1) The Column function will ignore the ROW reference, in this case, 1. So, Indirect uses the TEXT string: "Sheet2!A2" and "converts" it to the useable formula reference: =Sheet2!A2 Biff "SenojNW" wrote in message ... The INDIRECT function seems to work well... Can you explain the formula a bit more for me - I like to understand what it is doing... :) -- SenojNW ------------------------------------------------------------------------ SenojNW's Profile: http://www.excelforum.com/member.php...o&userid=25958 View this thread: http://www.excelforum.com/showthread...hreadid=393746 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill handle | Excel Discussion (Misc queries) | |||
fill handle | Excel Discussion (Misc queries) | |||
Keyboard command to replace double clicking the fill handle | Excel Discussion (Misc queries) | |||
Fill handle problem XP | Excel Discussion (Misc queries) | |||
3-d reference not adjusting when using Fill Handle to copy down | Excel Worksheet Functions |