Formula for Returning MAX of a Dynamic Row

[kstilwill]

HELP!!!!


This one is a little complicated. I’m trying to write a very dynamic template to be used across multiple Markets. The object is to have one dashboard with opportunity for multiple markets to use the same one with in the same workbook. The only thing that eludes me is one equation to pull the ‘Peak YTD Month’ for each category. The formula I’m using looks a little bit of a mess …This is the formula in Cell I9 thru I21 on the Non-ASG Sheet.


My Formula:

=IFERROR(INDIRECT("'"&$B$8&"'!"&ADDRESS(1,MATCH(MAX(INDIRECT("'"&$B$8&"'!"&ADDRESS(VLOOKUP(B9,INDIRECT("'"&$B$8&"'!1:200"),2,0),MATCH(dhLastDayJanuary($D$3),INDIRECT("'"&$B$8&"'!1:1"),0))&":"&ADDRESS(VLOOKUP(B9,INDIRECT("'"&$B$8&"'!1:200"),2,0),MATCH(dhLastDayJanuary($D$3),INDIRECT("'"&$B$8&"'!1:1"),0)+11))),INDIRECT("'"&$B$8&"'!"&ADDRESS(VLOOKUP(B9,INDIRECT("'"&$B$8&"'!1:200"),2,0),1)&":"&ADDRESS(VLOOKUP(B9,INDIRECT("'"&$B$8&"'!1:200"),2,0),COUNTA(INDIRECT("'"&$B$8&"'!1:200")),TRUE)),0),4)),"")


So Basically…


I want to return the first month that has the largest value with in the current reporting month for each catagory. I am using a VBE Function to get the last day of January of the current year so I am constant on using EOMONTH dates for calculations are running off the same dates. Can anyone help me solve my issue where it will find the correct cells and return the correct month for the year? Cell I13 is the one that shows there is still a problem with the formula. Can anyone help?


I have a Spreadsheet i can send..I have this posted on another site "theCodeCage" for help also...

http://www.thecodecage.com/forumz/microsoft-excel-forum/213777-formula-returning-max-dynamic-row.html

 

[Hui]

Katilwill


Firstly, Welcome to the Chandoo.org Forums


Can you post your file so we can understand what your doing

Refer the green sticky posts on the Forum page

 

[NARAYANK991]

Hi Kelly ,


Two points :


1. Instead of posting such a formidable formula , if you can expand on your requirement , it will be helpful ; can you give more explanations on this sentence :

[pre]

I want to return the first month that has the largest value with in the current reporting month
for each category.

[/pre]
If you can explain how your data is laid out , column-wise , row-wise , we can visualize your problem better.


2. Referring to the other site where you posted your problem , there seems to be hav been some replies ; have you got any pointers or discarded any methods ? This will help so that efforts are not duplicated.


Narayan