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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|