cell linking and formulas

Diana Davila

I am currently working on a Dashboard sheet.. that i would like to count the number of each status within the first sheet.  

For Example 

Sheet 1 - contains 150 rows of data with different stages of Status 

Sheet 2  - Would like to create a formual that would go back to Sheet 1 and count a specific status and advise the count. 

 

the key is i do not want to show the formula on Sheet 1 as that is shared with the customer and would like for my counts to be an operational check internally ( so creating a non shared sheet) 

 

PPS Solutions

Hi Diane, have you tried cell linking from your control sheet 2 and then using a CountIF formula in Sheet 2 to count the occurences of the Status/s you need to total?

 

Diana Davila

Peter, 

 

Yes but reality it only copies the same data so not as cleaned up as i would like.   Pretty much having to duplicate the sheet and do the count seperate.  

Travis

Diana, if you have a column in Sheet 1 that counts each status then is linked to Sheet 2, you could hide the column in Sheet 1. If your customers are Editors on the sheet, they cannot unhide columns. 

PPS Solutions

I agree Travis, that is another option if the csutomers cannot see the columns that need protecting

Jeremy Michels

Can you not do this in a report and not select the columns and be able to edit the data and only have one sheet. I have done the two sheet approach. The reporting approach is nicer for me. I can select the columns and rows the customer can only act on the data in the report. It is live information and is acurate everytime they open it. You can also adjust this per customer. That way if you only want them to see the rows that are assigned to them, that is all they see. Alot of power in Reports. 

Travis

Jeremy, the report option is a good idea but in order to see and edit the data in the report, the user would have to be shared to (as at least an Editor) to the underlying sheet(s). If they are not shared to the sheet(s) the report is pulling from, they will see a blank report.  If you wanted to provide a read only version of a section of a sheet (or multiple sheets), you could build a report to show the data you want to share, then publish it using the Publish tab at the bottom of the report. 

Stan Duncan

Are there any updates to this? I do not see this feature request listed in your product roadmap.

Travis

Hi Stan,  I do not have any updates to this request. I will update this thread when there is additional information I can share.

 

Thanks for asking! 

miribilist

Can you link a cell from another sheet directly into a formula without the need to link the data and then create the formula?

Annemieke Pot

Someone already raised a question that I now run into, however, I cannot see a reply, so I am reposting: Can you link a cell from another sheet directly into a formula without the need to link the data and then create the formula?

Thx for some guidance!