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!
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.
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.
Got this to work for a simple lookup form for our field crew. Lookup a job number to find the items needed and scroll through them all one page formatted as a locked form. Thanks for this, it makes the process much cleaner. Thank you, thank you thank you.
Thanks!
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!
Crisp explanation. Thank you so much
I realize this is at least a 2 year old video, but it still holds up. Appreciate the knowledge!
Excellent pro Tip. Thanks
you are a legend. A solid one indeed
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!!
Also looking for this!
My man, this is an incredible video. It really helped. Cheers.
Great that video its so good and simple, thats exactlly what im looking for
just the video i was looking for.. thank you... what would you do if you needed to add more rows to your table.. thx
thank you for this. helps a lot!
Very useful thank you
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 this be created using a pivot table? or you need to convert the table to an excel table.
How to make the max value dynamic based on spill data that changes?
Can a vertical and horizontal scroll bar be created on a single table.
Very nice!!
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
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.
Got this to work for a simple lookup form for our field crew. Lookup a job number to find the items needed and scroll through them all one page formatted as a locked form. Thanks for this, it makes the process much cleaner. Thank you, thank you thank you.
but i can not overwrite the table so its useless