Formulas with Tab Names

%3CLINGO-SUB%20id%3D%22lingo-sub-2332100%22%20slang%3D%22en-US%22%3EFormulas%20with%20Tab%20Names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2332100%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20currently%20working%20with%20the%20below%20formula%20and%20it%20has%20worked%20well%20so%20far.%20However%2C%20I%20am%20now%20needing%20to%20change%20the%20list%20the%20data%20is%20pulled%20from%20to%20a%20new%20tab%2Fsheet%20and%20the%20calendar%20the%20formula%20creates%20will%20remain%20on%20the%20existing%20tab.%20Moving%20the%20list%20to%20the%20new%20tab%20worked%20fine%20with%20the%20first%20cell%20but%20trying%20to%20pull%20the%20formula%20into%20adjacent%20cells%20it%20no%20longer%20recognizes%20the%20cell%20from%20the%20other%20tab%20(List!X%23).%20I've%20tried%20to%20add%20the%20%24%20in%20front%20of%20the%20tab%20name%20to%20lock%20the%20name%20into%20the%20formula%20but%20that%20doesn't%20work.%20Is%20there%20another%20way%20to%20keep%20the%20tab%20name%20in%20a%20formula%3F%26nbsp%3B%3C%2FP%3E%3CP%3EPrevious%20formula%20when%20all%20data%20was%20on%20one%20tab%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(L%244%26gt%3B%3D%24G6%2C%20L%244%26lt%3B%3DG6)%2C%22C%22%2CIF(I6%3DL%244%2C%22E%22%2CIF(%24K6%3DL%244%2C%22R%22%2C0)))%3C%2FP%3E%3CP%3ENew%20formula%20with%20the%20data%20list%20in%20separate%20tab%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(L%244%26gt%3B%3DList!%24G6%2C%20L%244%26lt%3B%3DList!G6)%2C%22C%22%2CIF(List!%24I6%3DL%244%2C%22E%22%2CIF(List!%24K6%3DL%244%2C%22R%22%2C0)))%3C%2FP%3E%3CP%3EFormula%20pulled%20into%20adjacent%20cell%20looks%20correct%20but%20doesn't%20work%20as%20it%20loses%20'connection'%20with%20List%3C%2FP%3E%3CP%3E%3DIF(AND(M%244%26gt%3B%3D(List!%24G6)%2C%20M%244%26lt%3B%3DList!%24G6)%2C%22C%22%2CIF(List!%24I6%3DM%244%2C%22E%22%2CIF(List!%24K6%3DM%244%2C%22R%22%2C0)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2332100%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-2342440%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20with%20Tab%20Names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2342440%22%20slang%3D%22en-US%22%3Ein%20this%20statement%3A%3CBR%20%2F%3E%3DIF(AND(M%244%26gt%3B%3D(List!%24G6)%2C%20M%244%26lt%3B%3DList!%24G6)%2C%22C%22%2CIF(List!%24I6%3DM%244%2C%22E%22%2CIF(List!%24K6%3DM%244%2C%22R%22%2C0)))%3CBR%20%2F%3E%3CBR%20%2F%3Ethe%20statements%20inside%20the%20AND%20is%20conflicted%3A%3CBR%20%2F%3EAND(M%244%26gt%3B%3D(List!%24G6)%2C%20M%244%26lt%3B%3DList!%24G6)%3CBR%20%2F%3Eit%20can't%20be%20both%2C%20either%20%24M%244%26gt%3B%3D%20List!%24G6%3CBR%20%2F%3EOR%3CBR%20%2F%3E%24M%244%26lt%3B%3DList!%24G6%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20which%20is%20it%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am currently working with the below formula and it has worked well so far. However, I am now needing to change the list the data is pulled from to a new tab/sheet and the calendar the formula creates will remain on the existing tab. Moving the list to the new tab worked fine with the first cell but trying to pull the formula into adjacent cells it no longer recognizes the cell from the other tab (List!X#). I've tried to add the $ in front of the tab name to lock the name into the formula but that doesn't work. Is there another way to keep the tab name in a formula? 

Previous formula when all data was on one tab 

=IF(AND(L$4>=$G6, L$4<=G6),"C",IF(I6=L$4,"E",IF($K6=L$4,"R",0)))

New formula with the data list in separate tab 

=IF(AND(L$4>=List!$G6, L$4<=List!G6),"C",IF(List!$I6=L$4,"E",IF(List!$K6=L$4,"R",0)))

Formula pulled into adjacent cell looks correct but doesn't work as it loses 'connection' with List

=IF(AND(M$4>=(List!$G6), M$4<=List!$G6),"C",IF(List!$I6=M$4,"E",IF(List!$K6=M$4,"R",0)))

1 Reply
in this statement:
=IF(AND(M$4>=(List!$G6), M$4<=List!$G6),"C",IF(List!$I6=M$4,"E",IF(List!$K6=M$4,"R",0)))

the statements inside the AND is conflicted:
AND(M$4>=(List!$G6), M$4<=List!$G6)
it can't be both, either $M$4>= List!$G6
OR
$M$4<=List!$G6

So which is it?