Columnar vs. Row Formula References

Tandem_2 · Apr 27th, 2002, 02:35 PM

Is there someway to create a formula that is to be copied -- horizontally across many columns, that references data from another worksheet that is listed in columnar fashion? Not sure if I am making myself clear.....
Example: Can I write a formula in Sheet1!A1 which references to Sheet2!A1 and then copy this formulat to Sheet1!B1 and have the reference to Sheet2!A1 automatically change to Sheet2!A2? What I want is although I am moving the formula horizontally, I want the reference to move vertically? In effect I am moving the refernce from a column to a row. It really is a pain to have to go and change the row reference in every single formula after it is copied.

Appreciate any suggestions. Thanks.

Also, this is my first posting so...I hope I have made myself clear.

Yogi Anand · Apr 27th, 2002, 02:41 PM

Hi Tandem_2:
Welcome to the board. It seems what you want to do is simple enough, although the statement seems pretty convoluted. It will help if you will show some sample data that you already have including the source and the target data.

Aladin Akyurek · Apr 27th, 2002, 02:48 PM

One way:

In A1 Sheet1 enter and copy across:

=INDEX(Sheet2!$A:$A,COLUMN())

Tandem_2 · Apr 27th, 2002, 02:54 PM

Quote:

On 2002-04-27 13:48, Aladin Akyurek wrote:
One way:

In A1 Sheet1 enter and copy across:

=INDEX(Sheet2!$A:$A,COLUMN())

Tandem_2 · Apr 27th, 2002, 02:55 PM

Thank you! I think this will work perfectly!
You made my day!

On 2002-04-27 13:54, Tandem_2 wrote:

Quote:

On 2002-04-27 13:48, Aladin Akyurek wrote:
One way:

In A1 Sheet1 enter and copy across:

=INDEX(Sheet2!$A:$A,COLUMN())

[/quote]

Apr 27th, 2002, 02:41 PM	#2
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Tandem_2: Welcome to the board. It seems what you want to do is simple enough, although the statement seems pretty convoluted. It will help if you will show some sample data that you already have including the source and the target data. __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 27th, 2002, 02:35 PM	#1
Tandem_2 New Member Join Date: Apr 2002 Posts: 21	Is there someway to create a formula that is to be copied -- horizontally across many columns, that references data from another worksheet that is listed in columnar fashion? Not sure if I am making myself clear..... Example: Can I write a formula in Sheet1!A1 which references to Sheet2!A1 and then copy this formulat to Sheet1!B1 and have the reference to Sheet2!A1 automatically change to Sheet2!A2? What I want is although I am moving the formula horizontally, I want the reference to move vertically? In effect I am moving the refernce from a column to a row. It really is a pain to have to go and change the row reference in every single formula after it is copied. Appreciate any suggestions. Thanks. Also, this is my first posting so...I hope I have made myself clear.

Apr 27th, 2002, 02:48 PM	#3
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	One way: In A1 Sheet1 enter and copy across: =INDEX(Sheet2!$A:$A,COLUMN())