If Statement Error

[Mike]

I am trying to pull data from a monthly data sheet to a summary sheet using a
combo box. I have each month listed on a workbook data sheet for the combo
box on the summary sheet. What I am trying to do on the summary sheet is
pull the data for the month selected in the combo box. My function works
fine until I reach month 9. Is there a length limit to a funtion? I cell D6
in my Summary sheet I am tryng to enter the lengthy formula and if/when it
works, copy it down about 60 rows x 2 colums. The formula is:

=IF('Workbook Data'!$A$23=1,'Monthly Data'!C4,IF('Workbook
Data'!$A$23=2,'Monthly Data'!F4,IF('Workbook Data'!$A$23=3,'Monthly
Data'!I4,IF('Workbook Data'!$A$23=4,'Monthly Data'!L4,IF('Workbook
Data'!$A$23=5,'Monthly Data'!O4,IF('Workbook Data'!$A$23=6,'Monthly
Data'!R4,IF('Workbook Data'!$A$23=7,'Monthly Data'!U4,IF('Workbook
Data'!$A$23=8,'Monthly Data'!X4))))))))

As soon as I try to continue for A23=9, my error ocurrs. Any ideas? I
realize that this probably isn't the best way to do this, but I am fairly
novice and completely green in VBA.

 

[T. Valko]

Try this:

=INDEX('Monthly Data'!A4:AJ4,'Workbook Data'!A23*3)

 

[Per Jessen]

Hi Mike

The problem is that you have reached the limit of allowed nested
statements.

This formula should do what you need:

=IF('Workbook Data'!$A$23<>"",OFFSET('Monthly Data'!C4,0,('Workbook
Data'!A23-1)*3),"")

Regards,
Per

 

[Mike]

Worked great!! Thanks.
--
Mike

Try this:

=INDEX('Monthly Data'!A4:AJ4,'Workbook Data'!A23*3)

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Mike]

I have added a couple of colums here and there and now the info getting
pulled is from the wrong cell. I have tried to change the "*3" at the end to
other values, but cannot come up with something that works. I am very
unfamiliar with this function. What was being pulled from the Monthly Data
sheet in C4, F4, I4...etc is now in D5, H5, L5...etc. Any ideas?

 

[T. Valko]

Try Per Jessen's formula. It will account for inserted columns before the
range.

 

[Mike]

I tried Per Jessen's and it is giving me #REF!. My Monthly Data sheet has
been changed to CZV - CU (whiched I changed in the formula). My data I am
pulling is in D7:F7, H7:J7...ending with T7:V7. Thank you for your support.
I really appreciate it.

 

[T. Valko]

My data I am pulling is in D7:F7, H7:J7...ending with T7:V7.

Are those merged cells?

How about refreshing my memory as to the eaxct cells you want to pull from.

If cell X =
1 pull from ____
2 pull from ____
3 pull from ____
5 pull from ____

 

[Mike]

No, not merged cells. I have a Summary sheet, 5 location sheets, and a
Workbook Data sheet. My locations are CZV - CU, CZV - FL, CZV - GA, CZV -
SE, and CZV - VA. Each location sheet has a 3 column x 116 row section for
each month to enter data (Jan = D7:F122, Feb = H7:J122....). I have a combo
box in the Summary sheet for choosing the month. The month names are in the
Workbook Data sheet in A10:A21 and the month number in A23 (when chosen via
the combo box). When I pick the month I want to view, I need that month's
data from each location to pull into the Summary sheet. CZV - CU location to
D7:F122, CZV - FL location to H7:J122...etc. Let me know if you need more
info. Thanks!

 

[T. Valko]

Jan = D7:F122, Feb = H7:J122....

Do you have a column header that identifies the data for that particular
month? For example, for the Jan data, maybe D6 says Jan (or possibly a date
like 1/1/2009).

 

[Mike]

I think I found my answer by accident just fooling with it. =INDEX('CZV -
CU'!D7:AX7,'Workbook Data'!$A$23*4-3 seems to pull the correct data for each
location. Thanks very much for all your support.

 

[T. Valko]

Ok, good deal. Thanks for the feedback!