Using MAX with concatenation or with INDIRECT

mhessnm

New Member
Joined
Apr 12, 2019
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone,

My supervisor has given me a thankless task that is turning out to be very complicated. I have 13 worksheets labeled October 21...October 22 and a Summary Sheet. This worksheet is to track data of formerly homeless clients who sign up as part of a housing program. One of the program's goals is that the clients increase their income by at least 40% in the first six months after being housed. Assuming most people start in October when the program starts, I have a start date. I also have a base income from the past year. So, I need to determine if the highest income the person has within the first 6 months is at least 40% higher than their base income.

The problem is that the sheets are for each month, so somehow I have to, if October 21 is the start date, find the max between the October 21, November 21...April 21 sheets. I'm trying to do this by formula, and I'm almost there. I use a vlookup nested in a vlookup that searches a table to get the correct sheet name. The table has columns of month number, year, index and sheet name. The first vlookup gets the start date, extracts the value of the month, and then uses the nested vlookup to get a start sheet name. The second vlookup adds 6 to the index value and retrieves the end sheet name. So, if a person starts in October 21, the start sheet is October 21 and the end sheet is April 22. I can thus get the start month sheet name and the end month sheet name for each client.

1630094479800.png


I then want to get the MAX between those two sheets, but I haven't been able to figure out how to make it work. If I do the simple MAX formula using the $G7 cell where the first client's basic income is located on all sheets, =MAX('October 21:April 22'!$G7), I get the correct result. But, when I try to concatenate, the $G7 evaluates to the income number so the evaluation shows a value error, i.e. MAX('October 21:April 22'!15000)=#VALUE!. I have been able to concatenate my lookups so that in each cell I get that sheet range, i.e. 'October 21:April 22'!. How do I get MAX to work in this situation, either concatenating the sheet names separately, or with the combined sheet range name. I have tried using INDIRECT, but it is either not appropriate or I am not correctly using it. Thank you in advance for your help.

1630095278132.png


I'm sorry this is scattered, I cannot apparently download the helpful mini-sheet tool Mr. Excel has for posting these examples on my work computer without calling an IT administrator.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,661
Rather than a lookup, use

=TEXT(A2,"mmmm yy")

to get the sheet name, where A2 contains a date in the first month.

Then since you only have 6 sheets to look at, you can get the max of G7 on each sheet like this:

Book4
AB
210/1/20218
January 22
Cell Formulas
RangeFormula
B2B2=MAX( INDIRECT("'"&TEXT(EDATE(A2,0),"mmmm yy")&"'!G7"), INDIRECT("'"&TEXT(EDATE(A2,1),"mmmm yy")&"'!G7"), INDIRECT("'"&TEXT(EDATE(A2,2),"mmmm yy")&"'!G7"), INDIRECT("'"&TEXT(EDATE(A2,3),"mmmm yy")&"'!G7"), INDIRECT("'"&TEXT(EDATE(A2,4),"mmmm yy")&"'!G7"), INDIRECT("'"&TEXT(EDATE(A2,5),"mmmm yy")&"'!G7") )


I'll see if there's a way to shorten that though.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,661
This seems to work:

Excel Formula:
=AGGREGATE(14,6,SUMIF(INDIRECT("'"&TEXT(EDATE(A2,{0,1,2,3,4,5}),"mmmm yy")&"'!G7"),">0"),1)
 

mhessnm

New Member
Joined
Apr 12, 2019
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
Hi Eric, that definitely worked, and it was a bit easier than I was making it, even if the formula is longer! Thank you, it will also solve the problem for a couple of other columns also! I'll be interested if it can be done shorter.
 

mhessnm

New Member
Joined
Apr 12, 2019
Messages
36
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Wow Eric, that is a lot shorter. I'm going to have to explore the aggregate function to see how it works, but it does. Thank you so much. You've saved me a lot of time and a lot of typing. I really appreciate it!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,661
I got it a little shorter:

Excel Formula:
=MAX(SUMIF(INDIRECT(TEXT(EDATE(A2,{0,1,2,3,4,5}),"'mmmm yy")&"'!G7"),">0"))

Glad I could help! :cool:
 
Solution

mhessnm

New Member
Joined
Apr 12, 2019
Messages
36
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Eric, can I ask one more question. In using your formula, I am trying to get a sum in the first 6 months of those sheets in the same way. I have modified the formula to read =AGGREGATE(9,6,SUMIF(INDIRECT("'"&TEXT(EDATE($E7,{0,1,2,3,4,5,6}),"mmmm yy")&"'!"&CELL("address",$P7)),">0")). However it gets a value error. It seems to evaluate down to AGGREGATE(9,6,{0,2,2,1,2,1,0}). I would expect it to return 8, but instead it returns a #VALUE! What have I done wrong here?
 

mhessnm

New Member
Joined
Apr 12, 2019
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
Eric, I didn't see your new formula, let me mess with that. Thank you!
 

mhessnm

New Member
Joined
Apr 12, 2019
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
Figured it out, I just need to surround your formula with SUM, and it gets what I want! Thanks, you've opened a new world for me!
 

Forum statistics

Threads
1,147,635
Messages
5,742,253
Members
423,717
Latest member
rubthenut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top