Formula to reference cell in another sheet?

Walter Day

Hi, I'm wondering if it's possible to reference a cell in a different sheet with a formula. My source sheet is intended for users to add entries, and my destination sheet is intended to keep track of when the source sheet was last updated. Because entries are added to the source sheet, and I always want the newest date on the destination sheet, I can't use cell linking. I need a static link to another sheet, similar to Excel's "Sheet_name!Cell_address" reference (I pulled that from https://www.ablebits.com/office-addins-blog/2015/12/08/excel-reference-another-sheet-workbook/ if more info is required).

 

Basically, I want to have some form of:

 

=First_Aid_Kit_Inspections!Date1

 

to show the created date (the value of the Date column) of the entry in row 1 (the newest entry) on the First Aid Kit Inspections sheet. Is this possible?

Etienne Desbiens

Hi!

 

You can't use a formula to read a cell in another sheet... I still have a solution (if I understand your problem correctly!). If you created a cell with this formula :

=MAX(Created:Created)
(where Created = the name of the collumn of the creation dates)

and link the result to your other sheet using cell linking, you can get the most recent date.

 

I hope that helps!

 

Étienne Desbiens

Walter Day

Genius! Thank you, this is an excellent workaround.

Heath Sanders

Hi Team,

I have a situation where my destination sheet will grab the data from main sheets (Two reps with separate sheets) 

Because I filter in the main sheet, the cell linking doesn't work (Doesn't follow the row)

My question is, will what you have discussed here fix the problem ? If I send the row to another sheet will if follow it ? Can you give me an example of how the formula should look please....I looked at link you posted, still unsure how to do it. Thank you so much...

 

J. Craig Williams

Heath,

Was this part of your other post that I saw over the last day or so? (You would have written it earlier, I'm catching up)

I am still confused when you say sorting and/or filtering messes up your cell links.

Craig

 

I_Rey

I am new to Smartsheet.  Is there a way to have a "database" of sorts, and use a formula similar to a V_Lookup in Excel?  I want to have a master database (or Sheet) of contacts (not necessarily Smartsheet users), and needs to have name, numbers, email, etc..  I wanted a master contact sheet, then for new projects just have a dropdown for contacts to select to add to the project, instead of entering the data every time I have a new project.  

Thanks in advance,

Isaac

 

J. Craig Williams

I_Rey,

Smartsheet does not yet have a cross-sheet lookup function.

Depending on use case, cell-linking may solve some or all of your problems.

Smartsheet has a premium add-on called "DataMesh" that may also fulfill some of your requirements. (I do not endorse it)

We (at Smarter Business Processes) have used AppSheet to sync sheets between two Smartsheet Sheets, or other spreadsheet in the cloud sources (Excel in Dropbox, Google Sheets, etc...). 

Also, Smartsheet has announced that they are working on cross-sheet lookups of some fashion. The last I recall is that it will be rolled out to the Early Adopter Program (EAP) in Q1 2018, but they may have changed that somewhere I have not seen.

Back to the cell linking solution, this post (http://ronin-global.com/2017/04/22/an-alternative-to-nested-ifs/) does not explicitly call it out, but if you look closely, incoming cells of the example are cell-links. The part missing from the article (on purpose) is that the output links back to the first sheet - thus having a 'lookup' across sheets. There are two primary limitations to cell linking -- the first being the number of cells that can be linked into a sheet (5000) and not being able to link by rows or columns, only individual cells (though you can do them in batches).

Craig

 

Gwyneth C

Updating this thread with the news that Smartsheet now supports cross-sheet references for VLOOKUP and other functions.

More detail and resources are available here:

https://community.smartsheet.com/announcement/new-formulas-feature-reference-data-other-sheets