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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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).
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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
Back
Top