Reference cells in another sheet WITHOUT having to use named ranges
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
-
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.
Answers
-
HERE is some information regarding referencing another sheet.
-
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.
-
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.
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!