I have a formula that references another worksheet which I occasionally delete all cells. This causes my Worksheet!A1 to change to Worksheet!#REF!. I would like the Worksheet!A1 to be hardcoded and return any errors on that end rather than changing the reference to an error. Is this possible and if so what is the best method to implement it?
I'm assuming your deleting the cells rather than the contents? your stuck with the ref error in that case
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
Something like this:
=INDEX(Sheet3!1:1048576,1,1)
which references A1 in Sheet3, will keep if you delete, say column A or Row 1.
You can also use Indirect...
e.g. =INDIRECT("'Sheet3'!A"&ROW($A$1)) and copy down to reference all column A cells in Sheet3, for example....
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks