reference a cell another worksheet that will stay the same even as I add/delete rows etc

mechengineer

New Member
Joined
Aug 11, 2014
Messages
14
I have a front page worksheet called "LIVE REPORT" that should always show the number in my DailyElect worksheet that is column H row 34 (H34 is also a formula that looks like this =G34*$H$2 but I have to delete a row and add a row daily and when I delete the rows it changes it from what was row 34 to know row 33, I used the Indirect function but it keeps giving me a REF error. Please help

This is the formula as of right now =DailyElect!H34 and I tried =INDIRECT(DailyElect!H34) but it keeps giving me #REF!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

ghrain22

Active Member
Joined
Jan 9, 2014
Messages
473
That's not how the indirect formula works. The indirect formula basically turns a string or value into an actual reference. You're getting a ref error becuase the value in H34 isn't an appropriate Cell reference.

Insead of an indirect formula try making the cell a named range. Excel is pretty smart and will move the named range when you delete/add rows (unless you delete the row/column it's in).
 

mechengineer

New Member
Joined
Aug 11, 2014
Messages
14
That's not how the indirect formula works. The indirect formula basically turns a string or value into an actual reference. You're getting a ref error becuase the value in H34 isn't an appropriate Cell reference.

Insead of an indirect formula try making the cell a named range. Excel is pretty smart and will move the named range when you delete/add rows (unless you delete the row/column it's in).

Thanks for the reply but could you kindly show me the correct formula to use please?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,963
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top