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.
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.
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.
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.
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.
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.
