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

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

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

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

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

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

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