using absolute cell reference with INDIRECT

Microsoft Office excel 2007 - upgrade
April 1, 2011 at 04:41:25
Specs: Windows 7
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.


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.

See More: using absolute cell reference with INDIRECT

Report •

April 1, 2011 at 07:11:30
Is there any way where I can increment or decrement the cell reference while adding or removing rows.
Not in another sheet.
There is no way for the formula to know you have inserted a row.

Can you do a lookup of some sort?


Report •

April 1, 2011 at 11:04:16
If you want to access a cell's value irrespective of the cell moving it's absolute location, give that cell a range name. Use the text box to the left of the edit line. Subsequent (non-manual) formulas will automatically use the name rather than the grid location.

Report •

April 1, 2011 at 23:06:45
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

Report •

Related Solutions

September 29, 2011 at 10:02:11
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...".

Report •

September 30, 2011 at 13:06:33
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:


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Ask Question