Formula Cannot Reference a Range on other sheets

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3175119%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EFormula%20Cannot%20Reference%20a%20Range%20on%20other%20sheets%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3175119%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%20lovely%20people%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20trying%20to%20make%20the%20following%20forumula%20work%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DLOOKUP(2%2C1%2F(((COUNTIF(%24A%241%3AA1%2C%20%3CSTRONG%3EInput!%24A%242%3A%24A%24200%26lt%3B%5C%2FSTRONG%26gt%3B)%3D0)*(COUNTIF(%3CSTRONG%3EInput!%24A%242%3A%24A%24200%26lt%3B%5C%2FSTRONG%26gt%3B%2C%20%3CSTRONG%3EInput!%24A%242%3A%24A%24200%26lt%3B%5C%2FSTRONG%26gt%3B)%26gt%3B0.5)))%2C%20%3CSTRONG%3EInput!%24A%242%3A%24A%24200%26lt%3B%5C%2FSTRONG%26gt%3B)%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20parts%20in%20bold%20are%20references%20to%20a%20range%20in%20another%20sheet%20on%20the%20document%20called%20%22Input%22.%20Whenever%20I%20type%20the%20%22Input!%22%20in%20front%20of%20the%20range%2C%20it%20immediately%20deactivates.%20This%20is%20incredibly%20frustrating.%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20also%20tried%20%3DLOOKUP(2%2C1%2F(((COUNTIF(%24A%241%3AA1%2C%20'%3CSTRONG%3EInput!'%24A%242%3A%24A%24200%26lt%3B%5C%2FSTRONG%26gt%3B)%3D0)*(COUNTIF('%3CSTRONG%3EInput!'%24A%242%3A%24A%24200%26lt%3B%5C%2FSTRONG%26gt%3B%2C%20'%3CSTRONG%3EInput!'%24A%242%3A%24A%24200%26lt%3B%5C%2FSTRONG%26gt%3B)%26gt%3B0.5)))%2C%20'%3CSTRONG%3EInput!'%24A%242%3A%24A%24200%26lt%3B%5C%2FSTRONG%26gt%3B)%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20ideas%20on%20how%20to%20change%20it%3F%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3CBR%20%2F%3EJT%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3175119%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FSTRONG%3E%3C%2FSTRONG%3E%3C%2FSTRONG%3E%3C%2FSTRONG%3E%3C%2FSTRONG%3E%3C%2FSTRONG%3E%3C%2FSTRONG%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3175189%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Cannot%20Reference%20a%20Range%20on%20other%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3175189%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1309352%22%20target%3D%22_blank%22%3E%40dudemn716%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20copy%2Fpaste%20the%20formula%20and%20have%20no%20problems%20with%20it%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20911px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348833i61C63CB0F292F76F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi lovely people,

I am trying to make the following forumula work:

=LOOKUP(2,1/(((COUNTIF($A$1:A1, Input!$A$2:$A$200)=0)*(COUNTIF(Input!$A$2:$A$200, Input!$A$2:$A$200)>0.5))), Input!$A$2:$A$200)

The parts in bold are references to a range in another sheet on the document called "Input". Whenever I type the "Input!" in front of the range, it immediately deactivates. This is incredibly frustrating.

I've also tried =LOOKUP(2,1/(((COUNTIF($A$1:A1, 'Input!'$A$2:$A$200)=0)*(COUNTIF('Input!'$A$2:$A$200, 'Input!'$A$2:$A$200)>0.5))), 'Input!'$A$2:$A$200

Any ideas on how to change it?

Cheers
JT

1 Reply

@dudemn716 

I copy/paste the formula and have no problems with it

image.png