Last column with value

[farrusete]

Hi,

Im trying to locate last column with value from non adjacent columns and set that one's column name in a cell. Please check attachment because this is a bit tricky. Column in red for line 1 should say "jan-15" and it should be empty for line 2. It should be the last month in which we have invoiced (the last column named "Actual Amount XXX-15" with value)

Also, you will see that columns named "Forecast XXX-15" calculate the total amount of that line and divides it by the total amount of days in that order to get a daily spent and then multiplies it by the amount of days in that month. How could I take into account previous months invoices to calculate forecast based on what we have already invoiced?

Thank you in advance,

 

[Hui]

Farrusete

Why isn't G3 = Sept 15 as it is the last date with a value in the Actual Amount ?

I suspect that G3: Should be
=OFFSET($A$1,,MAX((LEFT($I$2:$AR$2,8)="Actual A")*($I3:$AR3>0)*COLUMN($I$2:$AR$2))-3) Ctrl+Shift+enter
Copy down

 

[farrusete]

You are right. I copied/pasted columns and didnt remove previous values.

It works great for finding last invoiced month (i hace to check it in order to know how it works). Any clue about my second question?

Thank you again