# If Statement Error

Mike
Guest
Posts: n/a

 28th Sep 2009
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.
--
Mike

T. Valko
Guest
Posts: n/a

 28th Sep 2009
Try this:

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

--
Biff
Microsoft Excel MVP

"Mike" <(E-Mail Removed)> wrote in message
news:A1E9D80D-E499-4A5A-91AB-(E-Mail Removed)...
>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.
> --
> Mike

Per Jessen
Guest
Posts: n/a

 28th Sep 2009
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

On 28 Sep., 23:24, Mike <M...@discussions.microsoft.com> wrote:
> 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? *Icell 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.
> --
> Mike

Mike
Guest
Posts: n/a

 30th Sep 2009
Worked great!! Thanks.
--
Mike

"T. Valko" wrote:

> Try this:
>
> =INDEX('Monthly Data'!A4:AJ4,'Workbook Data'!A23*3)
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Mike" <(E-Mail Removed)> wrote in message
> news:A1E9D80D-E499-4A5A-91AB-(E-Mail Removed)...
> >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.
> > --
> > Mike

>
>
>

T. Valko
Guest
Posts: n/a

 30th Sep 2009
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Mike" <(E-Mail Removed)> wrote in message
news:8359886D-8B13-4C96-AFCD-(E-Mail Removed)...
> Worked great!! Thanks.
> --
> Mike
>
>
> "T. Valko" wrote:
>
>> Try this:
>>
>> =INDEX('Monthly Data'!A4:AJ4,'Workbook Data'!A23*3)
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Mike" <(E-Mail Removed)> wrote in message
>> news:A1E9D80D-E499-4A5A-91AB-(E-Mail Removed)...
>> >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.
>> > --
>> > Mike

>>
>>
>>

Mike
Guest
Posts: n/a

 1st Oct 2009
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?
--
Mike

"T. Valko" wrote:

> You're welcome. Thanks for the feedback!
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Mike" <(E-Mail Removed)> wrote in message
> news:8359886D-8B13-4C96-AFCD-(E-Mail Removed)...
> > Worked great!! Thanks.
> > --
> > Mike
> >
> >
> > "T. Valko" wrote:
> >
> >> Try this:
> >>
> >> =INDEX('Monthly Data'!A4:AJ4,'Workbook Data'!A23*3)
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Mike" <(E-Mail Removed)> wrote in message
> >> news:A1E9D80D-E499-4A5A-91AB-(E-Mail Removed)...
> >> >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.
> >> > --
> >> > Mike
> >>
> >>
> >>

>
>
>

T. Valko
Guest
Posts: n/a

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

--
Biff
Microsoft Excel MVP

"Mike" <(E-Mail Removed)> wrote in message
news:835C0405-80FB-498C-9D7D-(E-Mail Removed)...
>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?
> --
> Mike
>
>
> "T. Valko" wrote:
>
>> You're welcome. Thanks for the feedback!
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Mike" <(E-Mail Removed)> wrote in message
>> news:8359886D-8B13-4C96-AFCD-(E-Mail Removed)...
>> > Worked great!! Thanks.
>> > --
>> > Mike
>> >
>> >
>> > "T. Valko" wrote:
>> >
>> >> Try this:
>> >>
>> >> =INDEX('Monthly Data'!A4:AJ4,'Workbook Data'!A23*3)
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Mike" <(E-Mail Removed)> wrote in message
>> >> news:A1E9D80D-E499-4A5A-91AB-(E-Mail Removed)...
>> >> >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.
>> >> > --
>> >> > Mike
>> >>
>> >>
>> >>

>>
>>
>>

Mike
Guest
Posts: n/a

 2nd Oct 2009
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.
--
Mike

"T. Valko" wrote:

> Try Per Jessen's formula. It will account for inserted columns before the
> range.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Mike" <(E-Mail Removed)> wrote in message
> news:835C0405-80FB-498C-9D7D-(E-Mail Removed)...
> >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?
> > --
> > Mike
> >
> >
> > "T. Valko" wrote:
> >
> >> You're welcome. Thanks for the feedback!
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Mike" <(E-Mail Removed)> wrote in message
> >> news:8359886D-8B13-4C96-AFCD-(E-Mail Removed)...
> >> > Worked great!! Thanks.
> >> > --
> >> > Mike
> >> >
> >> >
> >> > "T. Valko" wrote:
> >> >
> >> >> Try this:
> >> >>
> >> >> =INDEX('Monthly Data'!A4:AJ4,'Workbook Data'!A23*3)
> >> >>
> >> >> --
> >> >> Biff
> >> >> Microsoft Excel MVP
> >> >>
> >> >>
> >> >> "Mike" <(E-Mail Removed)> wrote in message
> >> >> news:A1E9D80D-E499-4A5A-91AB-(E-Mail Removed)...
> >> >> >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.
> >> >> > --
> >> >> > Mike
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>
>

T. Valko
Guest
Posts: n/a

 2nd Oct 2009
>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 ____

--
Biff
Microsoft Excel MVP

"Mike" <(E-Mail Removed)> wrote in message
news:113D48FD-0AB9-4842-A677-(E-Mail Removed)...
>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.
> --
> Mike
>
>
> "T. Valko" wrote:
>
>> Try Per Jessen's formula. It will account for inserted columns before the
>> range.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Mike" <(E-Mail Removed)> wrote in message
>> news:835C0405-80FB-498C-9D7D-(E-Mail Removed)...
>> >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?
>> > --
>> > Mike
>> >
>> >
>> > "T. Valko" wrote:
>> >
>> >> You're welcome. Thanks for the feedback!
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Mike" <(E-Mail Removed)> wrote in message
>> >> news:8359886D-8B13-4C96-AFCD-(E-Mail Removed)...
>> >> > Worked great!! Thanks.
>> >> > --
>> >> > Mike
>> >> >
>> >> >
>> >> > "T. Valko" wrote:
>> >> >
>> >> >> Try this:
>> >> >>
>> >> >> =INDEX('Monthly Data'!A4:AJ4,'Workbook Data'!A23*3)
>> >> >>
>> >> >> --
>> >> >> Biff
>> >> >> Microsoft Excel MVP
>> >> >>
>> >> >>
>> >> >> "Mike" <(E-Mail Removed)> wrote in message
>> >> >> news:A1E9D80D-E499-4A5A-91AB-(E-Mail Removed)...
>> >> >> >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.
>> >> >> > --
>> >> >> > Mike
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>

>>
>>
>>

Mike
Guest
Posts: n/a

 2nd Oct 2009
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!
--
Mike

"T. Valko" wrote:

> >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 ____
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Mike" <(E-Mail Removed)> wrote in message
> news:113D48FD-0AB9-4842-A677-(E-Mail Removed)...
> >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.
> > --
> > Mike
> >
> >
> > "T. Valko" wrote:
> >
> >> Try Per Jessen's formula. It will account for inserted columns before the
> >> range.
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Mike" <(E-Mail Removed)> wrote in message
> >> news:835C0405-80FB-498C-9D7D-(E-Mail Removed)...
> >> >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?
> >> > --
> >> > Mike
> >> >
> >> >
> >> > "T. Valko" wrote:
> >> >
> >> >> You're welcome. Thanks for the feedback!
> >> >>
> >> >> --
> >> >> Biff
> >> >> Microsoft Excel MVP
> >> >>
> >> >>
> >> >> "Mike" <(E-Mail Removed)> wrote in message
> >> >> news:8359886D-8B13-4C96-AFCD-(E-Mail Removed)...
> >> >> > Worked great!! Thanks.
> >> >> > --
> >> >> > Mike
> >> >> >
> >> >> >
> >> >> > "T. Valko" wrote:
> >> >> >
> >> >> >> Try this:
> >> >> >>
> >> >> >> =INDEX('Monthly Data'!A4:AJ4,'Workbook Data'!A23*3)
> >> >> >>
> >> >> >> --
> >> >> >> Biff
> >> >> >> Microsoft Excel MVP
> >> >> >>
> >> >> >>
> >> >> >> "Mike" <(E-Mail Removed)> wrote in message
> >> >> >> news:A1E9D80D-E499-4A5A-91AB-(E-Mail Removed)...
> >> >> >> >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.
> >> >> >> > --
> >> >> >> > Mike
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post =?Utf-8?B?dG90YQ==?= Microsoft Access VBA Modules 1 12th Dec 2004 12:13 PM Mcasteel Microsoft Excel Programming 1 8th Nov 2004 10:23 PM Mcasteel Microsoft Excel Programming 0 8th Nov 2004 09:58 PM Edward S Microsoft Access 1 19th May 2004 02:41 AM Edward S Microsoft Access 1 17th May 2004 08:34 PM

Features