Formula - I am Stumped

Options
BESP10
BESP10 ✭✭✭✭✭✭

Good afternoon

I am not understanding why I am having this issue, I have two sheets and on one sheet thecolumn formula works, on the other sheet it is giving me an "Invalid Ref" .


For what is is worth the formula is

=SUMIFS({BESDailyReportandWorkHrSheet2023_HRSTODAY}, {BESDailyReportandWorkhrSheet2023_Scope}, Scope@row) + SUMIFS({BESDailyReportWorkHrSheetJantoMar_HRSTODAY}, {BESDailyReportWorkHrSheetJantoMar_Scope}, Scope@row) + SUMIFS({2023DailyHrLogJunetoSept_HRSTODAY}, {2023DailyHrLogJunetoSept_Scope}, Scope@row) + SUMIFS({2023DailyWkHrLogOcttoDec_HRSTODAY}, {2023DailyHrLogOcttoDec_Scope}, Scope@row) + SUMIFS({2024DailyWkHrLogJanMar_HRSTODAY}, {2024DailyWkHrLog_Scope}, Scope@row) + SUMIFS({2024 Daily Work Hr Log Mar 25 to June 30 Range 1}, {2024 Daily Work Hr Log Mar 25 to June 30 Range 2}, Scope@row)


When I copy and paste I get an "Invalid Ref"


Any insight would be helpful


Thanks

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    @BESP10

    You don't have to type them out but I'm confused why you would be typing them out? You insert them through your formula reference, with each reference inserted at least once. On your new sheet, for each reference one by one for each unique reference, click inside the cross sheet range and then click edit reference in the formula window. See what column is being highlighted. If you come across a reference with no highlighted column on the other end, you found the missing reference(s).

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @BESP10

    Cross sheet references cannot simply be copy pasted in - they must be created on every sheet. Are the existing references created on this new sheet?

  • BESP10
    BESP10 ✭✭✭✭✭✭
    Options

    they are, then I add the new one and it still gives me an invalid reference, do I have to type the whole thing out? @Kelly Moore

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    @BESP10

    You don't have to type them out but I'm confused why you would be typing them out? You insert them through your formula reference, with each reference inserted at least once. On your new sheet, for each reference one by one for each unique reference, click inside the cross sheet range and then click edit reference in the formula window. See what column is being highlighted. If you come across a reference with no highlighted column on the other end, you found the missing reference(s).

    Kelly

  • BESP10
    BESP10 ✭✭✭✭✭✭
    Options

    It just worked,. thank you for info that was helpfull

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!