Pulling data from one sheet and tabulating in another
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?
Best Answer
-
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")
Answers
-
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")
-
That did it, thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!