Max If Function across several Sheets

Hi All,

I am trying to apply a max if function across several sheets using named range but it doesn't work.

I would like to retrieve the latest date by product name and the formula I am using is:

Please Login or Register  to view this content.

with Weeks being a named range.

Thank you for letting me know what's wrong with this formula

Change your Named Range to:

=TRANSPOSE(Master!$G$1:$G$3)

Then use:

=IFERROR(1/(1/MAX(IF(T(OFFSET(INDIRECT("'"&Weeks&"'!A2:A30"),ROW($1:$30)-1,))=A2,N(OFFSET(INDIRECT("'"&Weeks&"'!B2:B30"),ROW($1:$30)-1,))))),"Not Found")

You need to:

1. Adjust the bits in RED to be sensible and future-proof

2. Ensure that the number of rows covered by ROW is at least equal to the number of rows covered by the INDIRECT functions.

3. Ensure that you do not go mad and use whole column references... it ill get slow.

4. Answer this Q:

There >>>may<<< be a non-volatile alternative. In your REAL data, roughly how many rows of raw data per sheet and how many sheets???

I have a mental block about MAX-MAXIFS. I NEVER think about it and always wind up with a more long winded formula.

If the limitations re string length are not an issue, and assuming the sheets to be interrogated are consecutive and well-ordered, with 365 we also have the option of circumventing the traditional volatile set-up for such 3D scenarios, viz:

=IFERROR(MAX(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,Week1:Week3!A$2:B$9)&"</b></a>","//b[preceding::*[1]="""&A2&"""]")),"Not Found")

Regards

My idea would not have worked... but with only about 600 rows in total... XOR LX's solution should ve just fine!! As long as there are < 32767 characters in the whole lot combined try his solution.

Volatile = recalculates every time anything changes. With BIG data (unlike yours) it can get laggy. OFFSET & INDIRECT are both volatile. Avoid them if you can...

Hi Xor,

thank you for your help with this formula. However, in my real file this formula fails and returns the #VALUE! error.

If I check the error through the Error checking functionality of Excel, the error shows me the following:

Attachment 775067

When I then evaluate the calculations, this is the errors I get:

Attachment 775068
Attachment 775069

I actually understand what's annoying this formula. In the real file, column B and C of the Weeks sheets contain some free text: when I this text is removed the formula works, when the text is there the #VALUE! error comes up.

2 Questions:
- How can I make sure that this formula doesn't get annoyed by the free text in column B and C? The length of this text may vary from cell to cell and is not fixed.
- In my real file, the Weeks sheets contain 2 columns with a date: column D and F. How can I make sure that the formula only gives me the date located in column F?

Thanks a lot in advance