Return value of last non blank cell in 3D range

0
votes

I'm working on a workbook that tracks monthly numbers, and also provides a yearly and quarterly summary for those months. The workbook has a Fiscal Year sheet, four quarterly sheets (named Q1-Q4), and 12 sheets for the months (Jul, Aug, Sep, Oct, etc). All of sheets in this workbook look identical.

Completing most of the Fiscal Year sheet is fairly simple, just use the formula =SUM(Jul:Jun!B3), which looks at all 12 monthly sheets and sums cell "B3". No problems there.

However, we have a backlog section that shows how far behind we are. For this number on the Fiscal Year sheet, I just want the number from the latest month of data. Since it's not a cumulative sum of all the months before, it's causing me issues.

I need a formula that will find the last non-blank cell in those 12 monthly sheets (they are in fiscal year order), and return just that cells value. The range for that formula would be Jul:Jun!B17. So if March is the last months worth of data, and the backlog number for march is 56, I want 56 to show on my Fiscal Year sheet.

I tried using 

=LOOKUP(2,1/('Jul:Jun'!B17<>""),B17)

But it becomes this 

=LOOKUP(2,1/('Jul:[June]June'!B17<>""),B17)

And returns a #REF! error. Any ideas?

Thanks!

2
excelexcel-formulaexcel-2016
What is Jul:Jun!B17 supposed to mean? What is your worksheet name and what is the full range that you are trying to find the last non-blank cell? – JNevill
Jul:Jun!B17 is the full range that I'm trying to find the last non-blank cell. It's July through June (fiscal year). I need a formula that will look at cell B17 over every month (July is the first month, June is the last month) and return the value of the last non-blank cell? The sheet I'm trying to put the formula in is named "Fiscal Year", and the formula would go in cell B17 on that sheet. – user4907546

2 Answers

0
votes

If I interpret your question correctly, then the following Array formula will give you what you seek: =INDEX(A1:A16,MAX(IF(ISNUMBER(A1:A16),ROW(A1:A16),""))).This can be put in A17 for July and copied across the row to L17 for June.

0
votes

Ahhh,I see.The following formula is a bit cumbersome, but solves what you are trying to accomplish.I would imagine that using VBA would be more direct and simpler, but in your example of effort you used formulas so I take it that you prefer a formulaic answer :

=CHOOSE(MAX(IF(Sheet1!B17>0,1,0),IF(Sheet2!B17>0,2,0),IF(Sheet3!B17>0,3,0),IF(Sheet4!B17>0,4,0),IF(Sheet5!B17>0,5,0),IF(Sheet6!B17>0,6,0),IF(Sheet7!B17>0,7,0),IF(Sheet8!B17>0,8,0),IF(Sheet9!B17>0,9,0),IF(Sheet10!B17>0,10,0),IF(Sheet11!B17>0,11,0),IF(Sheet12!B17>0,12,0)),Sheet1!B17,Sheet2!B17,Sheet3!B17,Sheet4!B17,Sheet5!B17,Sheet6!B17,Sheet7!B17,Sheet8!B17,Sheet9!B17,Sheet10!B17,Sheet11!B17,Sheet12!B17)