• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

dynamic list whilst ignoring blank cells

F

fred

Member
Hi,


I was reading http://chandoo.org/forums/topic/return-last-5-entries-of-a-dynamic-list-ignoring-blank-cells and wonder what if I want to return all the numbers (or text), instead of just the last 5 numbers?
 
Hi Fred ,


Specifically with reference to the example worksheet which you have mentioned , the last 5 entries are being returned because the formulae have been copied to 5 cells ; if you copy the same formula further downwards , all the other entries , in reverse order , will be retrieved. After the first value has been retrieved , the remaining cells below that , will display #N/A.


Narayan
 
I used E1 for user to enter the desired number of figures to display (counting backwards). and the new formula is


IFERROR(INDEX($B$3:$B$101,MATCH(COUNT($B$3:$B$101)-$E$1+ROWS($D$3:D3),$C$3:$C$101,0)),"")


and it will display the x number of figures in a straight sequence. Easier on the eyes.
 
You must log in or register to reply here.
Back
Top