Hello,

New challange :).

I tried to figure it out with Index/Match formula, but........ :(

Better (?) to solve with a macro. So if there's a solution.... Thx!


In column H (=8) I need to report the last used column with data, within the same row, within the range of column I (=9) till AP (=42) minus the ForeLast used column with data.

For example;
Column AA (=27) has data "xx"
Column AL (=38) has data "xxx"
Column AO (=41) has data "xxxx"

Then reporting in the specific row in column H - 41-38= 3

because LastUsedColumn = 41 and ForeLastUsedColumn = 38.
Then reporting 41-38=3

regards, Johan

Hi Johan,

Am Wed, 11 Sep 2019 11:17:15 -0700 (PDT) schrieb JS SL:

In column H (=8) I need to report the last used column with data, within the same row, within the range of column I (=9) till AP (=42) minus the ForeLast used column with data.

For example;
Column AA (=27) has data "xx"
Column AL (=38) has data "xxx"
Column AO (=41) has data "xxxx"

Then reporting in the specific row in column H - 41-38= 3

try:
=MAX((I1:AP1<"")*COLUMN(I:AP))-LARGE(IF(I1:AP1<"",COLUMN(I:AP)),2)
and insert that formula with CTRL+Shift+Enter


Regards
Claus B.
--
Windows10
Office 2016

Claus,

SuperThanks. Works as wished (as usual)
Also thx for youre quick respons on it.
:)

regards, Johan