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.

Reference a Cell in another Sheet in a Formula?

Bill Leonard
edited 12/09/19 in Archived 2016 Posts


I'd like to count the number of occurances of a specific value in a column on another sheet. It works great this way, but within the same sheet:

=COUNTIF([Column4]17:[Column4]26, "Red")


Is there a way to select a range of cells on another sheet and return (in this case) the number of them that are Red?




  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    Presently you cannot reference another sheet.  


    The best way to accomplish the same functionality would be to use the COUNTIF() formula in your sheet with all of the "Red" items.  Then you could link a cell in the receiving sheet to that COUNTIF value.



  • Bill Leonard

    Thanks Brett!  I will give that a whirl. The only issue is that the sheet it's in will have ever-growing number of rows, so hard to figure out where to hide it!

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    to sum a growing number of rows just sum the whole column without referencing row numbers

          =COUNTIF([Column4]:[Column4], "Red")


    You can put this in a specific column and hide the column.  Even if the column is hidden you can pull the info to another sheet.  


  • SRenner
    SRenner ✭✭✭

    Hi, has there been an update to this question yet?

    I have a master data sheet that I want to pull all my information from to other sheets.  In some cases all I want from the data is a cell reference and no formula.  

    for example in sheet1 I have cell (column1 row 1) that says "accept". 

    I have a second sheet called sheet2 that i want (column4 row 1) to say what sheet1 (column1 row1) says "accept"

    how do I make this possible?



This discussion has been closed.