Liked, this is the best scroll table I see! I see other people showed the Index formula, but your method is even better since it's cleaner due to the fact it doesn't involve the Array/Range thingy. Funny is, I don't often use Excel nor Excel savvy, so I will likely remember the Index formula more since it's a straight forward. I will try to remember your Offset formula, I think it's the Reference, which I may forget what cell to select in the future. Thanks!
Here is the spilled array solution: =IFERROR(TAKE(DROP(Table1,$O$3-1),10),"") Table1 is the source data, $O$3 is the reference cell for the output of the scroll bar, 10 is the number of rows to display. The advantages of this approach is it is dynamic, you can add or remove records without displaying blank records or having to manually adjust the scroll bar max value. You can also wrap your table in a filter function if you want to limit the records or apply a sort.
I was looking for the same answer. Not using a dirty trick like mocking a table. If this cannot be achieved in Excel, it would be good to know. Otherwise, I will keep looking. ps: With all due respect, BTW.
Here we go: =TAKE(DROP(Table1,$O$3-1),10) $O$3 is the reference cell for the output of the scroll bar. 10 is the number of rows to display. Replace Table1 with your filtered table.
I realize this is at least a 2 year old video, but it still holds up. Appreciate the knowledge!
Liked, this is the best scroll table I see! I see other people showed the Index formula, but your method is even better since it's cleaner due to the fact it doesn't involve the Array/Range thingy. Funny is, I don't often use Excel nor Excel savvy, so I will likely remember the Index formula more since it's a straight forward. I will try to remember your Offset formula, I think it's the Reference, which I may forget what cell to select in the future. Thanks!
Very useful thank you
you are a legend. A solid one indeed
Crisp explanation. Thank you so much
Great that video its so good and simple, thats exactlly what im looking for
Excellent pro Tip. Thanks
My man, this is an incredible video. It really helped. Cheers.
is there any way to use it with filter formula since i'm using filter function to get specific data in my dashboard.
Hey! I am also seeking the answer to your question. Let me know if you find it. TY!!
Here is the spilled array solution:
=IFERROR(TAKE(DROP(Table1,$O$3-1),10),"")
Table1 is the source data, $O$3 is the reference cell for the output of the scroll bar, 10 is the number of rows to display.
The advantages of this approach is it is dynamic, you can add or remove records without displaying blank records or having to manually adjust the scroll bar max value. You can also wrap your table in a filter function if you want to limit the records or apply a sort.
thank you for this. helps a lot!
just the video i was looking for.. thank you... what would you do if you needed to add more rows to your table.. thx
How to make the max value dynamic based on spill data that changes?
Thanks!
Very nice!!
Is it possible to add a scroll bar to the original table without making a second one?
I was looking for the same answer. Not using a dirty trick like mocking a table.
If this cannot be achieved in Excel, it would be good to know. Otherwise, I will keep looking.
ps: With all due respect, BTW.
Can a vertical and horizontal scroll bar be created on a single table.
can this be created using a pivot table? or you need to convert the table to an excel table.
And what about if you filter data in table? How you fix that?
could could probable get something working by wrapping the filter function with take and drop
Here we go:
=TAKE(DROP(Table1,$O$3-1),10)
$O$3 is the reference cell for the output of the scroll bar. 10 is the number of rows to display. Replace Table1 with your filtered table.
IMO the spilled array function is a much nicer solution than the offset function