General Betting
Welcome to Live View – Take the tour to learn more
Start Tour
There is currently 1 person viewing this thread.
I want to find the last number in a list which could be of different lengths. I know that there are the simple Lookup and VLookup formulas and these work with data that you input. Unfortunately, my data is created by formulas and Lookup and VLookup don’t work with generated lists. Formulas tried are, =LOOKUP(9e+10,A:A) =VLOOKUP(9e+10,A:A,1)
My excel knowledge is limited and I am not into macros. Any help would be appreciated.
My excel knowledge is limited and I am not into macros. Any help would be appreciated.
Show More
Can you not copy and paste special your data to make it be in number format and not formulas?
Copy the whole worksheet and paste special it into another worksheet without it's formulas.
Yes that works but I was looking for something automatic as while I'm copying and pasting I could just look at the list and type the figure in.
If Column A has no blank cells in the list ~
=OFFSET(A1,(COUNT(A:A)-1),0)
=OFFSET(A1,(COUNT(A:A)-1),0)
compound magic is right - what you need to do is to create a dynamic range (one that changes size depending on data). Google for lots of examples.
If I understand the question I would use INDIRECT/ADDRESS with an array formula
=INDIRECT(ADDRESS(MAX(IF($A1:$A999 > 0, ROW($A1:$A999), 0)),1,,,"Sheet1"))
... enter as array formula using Ctrl + Shift + Enter instead of just Enter
where Sheet1 is the worksheet name, 999 is the max row, and > 0 constitutes a number - amend as appropriate
=INDIRECT(ADDRESS(MAX(IF($A1:$A999 > 0, ROW($A1:$A999), 0)),1,,,"Sheet1"))
... enter as array formula using Ctrl + Shift + Enter instead of just Enter
where Sheet1 is the worksheet name, 999 is the max row, and > 0 constitutes a number - amend as appropriate
Post Your Reply
Please login to post a reply.
Wonder
Instance ID: 13539