Reference cells in another sheet WITHOUT having to use named ranges

Ed Rivis
Ed Rivis
edited 01/05/20 in Formulas and Functions

Hi -

can anyone tell me how to use the following formula on a *different* sheet than the columns referred to in the formula...

=SUMIF(MonthYear:MonthYear, FIND("19", @cell) > 0, [AW1]:[AW1])

(This formula works great when it's in the same sheet (A) as the one with columns name MonthYear and AW1, but I can't figure out how to make it work when I put it in another sheet (B).

I have tried putting the name of sheet A in curly braces... but not found a syntax that works?!

In Excel this would be easy... but SmartSheet has got me dumbfounded!

Thanks in advance,

Ed.

P.S. I don't want to use named ranges to refer to sheet A ... because the next thing I want to do is make the name of the column shown as 'AW1' a relative reference so I can do HLOOKUP.

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    HERE is some information regarding referencing another sheet.

  • Thanks for your reply. Unfortunately that article is the method which creates named ranges... which won't work for what I'm trying to achieve.

    In Excel you can refer to cells on other sheets using syntax like =SUM(Sheet1!B2:B20)

    (I.e. refer to cells on other sheets without having to create a named range.)

    I'm trying to achieve the same in SmartSheet... if it's possible.

  • You HAVE to use named ranges for cross sheet references

    That answers my question thank you - I'll stop trying to work out how to do it now I know it's not possible! :-)

    Cheers,

    Ed.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!