Last column with data, without VBA.

[Alexis]

Hello excel chums,

Had a look around and couldn't see the answer to this, forgive me if it's out there already.

Last week I asked a question about finding the first column with data, and I very promptly had that answered, so thanks to those who did!

My query today is the other part to the question; how do I find the last column with data?

I must admit, I have already figured it out. Thing is, I think the way I've done it is a bit clunky and I'm looking for a more elegant way of doing it.

I've got a count of cells in a row, a column number cell, and an if statement to check if the first cell is a 0 to determine where to start from. Seems like there are too many steps.

The sample file attached has my way of doing it in column J, called last val and highlighted in blue.

Conditions and context: There would never be gaps in data, and either side of the data are 0s rather than blanks (data coming from a getpivotdata formula). First and last val and count columns are to reference a compound annual growth rate function using indirect.

Any help would be gladly appreciated.

Thanks in advance,
Alexis

 

[Xiq]

Hi Alexis,

Do you want the cell reference or the cell value? I'll just post both. Enter in J9 and copy down (no need to array enter):

=INDEX($D9:$H9,SUMPRODUCT(MAX(($D9:$H9>0)*COLUMN($D9:$H9)))-3)

=CELL("address",INDEX($D9:$H9,SUMPRODUCT(MAX(($D9:$H9>0)*COLUMN($D9:$H9)))-3))

Edit:
The "3" in the formulas can reference to $C$8 if you want.

 

[Luke M]

Try this array formula:
=ADDRESS(ROW(),MAX(COLUMN(D9:H9)*(D9:H9>0)),4)

 

[shrivallabha]

Also [uses other two that you have found out]:
=ADDRESS(ROW(),COLUMN(INDIRECT(I9))+K9-1)

 

[Alexis]

Hi all,

Thanks for your responses.

Xiq - sorry, I didn't specify, I was looking for the cell reference, so thank you for giving solutions for both that and the cell value.

Luke M - does your solution need to be entered as an array formula for it to give the same effect as using SUMPRODUCT before the MAX function?

Shrivallabha - thanks for your solution too.

Regards,
Alexis

 

[Luke M]

@Alexis
Yes, it needs to be array-entered. Sorry about that.