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?