Index formula versus If And

maineL

Hi,

First time posting. I have several sheets I link together. Long story short is web applications pasted into one sheet populate into another for vetting for eligibility of employment. That sheet will then provide the data for my drop downs into the Active Sheet which will pull the corresponding rows of data based on the person's name. All that is working. I'm now trying to create a formula from a "helper sheet" where I have fall and spring stipends that I would like to populate based on whether F or S is selected and if the course is 15 or 20 hours for the semester.

I have been using Index, but I'm thinking I could use the If And formula, but unsure of how to make it work. This is the index formula I've been using, which works, but if I have someone who is only TA'ing in the Fall, I want the spring stipend column to remain blank and same for spring TA'ing and leaving fall blank.

=INDEX({UpdateRates 15Stipend}, MATCH(Term@row, {UpdateRates Term}, 0))

Any ideas what I might be doing wrong?

Thanks so much!

Lori

Paul Newcome

Right. You'll need to change the very first "S" to an "F" in the Fall column.

Paul Newcome

Right. You do not need 4 separate formulas. There is no need to break out the 15 hours and 20 hours into separate columns. The formula I provided already accounts for that using the bold portion:

=IF(Term@row = "F", INDEX(IF(Hrs@row = 15, {UpdateRates 15Hrs}, {20 Hour Column}), MATCH(Term@row, {UpdateRates Term}, 0)))

That IF statement is telling the formula to look in either the 15 hour column of the reference sheet or the 20 hour column of the reference sheet depending on what is in Hrs@row. The issue is that the {20 Hour Column} cross sheet reference has not yet been created in your sheet.

Paul Newcome

I am assuming that the far left column in the second screenshot is the one designating F/S. Is it possible it could be both?

maineL

Sorry I hit yes answered. Yes the Term column on the far left is the F/S column. No it can only be one or the other.

Paul Newcome

Ok. I will first suggest putting the dollar amounts in the 20 hours column in your first screenshot on the same row as the dollar amounts in the 15 hours column.

Term ..... 15H ..... 20H

F..............$$..........$$

S..............$$..........$$

Then you can use something like this in the Spring Stipend column and then just change that first "S" to an "F" for the Fall Stipend column:

=IF(Term@row = "S", INDEX(IF(Hrs@row = 15, {UpdateRates 15Stipend}, {20 Hour Column}), MATCH(Term@row, {UpdateRates Term}, 0)))

maineL

That works for Spring 15 hours. I'm overthinking this. Looks like it's populating both 15 Stipend and 20 Stipend columns with the same dollar amount. If I change Term to F, the columns are blank. Which is what I want, but if S is selected, I want the F Stipend column to be blank.

nk. Does that make sense?

Paul Newcome

Right. You'll need to change the very first "S" to an "F" in the Fall column.

maineL

Perfect! Thank you so much!!!! That leads me to another question of getting the HRS column to change when the Course changes. I currently have this formula:

=INDEX({Data RangeHrs}, MATCH(Course@row, ({Data RangeClass})))

But, it only seems to change when I select the End 1445 course.

The course selection and hours are on my Data "helper sheet":

I've tried several times and I'm unsure where I'm going wrong! Thank you so much for all of your help!!!!

Lori

maineL

So on the previous topic, The formula does work, but I'm trying to get it to do the following.

Term F/S in their respective columns for Fstipend/Sstipend

Hrs 15/20 to change to the correct dollar amount for the respective Stipend Column and dollar amount. Right now it's doing it for 20 hours in the spring stipend column, but not for 15 hours in the Spring column. I think I did something wrong?

In a perfect world, I'd like the Term and hours to populate the correct stipend column with the correct amount. I was hoping to do a formula for the column, but I'm guessing it will have to be a "cell" formula instead?

Thanks again! Sorry for the roundabout!

Lori

Paul Newcome

This should be working as a column formula but you need technically two separate formulas. One for the Spring column with the "S" at the beginning and one for the Fall column with the "F" at the beginning.

Exactly what formula do you have in there that is producing the error and what error is it?

maineL

Ok, I'm back on this one. So the 15 or 20 hour rate is what I was hoping to get to appear in the correct column with the correct amount. So the page with the rates:

And the formula I'm using is: =IF(Term@row = "S", INDEX(IF(Hrs@row = 15, {UpdateRates 15Hrs}, {20 Hour Column}), MATCH(Term@row, {UpdateRates Term}, 0)))

Is there a way to make the formula interchangeable based on the Hrs (15 or 20) so I can have it autopopulate or should I set up 2 columns for Fall (15 & 20) and Spring?

Thank you!

Lori

Paul Newcome

That is what the bold piece below is doing:

=IF(Term@row = "S", INDEX(IF(Hrs@row = 15, {UpdateRates 15Hrs}, {20 Hour Column}), MATCH(Term@row, {UpdateRates Term}, 0)))

Although in your previous screenshots the 15 and 20 were right justified in their cells (indicative of numerical values), but in your most recent screenshot they are left justified (indicative of text strings). Did you apply any kind of formatting to this column?

maineL

Oh, yes I tried to align everything to the left. Would that make a difference? I can set it back to right justification. So based on the formula above, I won't be able to autopopulate the column as there are 2 separate formulas. Would this work if I created the extra columns (one for 15 hours F and S and one for 20 hours F and S)?

Paul Newcome

If you adjusted the formatting then there is no issue. If you had not done anything with the formatting then we may have had to tweak the formula a little bit.

You can apply the above as a column formula in the Spring column. The "two separate formulas" are one for Spring and one for Fall. Both should be able to be applied as column formulas. One goes in the Spring column and the other goes in the Fall column (after changing the s to an f).

maineL

So that worked for the Spring column, but in changing the F to the Fall Column, I get Invalid Ref:

=IF(Term@row = "F", INDEX(IF(Hrs@row = 15, {UpdateRates 15Hrs}, {20 Hour Column}), MATCH(Term@row, {UpdateRates Term}, 0)))

maineL

I see it has something to do with the 20 hrs. If that helps?

Paul Newcome

Make sure you set up the {20 Hour Column} cross sheet reference correctly.