Max IF formula across multiple sheets

[mwifladt]

Hi All,

I currently have the following formula

" =MAX(C3:C167, '02'!C3:D167, '03'!C3:D167, '04'!C3:D167, '05'!C3:D167, '06'!C3:D167, '07'!C3:D167, '08'!C3:D167, '09'!C3:D167, '10'!C3:D167, '11'!C3:D167, '12'!C3:D167, '13'!C3:D167, '14'!C3:D167, '15'!C3:D167, '16'!C3:D167, '17'!C3:D167, '18'!C3:D167, '19'!C3:D167, '20'!C3:D167, '21'!C3:D167, '22'!C3:D167, '23'!C3:D167, '24'!C3:D167, '25'!C3:D167, '26'!C3:D167, '27'!C3:D167, '28'!C3:D167, '29'!C3:D167, '30'!C3:D167, '31'!C3:D167)"

This formula is used to show the most recent order that we put into our order log. It is a monthly log and each worksheet is a single day. As of yesterday we started adding a different series of number to the same column that our current forumla is searching in and they are much higher than the other order numbers. Example is our normal order numbers are 414562 and the new ones are 860182. Is there a way for me to modify the max formula so i continue to have the max number across all sheets in one cell (which would be the new higher series of numbers) and in a different cell have a modified max formula cell that is less than a specific number, like say 500,000 so that it does not pick up any of the new order series numbers?

Sorry for the long post and thank you in advance for your help!

[GeneralDisarray]

Don't have an answer for that, but have a tip :D

Your formula can be replaced by this: =MAX('02:31'!C3:D167)

You can group the sheets in the formula to avoid having to type it out 30 times.

[mwifladt]

Oh that would have been nice when i originally created the formula lol. Thank you for the tip!

[mwifladt]

Unfortunately the above mentioned tip doesn't work :/. Im entering it in exactly as shown and i keep getting a formula error.

[mwifladt]

Actually it does work. For some reason the email message response shows * marks in the formula.