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.

Formula to reference cell in another sheet?

Walter Day
Walter Day ✭
edited 12/09/19 in Archived 2016 Posts

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?

Tags:
· Share on FacebookShare on Twitter

Comments

  • Etienne Desbiens
    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

    · Share on FacebookShare on Twitter
  • Walter Day
    Walter Day ✭

    Genius! Thank you, this is an excellent workaround.

    · Share on FacebookShare on Twitter
  • Heath Sanders
    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...

     

    Destination Sheet.PNG

    Main Sheet.PNG

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    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

     

    · Share on FacebookShare on Twitter
  • I_Rey
    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

     

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    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

     

    · Share on FacebookShare on Twitter
  • Gwyneth C
    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

     

     

     

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories