Please, need help with multiple "if" conditions

[Ron M.]

This spreadsheet has 5 columns.

In Column B, "District," you input the number of a school district.
There are 20 districts, so these will between 1 and 20, inclusive. Each
district will appear many times in the column (there are over 1,500
rows).

Now:

Dave is assigned to districts 1,3,4,6,9,17 and 18.
Bill is assigned to districts 2,5,11,12,14,15 and 20
Mary is assigned to districts 7,8,10,13,16 and 19.

(I'm guessing at these assignments - the actual assignments may be
slightly different)

What I need to do is configure this spreadsheet so that when the
district number is entered in Column B, the name of the person assigned
to that district automatically appears in Column E, "Assignment."

What I will do is create the spreadsheet with 1500 blank lines, but
with this formula/macro imbedded. Throughout the next several months,
the data will be entered by various people.

I can do a simple "if" formula where if you enter, say, "4" in Column
B, "Dave" will appear in Column E, but I can't get beyond that point.

I need this for a major project at work. Can anybody help?

Thanks very much

Ron M.

[George Nicholson]

1) On a separate sheet create a 2 column table. Column 1: numbers 1-20,
Column 2: Person Name. Lets call this table "Assignments"
2) In Help, look at the entry for Vlookup
3) You should then be able to construct a formula in Column E that takes the
numerical value entered in B and returns the corresponding Person name from
the Assignment table:
=Vlookup(A2,Assignments,2)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

"Ron M." <[email protected]> wrote in message
news:[email protected]...
> This spreadsheet has 5 columns.
>
> In Column B, "District," you input the number of a school district.
> There are 20 districts, so these will between 1 and 20, inclusive. Each
> district will appear many times in the column (there are over 1,500
> rows).
>
> Now:
>
> Dave is assigned to districts 1,3,4,6,9,17 and 18.
> Bill is assigned to districts 2,5,11,12,14,15 and 20
> Mary is assigned to districts 7,8,10,13,16 and 19.
>
> (I'm guessing at these assignments - the actual assignments may be
> slightly different)
>
> What I need to do is configure this spreadsheet so that when the
> district number is entered in Column B, the name of the person assigned
> to that district automatically appears in Column E, "Assignment."
>
> What I will do is create the spreadsheet with 1500 blank lines, but
> with this formula/macro imbedded. Throughout the next several months,
> the data will be entered by various people.
>
> I can do a simple "if" formula where if you enter, say, "4" in Column
> B, "Dave" will appear in Column E, but I can't get beyond that point.
>
> I need this for a major project at work. Can anybody help?
>
> Thanks very much
>
> Ron M.
>

[Theresa]

Depending on the number of names you could also use a nested if statement if
you didn't want to have other sheets, however it would be rather long and if
you wanted to change a name at some point or who the district was assigned to
you would have to edit the formula.

Example:
=IF(OR(B2=1,B2=3,B2=4,B2=6,B2=9,B2=17,B2=18),"Dave",IF(OR(B2=2,B2=5,B2=11,B2=12,B2=14,B2=15,B2=20),"Bill"))

The Vlookup function is definitely the way to go.

"Ron M." wrote:

> This spreadsheet has 5 columns.
>
> In Column B, "District," you input the number of a school district.
> There are 20 districts, so these will between 1 and 20, inclusive. Each
> district will appear many times in the column (there are over 1,500
> rows).
>
> Now:
>
> Dave is assigned to districts 1,3,4,6,9,17 and 18.
> Bill is assigned to districts 2,5,11,12,14,15 and 20
> Mary is assigned to districts 7,8,10,13,16 and 19.
>
> (I'm guessing at these assignments - the actual assignments may be
> slightly different)
>
> What I need to do is configure this spreadsheet so that when the
> district number is entered in Column B, the name of the person assigned
> to that district automatically appears in Column E, "Assignment."
>
> What I will do is create the spreadsheet with 1500 blank lines, but
> with this formula/macro imbedded. Throughout the next several months,
> the data will be entered by various people.
>
> I can do a simple "if" formula where if you enter, say, "4" in Column
> B, "Dave" will appear in Column E, but I can't get beyond that point.
>
> I need this for a major project at work. Can anybody help?
>
> Thanks very much
>
> Ron M.
>
>

[Ron M.]

I used the VLOOKUP function and it worked fine. This was my first time
to use it, so I had to hack on it a bit, but I figured it out. One
thing, though: instead of naming the table, I just used the cell range.
The cell range of the table is M26 to N45. So the VLOOKUP formula is:

=(VLOOKUP(E2,$M$26:$N$45,2))

The district number is entered in Column E. The name of the person
assigned to that district appears in Column J, as a result of the
VLOOKUP formula.

This has caused something else, though, that I'm trying to fix. In the
empty rows where no district numbers have been entered yet, I get the
"#N/A" error message in Column J. Since there are still several
thousand empty rows, this looks kinda ugly. Can somebody tell me how
to tweak the formula so the cell in Column J would just stay blank
until a district number is entered in Column E?

Ron M.

[George Nicholson]

I find this to be reallllly klunky (because of the performance hit incurred
by invoking Vlookup twice if it is True), but the common solution is to use
the ISERROR or ISNA functions in an IF statement:

=IF(ISNA(VLOOKUP(E2,$M$26:$N$45,2)), "", VLOOKUP(E2,$M$26:$N$45,2))

Another approach might be to set a Conditional Format where the font color =
cell backgound (white) if this formula is True: =ISNA(J2)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Ron M." <[email protected]> wrote in message
news:[email protected]...
>I used the VLOOKUP function and it worked fine. This was my first time
> to use it, so I had to hack on it a bit, but I figured it out. One
> thing, though: instead of naming the table, I just used the cell range.
> The cell range of the table is M26 to N45. So the VLOOKUP formula is:
>
> =(VLOOKUP(E2,$M$26:$N$45,2))
>
> The district number is entered in Column E. The name of the person
> assigned to that district appears in Column J, as a result of the
> VLOOKUP formula.
>
> This has caused something else, though, that I'm trying to fix. In the
> empty rows where no district numbers have been entered yet, I get the
> "#N/A" error message in Column J. Since there are still several
> thousand empty rows, this looks kinda ugly. Can somebody tell me how
> to tweak the formula so the cell in Column J would just stay blank
> until a district number is entered in Column E?
>
> Ron M.
>

[Steve McBride]

Try this formula:

=IF(ISNA(VLOOKUP(E2,$M$26:$N$45,2)),"",(VLOOKUP(E2,$M$26:$N$45,2)))

It will return a blank if it doesn't find a match.

"Ron M." <[email protected]> wrote in message
news:[email protected]...
> I used the VLOOKUP function and it worked fine. This was my first time
> to use it, so I had to hack on it a bit, but I figured it out. One
> thing, though: instead of naming the table, I just used the cell range.
> The cell range of the table is M26 to N45. So the VLOOKUP formula is:
>
> =(VLOOKUP(E2,$M$26:$N$45,2))
>
> The district number is entered in Column E. The name of the person
> assigned to that district appears in Column J, as a result of the
> VLOOKUP formula.
>
> This has caused something else, though, that I'm trying to fix. In the
> empty rows where no district numbers have been entered yet, I get the
> "#N/A" error message in Column J. Since there are still several
> thousand empty rows, this looks kinda ugly. Can somebody tell me how
> to tweak the formula so the cell in Column J would just stay blank
> until a district number is entered in Column E?
>
> Ron M.
>