Reference cells in another sheet WITHOUT having to use named ranges

Ed Rivis

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.

Paul Newcome

Are you able to provide more details as to exactly how things work/how you want them to work? You HAVE to use named ranges for cross sheet references, but there are various formulas that allow you to specify a column number within a table which can be automated to include the VLOOKUP and INDEX functions.

Exactly what you want to do is not possible, but it may be possible to accomplish the same goal through different methods.

Paul Newcome

HERE is some information regarding referencing another sheet.

Paul Newcome

Are you able to provide more details as to exactly how things work/how you want them to work? You HAVE to use named ranges for cross sheet references, but there are various formulas that allow you to specify a column number within a table which can be automated to include the VLOOKUP and INDEX functions.

Exactly what you want to do is not possible, but it may be possible to accomplish the same goal through different methods.

Ed Rivis

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.

Ed Rivis

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

Happy to help! 👍️