Calculating sheet based off data in another sheet
I am working between several sheets. I am trying to pull back what courses may be missing for either Fall or Spring, respectively, and which account is left to be charged. My first sheet, Count (below), is where I'm trying to have the quick visual:
So I want my Active TAships (below) sheet data to calculate into my Count sheet above.
So for example, BioG 1445 offers 8 TAships on the State account and 2 on the endowed each semester. So I'm trying to pull the term below into the Open F/S columns above. So if there's an open TAship, my Active TAships sheet will notify the Count sheet above and it will highlight green with the number of openings. Otherwise, Open F/S will be red with a "0".
Any ideas? It gets confusing quick, so this visual will come in VERY handy.
Thank you!
Lori
Best Answer

Hey @maineL
For your first issue, this is because you're subtracting the Spring total (1) from the Total Lines (3) to get the result. If you're putting this formula into the SPRING column and you're just looking for how many times it appears on the other sheet, remove out the [Total Lines]@row  from your formula completely, so you only get the COUNT.
You can simply use the COUNTIFS without subtracting anything at all. 🙂
For your second problem, as long as the data in your source sheet either does or does not have "End" in the front, it should look at these as two separate values. Is there any chance there are more on your source than expected? Could you try adding a Filter to your source sheet to check how many rows there are?
Cheers,
Genevieve
Answers

@Lori Do you have a data column that is an exact match in both sheets that you can use as the lookup / match?
If so, you would be able to pull a multifunctional request. I would be happy to run through these scenarios with you.
Smartsheet Community Champion and Ambassador
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

So I am still having issues trying to get this to clearly show me which courses have any openings and if it's spring or fall. Any ideas? Thank you!
Lori

See if this solves your problem:
In the "Open Fall" Column place this formula:
=Cap@row  COUNTIFS({Reference1}, Course@row, {Reference 2}, "F")
"Open Spring" will have this formula:
=Cap@row  COUNTIFS({Reference1}, Course@row, {Reference 2}, "S")
Where "{Reference1}" is a reference to the "Course" column (via referencing another sheet in the formula)
and "{Reference2}" is a reference to the "Term" column
Let me know if this makes sense or not & if it meets your needs!
Jon

Thanks, Jon! That works. But what I'm looking to have it return is ONLY if there is an opening for Fall/Spring, rather than how many are filled.
So in this instance I know the BioG 1445 has 1 opening in Fall, but it's counting all that are filled. Des that make sense? Thank you!

Did you put the "Cap@row  " portion in the formula?
The formula (in Open Fall" column should work as follows:
The quantity in "Cap", (8) in this instance of BioG 1445, subtracts all instances of BioG 1445 Fall in the other sheet, leaving (4) spots remaining.
Also wanting to double check I'm understanding: the "Cap" quantity applies to EACH Semester (Fall / Spring)  so it's (8) available for Fall & (8) available for Spring, correct?
Finally  would it be beneficial for your "Filled" column to rather be (2) columns  (1) that's "Filled Fall" & (1) that's "Filled Spring"?
Let me know if this solves the problem or if I'm not understanding.
Jon

Hi Jon,
Here is what I put in the Open Fall column:
As you can see for BioG 1445 it shows Open Fall, 4, and Open Spring 5. It should only be showing 1 open for the Fall. I think I have something reversed. So where the Open Fall/Spring are, if there are no more openings, it should be at 0. Sorry I'm obviously missing something probably easy!
Honestly I can probably get rid of the Filled column is I have Open Fall/Open Spring working properly. It's just a quick visual for us to use to know which courses still need to be filled. As always, THANK YOU!!!!

Ok, I've gotten closer, but for some reason the data is being pulled into the wrong columns. For example, my Fall Filled courses are populating with Spring Filled:
The formula I'm using is:
It's almost like it's grabbing the opposite of what I want it to grab.
That's 1 problem. The second being the BioG 1445: Introduction to Comparative Anatomy & Physiology course (3rd from bottom), is pulling back data for BOTH the last option (End BioG 1445) AND the BioG 1445 Courses. It's not differentiating that these are 2 different courses. Any ideas? This is driving me crazy!!! 🙄 Thank you!!!

Hey @maineL
For your first issue, this is because you're subtracting the Spring total (1) from the Total Lines (3) to get the result. If you're putting this formula into the SPRING column and you're just looking for how many times it appears on the other sheet, remove out the [Total Lines]@row  from your formula completely, so you only get the COUNT.
You can simply use the COUNTIFS without subtracting anything at all. 🙂
For your second problem, as long as the data in your source sheet either does or does not have "End" in the front, it should look at these as two separate values. Is there any chance there are more on your source than expected? Could you try adding a Filter to your source sheet to check how many rows there are?
Cheers,
Genevieve

YOU DID IT!!!! THANK YOU! I've been pulling my hair out trying to figure out what I was doing wrong!!! The 2nd problem fixed after correcting the first problem! You are a GENIUS! Thank you so much!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!