Pulling data from one sheet and tabulating in another

K Goforth

I have a database containing survey data. I would like to create another sheet with formulas that sort and tabulate the data so that the original database is separate from the analysis. I'm also very new to writing formulas.

From what I can understand, I need to use INDEX or COLLECT or MATCH? On the main survey data sheet entitled Survey Data, in the OHSU Role column, someone indicates if they are faculty or administration. Then in Time at OHSU, it shows how many years they have been at the institution.

If I were summarizing data on another row on the main Survey Data sheet itself, the formula would be =COUNTIFS([OHSU Role]:[OHSU Role], "Faculty/Researcher", [Time at OHSU]:[Time at OHSU], "0 to 5 years")

How would I write that same formula on a summary sheet, called Summary View, so it would give me the answer on the summary sheet and not the main Survey Data sheet?

Lucas Rayala

Hi @K Goforth , it’s exactly the same formula, you just need a cross sheet column reference in place of the on-sheet column reference. When you create a formula, you’ll see the option to insert a cross sheet reference in the formula helper wizard. Select that and navigate to the sheet, then select the column you want to evaluate. Make sure you update the title of the cross sheet reference title to something that makes sense, or else looking back you’ll just see {Sheet Name Reference 1}, {Sheet Name Reference 2}, etc. Your formula will be this:

=COUNTIFS({OHSU Role}, "Faculty/Researcher", {Time at OHSU}, "0 to 5 years")