Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Use linked cells as part of a formula
I'm trying to port across a large database from Excel into Smartsheet to enable us to use the Kanban type activity flow.
My excel sheet uses multiple tabs, and on one of these tabs I have an 'Analysis' section that pulls in data from all of these tabs and uses cell references from multiple tabs.
Smartsheet (not using tabs) will require me to setup different sheets for each of my tabs which is fine, but some formulas require the use of data on different sheets.
Formula example:
==SUMPRODUCT(--(MONTH(SHEET1!P3:P1598)=B52)*(YEAR(SHEET2!P3:P1598)=C52))
The SHEET 1 & 2 are different tabs in the Excel file, which works fine, but I need to be able to reference different Smartsheets.
Is there a method for doing this?
Comments
-
You could simply create a link from the other sheet to this one using the cell linking tool on the left. This way, you could have cells directly linked to the data you want to get and you could use those as parts of your formulas.
Hope that helps,
Étienne
-
Etienne is right--Smartsheet links will solve your problem. Linking is one of the best features in Smartsheet and allows you to link up to 500 cells in a single operation with a maximum of 5000 incoming links per sheet.
-
Thats a good workaround, but ideally I dont want to have a duplicate of all the data on my sheet containing the formula calculations, or have all my analysis and data on the same sheet. A lot of my calculations use a range of cells as part of the formulas.
Currently I create a row of data for each job we do that has a total of approx 20 fields of data I use.
I have one sheet containing all my data which will be populated by a web form, a second sheet that collates it all by date (I make analyses month by month so gather totals or averages by month from my raw data).
Is there a way to export cards/rows to an excel file for analysis as they are created?
Does anyone have experiance using Zapier forthis kind of thing?Many thanks
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives