Referencing formula using Indirect


R

Raj

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
 
Ad

Advertisements

R

Raj

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top