Only 7 IFs?

[Rachel S]

I'm stumped...I need a formula for a Year to date worksheet that references
each MTD worksheet and grabs the most recent value. The only way i know how
to do this is through IF's but the limit won't allow for it. Any ideas?

[Niek Otten]

What's your data layout/what is on which sheets? What are you trying to sum?
What did you try so far? Did it work for <7 If's?

--
Kind regards,

Niek Otten

"Rachel S" <Rachel S@discussions.microsoft.com> wrote in message
news:1353EA31-E12C-4740-AF4C-4ABBC65F53EE@microsoft.com...
> I'm stumped...I need a formula for a Year to date worksheet that
> references
> each MTD worksheet and grabs the most recent value. The only way i know
> how
> to do this is through IF's but the limit won't allow for it. Any ideas?

[goofy11@gmail.com]

You can get around the 7 nested If statements limitation a few
different ways. This web page gives direction on how to do it.

http://www.cpearson.com/excel/nested.htm



Niek Otten wrote:
> What's your data layout/what is on which sheets? What are you trying to sum?
> What did you try so far? Did it work for <7 If's?
>
> --
> Kind regards,
>
> Niek Otten
>
> "Rachel S" <Rachel S@discussions.microsoft.com> wrote in message
> news:1353EA31-E12C-4740-AF4C-4ABBC65F53EE@microsoft.com...
> > I'm stumped...I need a formula for a Year to date worksheet that
> > references
> > each MTD worksheet and grabs the most recent value. The only way i know
> > how
> > to do this is through IF's but the limit won't allow for it. Any ideas?

[Gary''s Student]

Try VLOOKUP
--
Gary's Student


"Rachel S" wrote:

> I'm stumped...I need a formula for a Year to date worksheet that references
> each MTD worksheet and grabs the most recent value. The only way i know how
> to do this is through IF's but the limit won't allow for it. Any ideas?

[Rachel S]

Not sure if i worded my question clearly. I need the year to date formula to
search a specific cell in each of the 12 Month to Date worksheets to find the
most current figure. I was going to use If(Dec MTD!h8>0,Dec MTD!h8,IF(Nov
MTD!h8>0,Nov MTD!h8,...... etc. I guess it all boils down to....how do i get
the current month's information to show up in my year to date worksheet? Is
vlookup still the solution?

"Gary''s Student" wrote:

> Try VLOOKUP
> --
> Gary's Student
>
>
> "Rachel S" wrote:
>
> > I'm stumped...I need a formula for a Year to date worksheet that references
> > each MTD worksheet and grabs the most recent value. The only way i know how
> > to do this is through IF's but the limit won't allow for it. Any ideas?

[Peo Sjoblom]

If you have 12 sheets that you want to test (name Jan MTD, Feb MTD and so
on) meaning if Dec MTD H8 is empty or 0 then return Nov MTD?


Put a list of all sheet names in let's say H1:H12 (meaning all names from
Jan MTD to Dec MTD), then use

=INDEX(H1:H12,MAX((COUNTIF(INDIRECT("'"&H1:H12&"'!H8"),">0")*ROW(INDIRECT("1
:12")))))

entered with ctrl + shift & enter

will return Oct MTD if H8 there is greater than zero and if Nov and Dec are
blank or zero. If you want the most current amount instead of the month use

=INDEX(N(INDIRECT("'"&H1:H12&"'!H8")),MAX((COUNTIF(INDIRECT("'"&H1:H12&"'!H8
"),">0")*ROW(INDIRECT("1:12")))))

hardcoded

=INDEX({"Jan MTD";"Feb MTD";"Mar MTD";"Apr MTD";"May MTD";"Jun MTD";"Jul
MTD";"Aug MTD";"Sep MTD";"Oct MTD";"Nov MTD";"Dec
MTD"},MAX((COUNTIF(INDIRECT("'"&{"Jan MTD";"Feb MTD";"Mar MTD";"Apr
MTD";"May MTD";"Jun MTD";"Jul MTD";"Aug MTD";"Sep MTD";"Oct MTD";"Nov
MTD";"Dec MTD"}&"'!H8"),">0")*ROW(INDIRECT("1:12")))))


and

=INDEX(N(INDIRECT("'"&{"Jan MTD";"Feb MTD";"Mar MTD";"Apr MTD";"May
MTD";"Jun MTD";"Jul MTD";"Aug MTD";"Sep MTD";"Oct MTD";"Nov MTD";"Dec
MTD"}&"'!H8")),MAX((COUNTIF(INDIRECT("'"&{"Jan MTD";"Feb MTD";"Mar MTD";"Apr
MTD";"May MTD";"Jun MTD";"Jul MTD";"Aug MTD";"Sep MTD";"Oct MTD";"Nov
MTD";"Dec MTD"}&"'!H8"),">0")*ROW(INDIRECT("1:12")))))


--

Regards,

Peo Sjoblom




"Rachel S" <RachelS@discussions.microsoft.com> wrote in message
news:27DF4991-1C36-4407-8BDE-B513E373C832@microsoft.com...
> Not sure if i worded my question clearly. I need the year to date formula
to
> search a specific cell in each of the 12 Month to Date worksheets to find
the
> most current figure. I was going to use If(Dec MTD!h8>0,Dec MTD!h8,IF(Nov
> MTD!h8>0,Nov MTD!h8,...... etc. I guess it all boils down to....how do i
get
> the current month's information to show up in my year to date worksheet?
Is
> vlookup still the solution?
>
> "Gary''s Student" wrote:
>
> > Try VLOOKUP
> > --
> > Gary's Student
> >
> >
> > "Rachel S" wrote:
> >
> > > I'm stumped...I need a formula for a Year to date worksheet that
references
> > > each MTD worksheet and grabs the most recent value. The only way i
know how
> > > to do this is through IF's but the limit won't allow for it. Any
ideas?

[Stephen]

I ran into a similar problem... Its not the best answer but it works... Split
the formula into 2 seperate cells (months 1 to 6, and 7-12), put an else ""
in each one, then use a 3rd cell to look into both cells and return the value
that isn't "".

"Rachel S" wrote:

> Not sure if i worded my question clearly. I need the year to date formula to
> search a specific cell in each of the 12 Month to Date worksheets to find the
> most current figure. I was going to use If(Dec MTD!h8>0,Dec MTD!h8,IF(Nov
> MTD!h8>0,Nov MTD!h8,...... etc. I guess it all boils down to....how do i get
> the current month's information to show up in my year to date worksheet? Is
> vlookup still the solution?
>
> "Gary''s Student" wrote:
>
> > Try VLOOKUP
> > --
> > Gary's Student
> >
> >
> > "Rachel S" wrote:
> >
> > > I'm stumped...I need a formula for a Year to date worksheet that references
> > > each MTD worksheet and grabs the most recent value. The only way i know how
> > > to do this is through IF's but the limit won't allow for it. Any ideas?