Remember Me?

 PeterM external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 19 Compound Reference

I have a spreadsheet that has 3 columns. I need to be able to make what I
call a compound reference. It's really hard to explain but I need to do the
following:

='Sheet A'!X(b1)

whe
The current sheet is Sheet B
Sheet A is the sheet containing the cell needed
X(b1) refers to column X of sheet A and
the (b1) refers to the cell in sheet B that contains the row number to use
in sheet A for row X

 Dave Peterson external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 35,218 Compound Reference

=indirect("'sheet a'!x" & b1)
or
=index('sheet a'!x:x,b1)

The =index() formula is better--it only recalculates when something changes in
column X of sheet a (or b1 changes).

The =indirect() formula will recalc whenever excel recalculates.

PeterM wrote:

I have a spreadsheet that has 3 columns. I need to be able to make what I
call a compound reference. It's really hard to explain but I need to do the
following:

='Sheet A'!X(b1)

whe
The current sheet is Sheet B
Sheet A is the sheet containing the cell needed
X(b1) refers to column X of sheet A and
the (b1) refers to the cell in sheet B that contains the row number to use
in sheet A for row X

--

Dave Peterson
 PeterM external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 19 Compound Reference

Perfect!

thank you Dave.

"Dave Peterson" wrote:

=indirect("'sheet a'!x" & b1)
or
=index('sheet a'!x:x,b1)

The =index() formula is better--it only recalculates when something changes in
column X of sheet a (or b1 changes).

The =indirect() formula will recalc whenever excel recalculates.

PeterM wrote:

I have a spreadsheet that has 3 columns. I need to be able to make what I
call a compound reference. It's really hard to explain but I need to do the
following:

='Sheet A'!X(b1)

whe
The current sheet is Sheet B
Sheet A is the sheet containing the cell needed
X(b1) refers to column X of sheet A and
the (b1) refers to the cell in sheet B that contains the row number to use
in sheet A for row X

--

Dave Peterson
.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Excel Beginner Excel Discussion (Misc queries) 0 May 29th 08 06:31 PM Jim May Excel Discussion (Misc queries) 7 September 26th 07 01:10 PM tikchye_oldLearner57 Excel Discussion (Misc queries) 4 November 26th 06 11:11 AM Robert Tracey Excel Worksheet Functions 1 November 30th 05 01:27 AM Paul Excel Worksheet Functions 3 March 8th 05 04:55 PM

All times are GMT +1. The time now is 01:12 AM. Copyright ©2004-2019 ExcelBanter.