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

--
Kind regards,

Niek Otten

"Rachel S" <Rachel wrote in message
...
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?

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?

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 wrote in message
...
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?

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!h80,Dec MTD!h8,IF(Nov
MTD!h80,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?

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" wrote in message
...
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!h80,Dec MTD!h8,IF(Nov
MTD!h80,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?

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

"Rachel S" wrote:

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 ""?

"Stephen" wrote:

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!h80,Dec MTD!h8,IF(Nov
MTD!h80,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?

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 ""?

"Stephen" wrote:

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!h80,Dec MTD!h8,IF(Nov
MTD!h80,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?

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!h80,Dec MTD!h8,IF(Nov
MTD!h80,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?