Nested IF problem - help please

[Dasin]

Our spreadsheet pulls values from a column when a month is inserted in
a certain cell:

OCT is typed in B2 and in the column below it, from B10 down to B286,
data (numbers) are pulled from the October column K10 through K286
using =IF($B$2="Oct",K10,IF(and so on for each month). IF Nov is typed
in B2 then the same happens except data is pulled from the November
column, L. The monthly columns from K through V are tied to and updated
from other sheets. As you can see the problem is that nested functions
allow only 7 and I need 12, one for each month. I have looked at the
VLOOPUP and the HLOOKUP but our data is not set up that way (tables)
since they need to be exact numbers pulled from the monthly columns
that are tied to other sheets.
OCT (B2)

OCT NOV DEC
Complaint 3 (B10) 3
6
Inspection 10 (B11)
10 2
NOV 11 (etc)
11 5
Door Notice 1 and
so on
Recheck 32
Citation 2
Work Order 0
Demolition 0

Don't know if when this is posted it will line up OK.

Any help appreciated.

Thanks,

James

[DOR]

Entr in B9

=INDEX(K9:V9,MATCH($B$2,$K$8:$V$8,0))

and copy/drag down as far as necessary (through row 286)

If you could spare a cell, say B3, to contain the index value, you
could put in it

=MATCH($B$2,$K$8:$V$8,0)

and change the first formula to

=INDEX(K9:V9,$B$3)

The latter approach would be more efficient.

HTH

Declan O'R



Dasin wrote:
> Our spreadsheet pulls values from a column when a month is inserted in
> a certain cell:
>
> OCT is typed in B2 and in the column below it, from B10 down to B286,
> data (numbers) are pulled from the October column K10 through K286
> using =IF($B$2="Oct",K10,IF(and so on for each month). IF Nov is typed
> in B2 then the same happens except data is pulled from the November
> column, L. The monthly columns from K through V are tied to and updated
> from other sheets. As you can see the problem is that nested functions
> allow only 7 and I need 12, one for each month. I have looked at the
> VLOOPUP and the HLOOKUP but our data is not set up that way (tables)
> since they need to be exact numbers pulled from the monthly columns
> that are tied to other sheets.
> OCT (B2)
>
> OCT NOV DEC
> Complaint 3 (B10) 3
> 6
> Inspection 10 (B11)
> 10 2
> NOV 11 (etc)
> 11 5
> Door Notice 1 and
> so on
> Recheck 32
> Citation 2
> Work Order 0
> Demolition 0
>
> Don't know if when this is posted it will line up OK.
>
> Any help appreciated.
>
> Thanks,
>
> James

[bpeltzer]

How about a MATCH with an OFFSET:
The match part will convert the month to a number.
Ex:
=MATCH("Oct",{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0) returns 1 through 12, indicating which month was selected.
Then the OFFSET can move you over that many columns from a reference cell.
Ex:
=offset(A10,0,match($B$2,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0))
This figures out what month you're looking at, and moves right that many
columns from the reference cell, a10 in this case.
--Bruce

"Dasin" wrote:

> Our spreadsheet pulls values from a column when a month is inserted in
> a certain cell:
>
> OCT is typed in B2 and in the column below it, from B10 down to B286,
> data (numbers) are pulled from the October column K10 through K286
> using =IF($B$2="Oct",K10,IF(and so on for each month). IF Nov is typed
> in B2 then the same happens except data is pulled from the November
> column, L. The monthly columns from K through V are tied to and updated
> from other sheets. As you can see the problem is that nested functions
> allow only 7 and I need 12, one for each month. I have looked at the
> VLOOPUP and the HLOOKUP but our data is not set up that way (tables)
> since they need to be exact numbers pulled from the monthly columns
> that are tied to other sheets.
> OCT (B2)
>
> OCT NOV DEC
> Complaint 3 (B10) 3
> 6
> Inspection 10 (B11)
> 10 2
> NOV 11 (etc)
> 11 5
> Door Notice 1 and
> so on
> Recheck 32
> Citation 2
> Work Order 0
> Demolition 0
>
> Don't know if when this is posted it will line up OK.
>
> Any help appreciated.
>
> Thanks,
>
> James
>
>

[DOR]

OOOPS!

I should have added that I assumed your month headers (OCT,NOV, etc.)
were in K8:V8. If they are in a different row, substitute that row
number for the number 8.

Declan

[Dasin]

Thanks for the response! I really appreciate it.
I haven't used MATCH or INDEX before, but I think I get the drift. I
tried both of your suggestions but I am getting a #N/A in the
cells/column where I tried it. I tried using B4 for the index and got
the same error.
Just figured it out. It is case sensitive! If I type oct I get the
error, if I type Oct it works!
Is there a way around this? You know users, some won't type the correct
Oct, Nov etc. and will get the #N/A error and I'll be getting calls.
But what an elegant solution to the IF problem!!!

Thanks,

James

[DOR]

James,

MATCH, as used, should not be case-sensitive. I believe the problem
may be due to a space being entered with the value "oct", either before
or after, and therefore not visible.

There are two solutions:

1. Substitue TRIM($B$2) for $B$2 in the MATCH function - this removes
surrounding spaces

or, preferably,

2. Use Data Validation to force entry of the correct value in B2. -
Select B2, and choose Data/Validation from the menu. Under Allow:
choose List. Then click in the Source: field that appears and then
highlight the list of months in your spreadsheet. This should cause
=$K$8:$V$8 to appear in the Source field. Click OK.

Now, when you click in B2, a drop-down arrow will appear and willenable
selection of a valid month. other entries can be keyed in but will be
rejected.

HTH

Declan O'R

[Dasin]

I didn't see any blanks, but I tried the drop down box and it works
great! Now my issue is that the font in the drop down box is extremely
small for some reason, but when it populates B2 it takes on the font
size of B2. I tried changing the font size on K9:V9 where it is pulling
the list from, but it didn't help. I tried it on a blank sheet and it
worked fine. I'll keep trying to correct that small issue, but I want
to thank you for your help! You have been great and I have learned from
it!

Thanks!

James

[DOR]

What font size is set in B2 and what is your zoom ratio for the sheet?

[DOR]

James

The small font is not an issue that has bothered me before, but some
research indicates that it cannot be changed. However, it appears to
me that the actual size is relative to the sheet zoom factor. So if
you have a less than 100% zoom factor, your apparent drop-down font
size will be proportionately smaller. Your other (cell) font sizes may
appear to be OK because you may have compensated for the low zoom
factor by increasing the cell font sizes. This would explain why it
seems to be ok on another sheet, which probably has a zoom factor of
100%.

If this is the case, increase your zoom factor and reduce your cell
font sizes. Then it should be ok.

When all else fails, you might want to try the approach described here

http://makeashorterlink.com/?X27B12274

As for the thanks - you're very welcome. We all learn from these
forums.

[Dasin]

Albertus Extra Bold 16 for B2 and 50% (I don't know who picked that
font!). Zooming in helps, but then it requires a lot of scrolling.

I tried it on a blank new sheet and it works fine, but this sheet is
very large and it was set up with smaller fonts to try and make it fit
our monitors a little better.

BTW I tried the TRIM idea and still am getting the #N/A error. I looked
at athe cell carefully and there are no spaces in the front or back.
The only other thing is that it is formatted for center.

Thanks again,

James

[Dasin]

I'll give it a shot!

Thanks,

James

[DOR]

James

Formatting should not cause the #N/A error. There must be something
other than "oct" either in B2 or in the K8. The fact that it works
with the data validation only proves that if you put whatever is in K8
in B2 the formula will find it, but if the contents of K8 are "Oct "
("Oct" with space following), putting "oct" in B2 without a following
space will not work.

As for the small drop-down font, you have just what I thought you had -
a small zoom and large cell font, which will make your drop-down font
very small. I assume you have corrected it by now by reducing the font
size in all your cells, changing the row heights and column widths to
match the new font size and increasing the zoom factor.

I see little reason for zoom factors of less than 100% unless you want
to see your overall sheet layout, after which you would revert to 100%.
It also helps to show more on smaller screens of course, but you then
negate the effect by using such large font.

Regards

Declan O'R