3d referencing not working

[Ronderbecke]

Hey There,

I have a formula that works just fine when only using 1 sheet. I changed it to reference multiple sheets and it just gives an error? Any reason this could happen in google sheets?

Original:

=SUMPRODUCT(IF(MOD(ROW('Travis Jones'!C12:C)-ROW('Travis Jones'!C12),12)=0,IF(ISNUMBER('Travis Jones'!C12:C),IF('Travis Jones'!H7:H="Stacie Getsinger",IF('Travis Jones'!C12:C-DAY('Travis Jones'!C12:C)+1=DATE(2017,7,1),'Travis Jones'!C4:C<>0)))))

3d referencing:

=SUMPRODUCT(IF(MOD(ROW("'"&sheets&"'!C12:C")-ROW("'"&sheets&"'!C12"),12)=0,IF(ISNUMBER("'"&sheets&"'!C12:C"),IF("'"&sheets&"'!H7:H"="Stacie Getsinger",IF("'"&sheets&"'!C12:C"-DAY("'"&sheets&"'!C12:C")+1=DATE(2017,7,1),"'"&sheets&"'!C4:C<>0")))))

With the new formula it simply says: #N/A and when I scroll over it, the error says: "Argument must be a range"?

Any help would be greatly appreciated as I feel I have exhausted all options and cannot wrap my head around what is being impacted.

 

[XOR LX]

Hi,

I'm not sure you should be posting the same question twice. I appreciate that your last attempt was unresolved, though that it is not to say that it was exhausted.

I suggest going back to that post, creating a small, mock example workbook (in Google Sheets) which exhibits the same structure as your actual workbook (though, importantly, with sensitive/confidential data replaced with dummy data) and making sure to include your expected results.

Regards

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1077248-3d-mapping-complex-formula.html#post5176508

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).