Calculating sheet based off data in another sheet

maineL

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

Genevieve P.

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

Colleen Patterson

@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.

maineL

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

Jon Mark H

@maineL

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

maineL

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!

Jon Mark H

@maineL

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

maineL

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!!!!

maineL

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!!!

Genevieve P.

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

maineL

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!