A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Referencing formula using Indirect



 
 
Thread Tools Display Modes
  #1  
Old April 25th, 2010, 12:37 PM posted to microsoft.public.excel.worksheet.functions
Raj[_4_]
external usenet poster
 
Posts: 27
Default Referencing formula using Indirect

Hi,

A workbook has 13 sheets, a sheet each for the months from Apr-10 to
Mar-11 and final summary sheet

In the Summary sheet, I have a validation list in cell A1 where the
names of one of the sheets is selected by the user. eg, May-10

I want a formula to retrieve the value of a cell from that sheet (ie
May-10), eg Cell R4. The formula should have a relative address of the
source cell, so that when copied down, other cells from the source
sheet should be displayed.

Currently, I am using the following formula:
=INDIRECT("'"&$A$1&"'!"&ADDRESS(ROW(R4),COLUMN(R4) ,1))

The formula works fine, except that the R4 refers to R4 in the Summary
sheet and not the month displayed in cell A1. Hence, any rows inserted/
deleted in the Summary sheet results in the REF error. Is there an
easy/another way to refer to cell R4 of the sheet whose name is
displayed in cell A1 of the current sheet?

Thanks in Advance for the help.

Regards,
Raj

  #2  
Old April 25th, 2010, 01:10 PM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default Referencing formula using Indirect

Hi,

Try this

=INDIRECT("'"&$A$1&"'!R4")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Raj" wrote in message
...
Hi,

A workbook has 13 sheets, a sheet each for the months from Apr-10 to
Mar-11 and final summary sheet

In the Summary sheet, I have a validation list in cell A1 where the
names of one of the sheets is selected by the user. eg, May-10

I want a formula to retrieve the value of a cell from that sheet (ie
May-10), eg Cell R4. The formula should have a relative address of the
source cell, so that when copied down, other cells from the source
sheet should be displayed.

Currently, I am using the following formula:
=INDIRECT("'"&$A$1&"'!"&ADDRESS(ROW(R4),COLUMN(R4) ,1))

The formula works fine, except that the R4 refers to R4 in the Summary
sheet and not the month displayed in cell A1. Hence, any rows inserted/
deleted in the Summary sheet results in the REF error. Is there an
easy/another way to refer to cell R4 of the sheet whose name is
displayed in cell A1 of the current sheet?

Thanks in Advance for the help.

Regards,
Raj

  #3  
Old April 25th, 2010, 03:39 PM posted to microsoft.public.excel.worksheet.functions
Raj[_4_]
external usenet poster
 
Posts: 27
Default Referencing formula using Indirect

I had tried this earlier. But when I copy the formula down the column,
the R4 does not become relative. eg. If the formula is in cell B3,
and is referencing R4, then I want the formula to reference R5 in
cell B4. But it references R4 only. How do I make the "R4" relative?

Thanks and Regards,
Rajendra


On Apr 25, 5:10*pm, "Ashish Mathur" wrote:
Hi,

Try this

=INDIRECT("'"&$A$1&"'!R4")

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"Raj" wrote in message

...

Hi,


A workbook has 13 sheets, a sheet each for the months from Apr-10 to
Mar-11 *and *final summary sheet


In the Summary sheet, I have a validation list in cell A1 where the
names of one of the sheets is selected by the user. eg, May-10


I want a formula to retrieve the value of a cell from that sheet (ie
May-10), eg Cell R4. The formula should have a relative address of the
source cell, so that when copied down, other cells from the source
sheet should be displayed.


Currently, I am using the following formula:
=INDIRECT("'"&$A$1&"'!"&ADDRESS(ROW(R4),COLUMN(R4) ,1))


The formula works fine, except that the R4 refers to R4 in the Summary
sheet and not the month displayed in cell A1. Hence, any rows inserted/
deleted in the Summary sheet results in the REF error. Is there an
easy/another way to refer to cell R4 of the sheet whose name is
displayed in cell A1 of the current sheet?


Thanks in Advance for the help.


Regards,
Raj


  #4  
Old April 25th, 2010, 04:59 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Referencing formula using Indirect

Hi

Try
=INDIRECT("'"&$A$1&"'!R"&ROWS($1:4))
--
Regards
Roger Govier

Raj wrote:
I had tried this earlier. But when I copy the formula down the column,
the R4 does not become relative. eg. If the formula is in cell B3,
and is referencing R4, then I want the formula to reference R5 in
cell B4. But it references R4 only. How do I make the "R4" relative?

Thanks and Regards,
Rajendra


On Apr 25, 5:10 pm, "Ashish Mathur" wrote:
Hi,

Try this

=INDIRECT("'"&$A$1&"'!R4")

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"Raj" wrote in message

...

Hi,
A workbook has 13 sheets, a sheet each for the months from Apr-10 to
Mar-11 and final summary sheet
In the Summary sheet, I have a validation list in cell A1 where the
names of one of the sheets is selected by the user. eg, May-10
I want a formula to retrieve the value of a cell from that sheet (ie
May-10), eg Cell R4. The formula should have a relative address of the
source cell, so that when copied down, other cells from the source
sheet should be displayed.
Currently, I am using the following formula:
=INDIRECT("'"&$A$1&"'!"&ADDRESS(ROW(R4),COLUMN(R4) ,1))
The formula works fine, except that the R4 refers to R4 in the Summary
sheet and not the month displayed in cell A1. Hence, any rows inserted/
deleted in the Summary sheet results in the REF error. Is there an
easy/another way to refer to cell R4 of the sheet whose name is
displayed in cell A1 of the current sheet?
Thanks in Advance for the help.
Regards,
Raj


  #5  
Old April 26th, 2010, 01:37 AM posted to microsoft.public.excel.worksheet.functions
Raj[_4_]
external usenet poster
 
Posts: 27
Default Referencing formula using Indirect

Thanks a ton, Roger. It works exactly as it should.

Regards,
Rajendra



On Apr 25, 8:59*pm, Roger Govier
wrote:
Hi

Try
=INDIRECT("'"&$A$1&"'!R"&ROWS($1:4))
--
Regards
Roger Govier

Raj wrote:
I had tried this earlier. But when I copy the formula down the column,
the R4 does not become relative. eg. If the formula is *in cell B3,
and is referencing R4, *then I want the formula to reference R5 in
cell B4. But it references R4 only. How do I make the "R4" relative?


Thanks and Regards,
Rajendra


On Apr 25, 5:10 pm, "Ashish Mathur" wrote:
Hi,


Try this


=INDIRECT("'"&$A$1&"'!R4")


--
Regards,


Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com


"Raj" wrote in message


....


Hi,
A workbook has 13 sheets, a sheet each for the months from Apr-10 to
Mar-11 *and *final summary sheet
In the Summary sheet, I have a validation list in cell A1 where the
names of one of the sheets is selected by the user. eg, May-10
I want a formula to retrieve the value of a cell from that sheet (ie
May-10), eg Cell R4. The formula should have a relative address of the
source cell, so that when copied down, other cells from the source
sheet should be displayed.
Currently, I am using the following formula:
=INDIRECT("'"&$A$1&"'!"&ADDRESS(ROW(R4),COLUMN(R4) ,1))
The formula works fine, except that the R4 refers to R4 in the Summary
sheet and not the month displayed in cell A1. Hence, any rows inserted/
deleted in the Summary sheet results in the REF error. Is there an
easy/another way to refer to cell R4 of the sheet whose name is
displayed in cell A1 of the current sheet?
Thanks in Advance for the help.
Regards,
Raj


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.