Use linked cells as part of a formula

miribilist

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?

Etienne Desbiens

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

Jim Hook

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.

miribilist

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