Column letter of the last column that has a value

Hi all,
I am looking for a function to use in an Excle spreadsheet that returns the column letter (with anchors) of the last column that has value. Pls see attached. Thanks alot.
Column-letter.xlsx
UtredningAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Will the last value also be the highest value as in the example?

If not would the last date but one in row 3 also be the correct place? I am assuming the dates will be in order if the values in row 5 aren't.
0
Rob HensonFinance AnalystCommented:
If you can use the values in row 5 as the indicator:
=LEFT(CELL("address",OFFSET($A$5,0,MATCH(MAX($5:$5),$5:$5,0)-1,1,1)),LEN(CELL("address",OFFSET($A$5,0,MATCH(MAX($5:$5),$5:$5,0)-1,1,1)))-1)

If you use the dates in row 3:
=LEFT(CELL("address",OFFSET($A$5,0,MATCH(MAX($3:$3),$3:$3,0)-2,1,1)),LEN(CELL("address",OFFSET($A$5,0,MATCH(MAX($3:$3),$3:$3,0)-2,1,1)))-1)

This assumes the row number will be less than 10, ie remove only one character from address created by the ADDRESS function. This is fine when using the dates in row 3 but I guess the data although currently in row 5 could move.

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
Bigger picture question:

What are you doing with the result?

Thanks
Rob H
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

UtredningAuthor Commented:
Rob Henson,
Thanks for looking into this question. I see that you are getting close to what I am looking for. Let me elaborate. In the enclose spreadsheet; the date-values are fixed. They are not relevant. What I dont know is when the cell values under each date-value are populated. There is a lag in the updating. Hence I am looking for a function that returns the column letter of the colunm that contains the last cell value. This cell value can be larger or smaller than the previous ones. That is not important. What is important is to identify the corresponding column letter that holds a cell with latest cell value. (Btw my appologies for this poorly formulated question). Thanks again.

(In the bigger picture; I need the column letter (with anchors) for input into a graph).
0
Rob HensonFinance AnalystCommented:
For input into a graph, you need a range rather than just a column. How are you intending to create the range from the column. You might be able to use the OFFSET function to create a range, all entries into this function are numeric so may not need to extract the column letter.

=OFFSET(Reference,RowsOffset,ColumnsOffset,Height,Width)

Reference - the starting point of the data
Rows/Columns Offset - the number of rows/columns away from the starting point where the range needs to start
Height - the number of rows in the range
Width - the number of columns in a range

So for your previous example, with data in row 5:

=OFFSET(B5,0,0,1,COUNTA(5:5))

This would create a range starting at B5, with no offsets, 1 row in height and as many columns wide as there are columns with data in row 5.

You can use the OFFSET function to create a Dynamic Named Range in the Name Manager and then refer to that Named Range in the graph data field.
0
UtredningAuthor Commented:
Rob Henson - thank you for your kind advice.
Could you please just address the question as I put it;  I am looking for a function that returns the column letter of the colunm that contains the last cell value. This cell value can be larger or smaller than the previous ones. The date-values are fixed. They are not relevant. What is important is to identify the corresponding column letter (with anchors) that holds a cell with latest cell value.
Thanks again.
0
Rob HensonFinance AnalystCommented:
OK, a couple of options:

=LEFT(ADDRESS(ROW(A5),COUNTA(5:5)+1,1,1),LEN(ADDRESS(ROW(A5),COUNTA(5:5)+1,1,1))-LEN(ROW(A5)))

or

=LEFT(CELL("address",OFFSET(A5,0,COUNTA(5:5),1,1)),LEN(CELL("address",OFFSET(A5,0,COUNTA(5:5),1,1)))-LEN(ROW(A5)))

Both refer to row 5 but will change if copied from original placing and will adjust row number accordingly. This now also gets over the removal of the single number from the cell reference.

They both rely on there being only one blank column to the left of the data. If this will not always be the case then a tweak will be needed.

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
See attached with both of the above and a further option. This last option does use the dates row, I know you said that they are irrelevant but this looks for the highest date in row 3 for which there is an entry in row 5, ie the latest entry, and returns the column for that date.

For ease of use, either of the first two are better. The third option uses an array entered (confirmed with Shift+Ctrl+Enter) formula which are less easy to understand.

Thanks
Rob H
Column-letter.xlsx
0
UtredningAuthor Commented:
Thanks alot - this works as intended; =LEFT(ADDRESS(ROW(A5),COUNTA(5:5)+1,1,1),LEN(ADDRESS(ROW(A5),COUNTA(5:5)+1,1,1))-LEN(ROW(A5)))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.