software

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?

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 lt;7 If's?

--
Kind regards,

Niek Otten

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

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

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

www.cpearson.com/excel/nested.htm
Niek Otten wrote:
gt; What's your data layout/what is on which sheets? What are you trying to sum?
gt; What did you try so far? Did it work for lt;7 If's?
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;Rachel Squot; lt;Rachel gt; wrote in message
gt; ...
gt; gt; I'm stumped...I need a formula for a Year to date worksheet that
gt; gt; references
gt; gt; each MTD worksheet and grabs the most recent value. The only way i know
gt; gt; how
gt; gt; to do this is through IF's but the limit won't allow for it. Any ideas?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!h8gt;0,Dec MTD!h8,IF(Nov
MTD!h8gt;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?

quot;Gary''s Studentquot; wrote:

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

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(quot;'quot;amp;H1:H12amp;quot;'! H8quot;),quot;gt;0quot;)*ROW(INDIRECT(quot;1
:12quot;)))))

entered with ctrl shift amp; 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(quot;'quot;amp;H1:H12amp;quot;'!H8quot;)),MAX((COUNTIF (INDIRECT(quot;'quot;amp;H1:H12amp;quot;'!H8
quot;),quot;gt;0quot;)*ROW(INDIRECT(quot;1:12quot;)))))

hardcoded

=INDEX({quot;Jan MTDquot;;quot;Feb MTDquot;;quot;Mar MTDquot;;quot;Apr MTDquot;;quot;May MTDquot;;quot;Jun MTDquot;;quot;Jul
MTDquot;;quot;Aug MTDquot;;quot;Sep MTDquot;;quot;Oct MTDquot;;quot;Nov MTDquot;;quot;Dec
MTDquot;},MAX((COUNTIF(INDIRECT(quot;'quot;amp;{quot;Jan MTDquot;;quot;Feb MTDquot;;quot;Mar MTDquot;;quot;Apr
MTDquot;;quot;May MTDquot;;quot;Jun MTDquot;;quot;Jul MTDquot;;quot;Aug MTDquot;;quot;Sep MTDquot;;quot;Oct MTDquot;;quot;Nov
MTDquot;;quot;Dec MTDquot;}amp;quot;'!H8quot;),quot;gt;0quot;)*ROW(INDIRECT(quot;1:12quot;)))))and

=INDEX(N(INDIRECT(quot;'quot;amp;{quot;Jan MTDquot;;quot;Feb MTDquot;;quot;Mar MTDquot;;quot;Apr MTDquot;;quot;May
MTDquot;;quot;Jun MTDquot;;quot;Jul MTDquot;;quot;Aug MTDquot;;quot;Sep MTDquot;;quot;Oct MTDquot;;quot;Nov MTDquot;;quot;Dec
MTDquot;}amp;quot;'!H8quot;)),MAX((COUNTIF(INDIRECT(quot;'quot;amp;{quot;Jan MTDquot;;quot;Feb MTDquot;;quot;Mar MTDquot;;quot;Apr
MTDquot;;quot;May MTDquot;;quot;Jun MTDquot;;quot;Jul MTDquot;;quot;Aug MTDquot;;quot;Sep MTDquot;;quot;Oct MTDquot;;quot;Nov
MTDquot;;quot;Dec MTDquot;}amp;quot;'!H8quot;),quot;gt;0quot;)*ROW(INDIRECT(quot;1:12quot;)))))--

Regards,

Peo Sjoblom

quot;Rachel Squot; gt; wrote in message
...
gt; Not sure if i worded my question clearly. I need the year to date formula
to
gt; search a specific cell in each of the 12 Month to Date worksheets to find
the
gt; most current figure. I was going to use If(Dec MTD!h8gt;0,Dec MTD!h8,IF(Nov
gt; MTD!h8gt;0,Nov MTD!h8,...... etc. I guess it all boils down to....how do i
get
gt; the current month's information to show up in my year to date worksheet?
Is
gt; vlookup still the solution?
gt;
gt; quot;Gary''s Studentquot; wrote:
gt;
gt; gt; Try VLOOKUP
gt; gt; --
gt; gt; Gary's Student
gt; gt;
gt; gt;
gt; gt; quot;Rachel Squot; wrote:
gt; gt;
gt; gt; gt; I'm stumped...I need a formula for a Year to date worksheet that
references
gt; gt; gt; each MTD worksheet and grabs the most recent value. The only way i
know how
gt; gt; gt; to do this is through IF's but the limit won't allow for it. Any
ideas?
Sorry Rachel,
What you can do is put 6 if statements into 1 cell and the other 6 if
statements into another cell... assential this gives you a cel with YTD for
the 1st 6 months and another cell with YTD for the 2nd six months. Sum the
two cells and you should have your YTD.

Hope that helps

quot;Rachel Squot; wrote:

gt; Thanks for the suggestion Stephen, that sounds like something i could
gt; grasp...if you could just break it down for me a little. Bear with me I'm a
gt; novice when it comes to excel. What is an else quot;quot;?
gt;
gt; quot;Stephenquot; wrote:
gt;
gt; gt; I ran into a similar problem... Its not the best answer but it works... Split
gt; gt; the formula into 2 seperate cells (months 1 to 6, and 7-12), put an else quot;quot;
gt; gt; in each one, then use a 3rd cell to look into both cells and return the value
gt; gt; that isn't quot;quot;.
gt; gt;
gt; gt; quot;Rachel Squot; wrote:
gt; gt;
gt; gt; gt; Not sure if i worded my question clearly. I need the year to date formula to
gt; gt; gt; search a specific cell in each of the 12 Month to Date worksheets to find the
gt; gt; gt; most current figure. I was going to use If(Dec MTD!h8gt;0,Dec MTD!h8,IF(Nov
gt; gt; gt; MTD!h8gt;0,Nov MTD!h8,...... etc. I guess it all boils down to....how do i get
gt; gt; gt; the current month's information to show up in my year to date worksheet? Is
gt; gt; gt; vlookup still the solution?
gt; gt; gt;
gt; gt; gt; quot;Gary''s Studentquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Try VLOOKUP
gt; gt; gt; gt; --
gt; gt; gt; gt; Gary's Student
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Rachel Squot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I'm stumped...I need a formula for a Year to date worksheet that references
gt; gt; gt; gt; gt; each MTD worksheet and grabs the most recent value. The only way i know how
gt; gt; gt; gt; gt; to do this is through IF's but the limit won't allow for it. Any ideas?

Thanks for the suggestion Stephen, that sounds like something i could
grasp...if you could just break it down for me a little. Bear with me I'm a
novice when it comes to excel. What is an else quot;quot;?

quot;Stephenquot; wrote:

gt; I ran into a similar problem... Its not the best answer but it works... Split
gt; the formula into 2 seperate cells (months 1 to 6, and 7-12), put an else quot;quot;
gt; in each one, then use a 3rd cell to look into both cells and return the value
gt; that isn't quot;quot;.
gt;
gt; quot;Rachel Squot; wrote:
gt;
gt; gt; Not sure if i worded my question clearly. I need the year to date formula to
gt; gt; search a specific cell in each of the 12 Month to Date worksheets to find the
gt; gt; most current figure. I was going to use If(Dec MTD!h8gt;0,Dec MTD!h8,IF(Nov
gt; gt; MTD!h8gt;0,Nov MTD!h8,...... etc. I guess it all boils down to....how do i get
gt; gt; the current month's information to show up in my year to date worksheet? Is
gt; gt; vlookup still the solution?
gt; gt;
gt; gt; quot;Gary''s Studentquot; wrote:
gt; gt;
gt; gt; gt; Try VLOOKUP
gt; gt; gt; --
gt; gt; gt; Gary's Student
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Rachel Squot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I'm stumped...I need a formula for a Year to date worksheet that references
gt; gt; gt; gt; each MTD worksheet and grabs the most recent value. The only way i know how
gt; gt; gt; gt; to do this is through IF's but the limit won't allow for it. Any ideas?

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 quot;quot;
in each one, then use a 3rd cell to look into both cells and return the value
that isn't quot;quot;.

quot;Rachel Squot; wrote:

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