Reference a Cell in another Sheet in a Formula?

Howdy!

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?

Thanks!

Find more posts tagged with

Formulas

Cell linking

Comments

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.

https://www.smartsheet.com/cell-linking

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

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

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?

Thanks

Scott