garyj
10-25-2023, 09:37 PM
Hello Excel Wizards...
I am basically writing a database in Excel using the Filter and Sort features, which operate like db queries. Seems better than Power Query.
One of my features (it is for a dispatch company) is to create a 2 week calendar on a sheet, which uses the [Bus Out date-time] and [Bus Back date-time] to place a line across the calendar representing that the bus is on a trip. My formulas use the various information to find the start and end position of each line: so basically, the Top, Left, and Width of each line, and, if it is longer than the end of the week, the same information for the next week. To accomplish this I have taken a multicolumn filter from the "Bookings" table, using =SORT(FILTER(BusMapData,(F1<=MyTimeIn)*(F2>MyTimeOut)*(MyCancel=0)),4). BusMapData is the name of a 6 column range in Bookings. F1 stores the first day of the calendar at 12:00:00 AM. F2 stores the day after the last day of the calendar at 12:00:00 AM (one second after the calendar ends).
What I am wondering is which of the following methods would be faster in getting the data and formulating it to get the info for .Left, .Top, and .Width for each bus out on a trip:
To use VBA code to get the data, to prepare it using formulas, to create ActiveX labels, and to store those values "With MyLbl", OR
To add columns after the last column of the spilled range, and use these columns to figure out the same data, and then use VBA code to get this data, create the ActiveX labels, and to store them.
My guess is that the second option is faster.
If so, I have a second question.
Does anyone know how to reference the cell for one column of a multicolumn spill without losing the spill? In a single column spill starting at A5, typing ="A5#" will show the whole column, where "A5" will only reference the one cell. In a multicolumn spill A5:C5 (regardless of length) how would I get only column B? See snippet to explain, I hope you can follow all my arrows :)
31137
Since my rows will be short, the first question is rather moot. But I would like to know out of curiosity.
I am basically writing a database in Excel using the Filter and Sort features, which operate like db queries. Seems better than Power Query.
One of my features (it is for a dispatch company) is to create a 2 week calendar on a sheet, which uses the [Bus Out date-time] and [Bus Back date-time] to place a line across the calendar representing that the bus is on a trip. My formulas use the various information to find the start and end position of each line: so basically, the Top, Left, and Width of each line, and, if it is longer than the end of the week, the same information for the next week. To accomplish this I have taken a multicolumn filter from the "Bookings" table, using =SORT(FILTER(BusMapData,(F1<=MyTimeIn)*(F2>MyTimeOut)*(MyCancel=0)),4). BusMapData is the name of a 6 column range in Bookings. F1 stores the first day of the calendar at 12:00:00 AM. F2 stores the day after the last day of the calendar at 12:00:00 AM (one second after the calendar ends).
What I am wondering is which of the following methods would be faster in getting the data and formulating it to get the info for .Left, .Top, and .Width for each bus out on a trip:
To use VBA code to get the data, to prepare it using formulas, to create ActiveX labels, and to store those values "With MyLbl", OR
To add columns after the last column of the spilled range, and use these columns to figure out the same data, and then use VBA code to get this data, create the ActiveX labels, and to store them.
My guess is that the second option is faster.
If so, I have a second question.
Does anyone know how to reference the cell for one column of a multicolumn spill without losing the spill? In a single column spill starting at A5, typing ="A5#" will show the whole column, where "A5" will only reference the one cell. In a multicolumn spill A5:C5 (regardless of length) how would I get only column B? See snippet to explain, I hope you can follow all my arrows :)
31137
Since my rows will be short, the first question is rather moot. But I would like to know out of curiosity.