Using Scroll Bar to scroll through Columns on a single Worksheet
Hello,
Please look at the attached Excel workbook.
As you can see, a scroll bar is used on a single sheet to move a series of date columns left and right.
I can't figure out how this was built, and haven't been able to find anything online to show how the creator connected the scroll bar with the columns. I don't see any macros.
Anyone have a clue?
Regards,
Naas
Scroll Bar.xls
The scroll is linked to cell B1 and on row 9 there are formulas tied to B9 that reflect the change of dates when you scroll. If you look in Cell C9 you will see there is formula =(A6-WEEKDAY(A6,1)+B9)+7*B1
It was built under the developer tab >> insert >> under form controls the small up down arrows between Aa and ab on that dropdown box.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
see jpg...
Thanks for your reply, Sambo.
I should have been more clear - I understand how it's built; I don't understand how to connect it to the columns. If you look at the file I attached, you'll notice that when you toggle the scroll bar left and right that the columns move along with it.
Well, when you right click on the scorll bar and go into format control, under the control tab it appears the cell link is B1. If you trace dependents for B1 it is linked to C9 and it appears most of the information in row 10 is linked to row 9 and it also appears that this formula (in C9) begins the process for the dates.
=(A6-WEEKDAY(A6,1)+B9)+7*B1
What is weird is I don't see anything in A6. However, if you put a date in there the scorll bar dates change.
A6 is supposed to be a date in dd-mm-yy format.
I need to build this except for a period of 36 months. In that context, A6 would be, say, November 2013, and I would want the columns to be listed in months.
It would seem to me you will have to change the formula in cell C9 and the subsequent ones in row 9 that work off that cell so you can get the dates in row 10 by the month.
Offhand though I don't know how you might do that as I often struggle when dealing with date functions.
Thanks for your help anyway!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks