Need help - Multiple if function

[wildenbeast]

Hello,

I am trying to write a formula using if function which has the logical result of this:
If A1="x", C1= B1. Since B1 here is not a number, I am struggling.

Any suggestions?

[Sam Capricci]

If then statements have the elements of IF this condition is true do this, if not do that. So what are you trying to accomplish with if A1=x, then do what with C1 or B1? is this an if AND statement? =IF(A1="x",C1,B1) would be that if A1 equals x then you put the value in C1 somewhere, otherwise you put the value in B1 somewhere. or =IF(AND(A1="x",C1=B1),then do this, otherwise do that)
Does that make sense?

[TheMechEngineer]

Is this what you are looking for?

Enter "x" into A1 and you will see that C1 turns into whatever value B1 is.

[Lewis.Henderson88]

Need help with a multi IF formula.

If A1 is ≥ 10000 then B1 equals "Large-cap"
If A1 is < 10000 but ≥ 2000 then B1 equals "Mid-cap"
If A1 is < 2000 but ≥ 300 then B1 equals "Small-cap"
If A1 is < 300 but > 0 then B1 equals "Micro/Nano-cap"
If A1 equals 0 then B1 equals "Private"

I can get the formula to work for "Large-cap" and "Mid-cap" but does not work accordingly when entering < 2000.

[wildenbeast]

It does make sense. Though it is not exactly what I am trying to accomplish. This is for a private school attendance tracker. They accept students from different districts and here is what I am trying to do: Lets say, I have student X on my roster. In his row, A1 is "Wright School District" and A2 is "P" as in present. There is another tab for invoicing where I want to only populate the attendance of the students from this specific district. The formula I am trying to write is this: If student's school district is "wright", copy A2 (which is P) to cell B2 (which is in another tab) So I want P to populate, not the count. Does that make sense?

[wildenbeast]

If then statements have the elements of IF this condition is true do this, if not do that. So what are you trying to accomplish with if A1=x, then do what with C1 or B1? is this an if AND statement? =IF(A1="x",C1,B1) would be that if A1 equals x then you put the value in C1 somewhere, otherwise you put the value in B1 somewhere. or =IF(AND(A1="x",C1=B1),then do this, otherwise do that)
Does that make sense?

It does make sense. Though it is not exactly what I am trying to accomplish. This is for a private school attendance tracker. They accept students from different districts and here is what I am trying to do: Lets say, I have student X on my roster. In his row, A1 is "Wright School District" and A2 is "P" as in present. There is another tab for invoicing where I want to only populate the attendance of the students from this specific district. The formula I am trying to write is this: If student's school district is "wright", copy A2 (which is P) to cell B2 (which is in another tab) So I want P to populate, not the count. Does that make sense?

[Sam Capricci]

So if I understand correctly, you want something like this... =IF(A1="Wright School District",A2,"")
Since I don't know how good your skills are in excel, begin this way
Click in the cell you want the results in, hit the = sign, then put IF and a left parens "(" then go to the sheet where the school district is and click in cell A1 then add the equal sign and type in quotes the school district name, then add A2 and then the blank quotes then close the parens and it should be the formula you want based on what you wrote in post 6. If that isn't what you want you might need to give more detail or post a sample sheet.

[wildenbeast]

Thanks for simplifying it. That is unfortunately not the formula I am looking for. Here is the sample excel sheet. Class attendance shows all students' attendance based on their school district. The tab on the very left shows their attendance for a specific school district. The SELPA for is a government form that cannot be changed. Therefore, I am trying to write a formula to auto populate students attendance for that school district.

Please see my notes in the workbook.

Thanks

So if I understand correctly, you want something like this... =IF(A1="Wright School District",A2,"")
Since I don't know how good your skills are in excel, begin this way
Click in the cell you want the results in, hit the = sign, then put IF and a left parens "(" then go to the sheet where the school district is and click in cell A1 then add the equal sign and type in quotes the school district name, then add A2 and then the blank quotes then close the parens and it should be the formula you want based on what you wrote in post 6. If that isn't what you want you might need to give more detail or post a sample sheet.

[Sam Capricci]

This is one way you could do it =VLOOKUP($B11,'Class Attendance'!$A4:E4,COLUMN(E$1),FALSE) dragged right and down. I would recommend getting rid of the merged cells, they create problems for the formulas.

[FlameRetired]

Agree with Sambo kid on the merged cells.

Additionally the mixed date references cause havoc.

The SELPA for is a government form that cannot be changed.

With the understanding that other sheets may be altered:

One work around is to use a helper row in 'Class Attendance'. I inserted one in row 1. Then put a lookup table in JE2:JF13 to lookup months and month numbers. That streamlines the helper formula in E1:IZ1.

Please Login or Register  to view this content.

Then in 'SELPA Attendance Santa Rosa' cell F11 array enter this formula and fill across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

The helper row and the lookup table can be hidden. The lookup table can even be moved to a different sheet.

You didn't ask for it, but is appears you could use another formula in the Total Student Attendance column (same sheet).

Please Login or Register  to view this content.

I also custom formatted the helper row m/d.

[wildenbeast]

Thank you Sambo kid and FlameRetired.Correct me if I am wrong, but my understanding here is that this is not filtering different school districts. So on the same class attendance sheet, if I have all 15 students listed representing 5 different school districts, I would need to have 5 SELPA tabs with different district names and need only the students representing that specific school district to auto populate on their specific SELPA sheet. Am I wrong? (I hope I am )

[FlameRetired]

So on the same class attendance sheet, if I have all 15 students listed representing 5 different school districts, I would need to have 5 SELPA tabs with different district names and need only the students representing that specific school district to auto populate on their specific SELPA sheet. Am I wrong? (I hope I am )

That depends.

The tab on the very left shows their attendance for a specific school district. The SELPA for is a government form that cannot be changed. Therefore, I am trying to write a formula to auto populate students attendance for that school district.

That's where I got confused.

Did you want multiple districts reported in the same SELPA form? I wasn't clear on that point. There is no indication that district output is expected in that form nor if district is a specific input criteria. And of course you don't have liberty to alter the form. I followed your lead from the preceding posts and from "Santa Rosa" being the only district listed in 'Class Attendance'.

If no district specific info is required on the form then remove *('Class Attendance'!$C$5:$C$19="Santa Rosa") from my formula and array enter as before.

Of course now if there is more than one "David Smith" from multiple districts there would be a problem.

Between that and this:

I would need to have 5 SELPA tabs with different district names and need only the students representing that specific school district to auto populate on their specific SELPA sheet. Am I wrong? (I hope I am )

it would appear that is exactly what SELPA expects.

Thoughts?

[wildenbeast]

Each SELPA form needs to represent one school district only. The class attendance is for all students from various school districts. In SELPA Santa Rosa, I only want to list students from Santa Rosa district and show their attendance. If I had SELPA Wright District, I would only list Wright District students on that sheet.

The question is, is there a way of making a cell equal to another cell based on a condition? In a perfect world, there would be a formula that can represent A1=A2 if B1 is "x" which is what I am trying to do. Maybe there is, but it is not as simple as I think it would be. Any suggestions?

[FlameRetired]

Each SELPA form needs to represent one school district only.

The question is, is there a way of making a cell equal to another cell based on a condition? In a perfect world, there would be a formula that can represent A1=A2 if B1 is "x" which is what I am trying to do. Maybe there is, but it is not as simple as I think it would be. Any suggestions?

Re: Form output
Does output in the form need to show District? Where would it go?

Re: Form input

• What is "Name of NPS" in the form? Is there any chance District name goes there?
• If not would a lookup table that associates Teacher's Name (E3 of the form) with District be possible? It could be added to another sheet. This works only if the form ships independent of the rest of the sheets ... ie prints or is sent as a workbook that includes that separate lookup sheet in the filing.
• If not can you include / concatenate District name with the Student # name in column B? While that can work the solution would be less than ideal.

[wildenbeast]

Re: form output
No it does not. The tab name is the identifier for district name. These pages are eventually printed and sent to the district themselves.

Re: For Input
NPS meand Non Public School. So it is the school's name and District name does not go there.
I don't need to show the district name on any of the SELPA sheets by any means. I want that to be the condition for it to populate attendance on the right sheet. If a student is from that specific district, I want their attendance to copy to that district's SELPA sheet. Does that make sense?

[FlameRetired]

Yes.

To specify which district to query in each sheet you can either hand type that criteria as in my first formula for each SELPA sheet or do it by associating ... for example ... the teacher with the district. That would automate the process but require a lookup table.

Which do you prefer?