Hi
Can I get Absolute Cell reference with INDIRECT function? Presently I have a 2 worksheets named “April 2011” and “Summary”. I want to access a value of cell in “April 2011” worksheet in “Summary” worksheet. So I used the following formula.
=INDIRECT(“‘”&A5;&”‘!G$5”)
where cell “A5” contains worksheet name (“April 2011”) and G5 cell in “April 2011” worksheet contains the value to be displayed. Now, I have inserted another row in “April 2011” worksheet and the G5 cell becomes G6. But the formula remains the same ( =INDIRECT(“‘”&A5;&”‘!G$5”)) and displays wrong value. Is there any way where I can increment or decrement the cell reference while adding or removing rows. Sorry for the Bad english.
What i was intended is when i insert a ROW in “April 2011” worksheet, the INDIRECT formula in “Summary” worksheet also change. i.e, If after inserting a row, G5 cell changed to G6, then i want the INDIRECT formula also change to =INDIRECT(“‘”&A5;&”‘!G$6”), automatically. It is not happening with current worksheet. I am using MS Excel 2007
I assume that the problem is that you are trying to reference “G5” on multiple sheets, based on the Sheet Name present in A5. Therefore giving G5 in any specific sheet a Name and then using that Name in the INDIRECT function won’t work since the name will only refer to a single G5.
One way around that is to name the cells so that each Name contains the name of the Sheet also, such as Sheet1_myName, Sheet2_myName, etc.
Then you could use your indirect function as follows:
=INDIRECT(“‘”&A5;&”‘!”&A5;&”_myName”)
Try =ADDRESS(ROW(cell_name),COLUMN(cell_name)).
A cell w/ the above formula will display $B$5, for example.
See Excel help for more info on usage/syntax, esp. the helpful abs_num option:
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
In Excel 2007, there’s a name manager that you can use to assign cell or range names. You can also select a cell or range of cells & then right click, & select “Name a Range…”.