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!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,960
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top