use a formula that links to another worksheet and that worksheet will clear contents causing ref err

%3CLINGO-SUB%20id%3D%22lingo-sub-1580817%22%20slang%3D%22en-US%22%3Euse%20a%20formula%20that%20links%20to%20another%20worksheet%20and%20that%20worksheet%20will%20clear%20contents%20causing%20ref%20err%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1580817%22%20slang%3D%22en-US%22%3E0%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1580817%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581006%22%20slang%3D%22de-DE%22%3ESubject%3A%20use%20a%20formula%20that%20links%20to%20another%20worksheet%20and%20that%20worksheet%20will%20clear%20contents%20causin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581006%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F755754%22%20target%3D%22_blank%22%3E%40Pabspaul%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EWhat%20exactly%20do%20you%20want%20to%20accomplish%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3EAccording%20to%20the%20translation%20(which%20is%20not%20the%20best%20-%20to%20put%20it%20mildly)%20would%20you%20like%20to%20link%20yourself%20to%20another%20worksheet%20with%20a%20formula%20and%20delete%20the%20Ref-err%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3EIf%20you%20delete%20a%20link%20in%20your%20worksheet%2C%20you%20do%20not%20need%20to%20enter%20a%20formula%20in%20the%20linked%20worksheet.%3C%2FSPAN%3E%20%3CSPAN%3Ethat%20goes%20from%20your%20worksheet%20too.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3EHowever%2C%20in%20order%20to%20provide%20the%20best%20possible%20solution%2C%20it%20would%20be%20advantageous%20if%20a%20file%20(without%20sensitive%20data)%20with%20a%20problem%20was%20available%20or%20if%20you%20had%20inserted%20it.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581460%22%20slang%3D%22en-US%22%3EBetreff%3A%20use%20a%20formula%20that%20links%20to%20another%20worksheet%20and%20that%20worksheet%20will%20clear%20contents%20causin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581460%22%20slang%3D%22en-US%22%3EThank%20you%20I'm%20I%20cannot%20attach%20document%20because%20it's%20sensitive%20information%20but%20I%20will%20try%20to%20explain%20I%20have%202%20work%20sheets%20a%20calc%20sheet%20and%20a%20data%20sheet.%3CBR%20%2F%3EI%20import%20data%20into%20the%20data%20sheetand%20then%20run%20a%20macro%20from%20the%20calc%20sheet.%3CBR%20%2F%3EI%20have%20a%20clear%20button%20that%20reset%20the%20sheet%3CBR%20%2F%3ESome%20of%20formulas%20from%20the%20calc%20sheet%20that%20pull%20from%20the%20data%20have%20a%20ref%20error%3CBR%20%2F%3ESumif%20function%20point%20to%20a%20range%20t%3At%20the%20t%3At%20becomes%20a%20ref%20error%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581574%22%20slang%3D%22de-DE%22%3ESubject%3A%20use%20a%20formula%20that%20links%20to%20another%20worksheet%20and%20that%20worksheet%20will%20clear%20contents%20causin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581574%22%20slang%3D%22de-DE%22%3E%3CBR%20%2F%3EThus%2C%20a%20%23BEZUG%20error%20is%20corrected%20(in%20German)%20%3CBR%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fde-de%2Foffice%2Fso-wird-ein-bezug-fehler-korrigiert-822c8e46-e610-4d02-bf29-ec4b8c5ff4be%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fde-de%2Foffice%2Fso-wird-ein-bezug-fehler-korrigiert-822c8e46-e610-4d02-bf29-ec4b8c5ff4be%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20to%20correct%20a%20%23REF!%20error%20%3CBR%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fhow-to-correct-a-ref-error-822c8e46-e610-4d02-bf29-ec4b8c5ff4be%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fhow-to-correct-a-ref-error-822c8e46-e610-4d02-bf29-ec4b8c5ff4be%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3ETheoretically%2C%20you%20could%20work%20around%20the%20problem%20by%20not%20writing%20the%20corresponding%20calculation%20formulas%20in%20the%20cells%2C%20but%20in%20VB%20code%20(macro%20recorder).%20%3CBR%20%2F%3E%20You%20can%20then%20insert%20this%20code%20into%20the%20%22Worksheet%20Activate%22%20event%20of%20Sheet1%2C%20for%20example.%20%3CBR%20%2F%3E%20This%20will%20restore%20the%20formula%20every%20time%20Sheet1%20is%20activated.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20I%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor
3 Replies

@Pabspaul 

What exactly do you want to accomplish?
According to the translation (which is not the best - to put it mildly) would you like to link yourself to another worksheet with a formula and delete the Ref-err?

If you delete a link in your worksheet, you do not need to enter a formula in the linked worksheet. that goes from your worksheet too.

However, in order to provide the best possible solution, it would be advantageous if a file (without sensitive data) with a problem was available or if you had inserted it.

 

Nikolino

I know I don't know anything (Socrates)

Thank you I'm I cannot attach document because it's sensitive information but I will try to explain I have 2 work sheets a calc sheet and a data sheet.
I import data into the data sheetand then run a macro from the calc sheet.
I have a clear button that reset the sheet
Some of formulas from the calc sheet that pull from the data have a ref error
Sumif function point to a range t:t the t:t becomes a ref error

So wird ein #BEZUG!-Fehler korrigiert (in German)
https://support.microsoft.com/de-de/office/so-wird-ein-bezug-fehler-korrigiert-822c8e46-e610-4d02-bf...

How to correct a #REF! error (in English)
https://support.microsoft.com/en-gb/office/how-to-correct-a-ref-error-822c8e46-e610-4d02-bf29-ec4b8c...

Theoretically, you could work around the problem by not writing the corresponding calculation formulas in the cells, but in VB code (macro recorder).
You can then insert this code into the "Worksheet Activate" event of Sheet1, for example.
This will restore the formula every time Sheet1 is activated.

I would be happy to know if I could help.


Nikolino
I know I don't know anything (Socrates)