It is a common situation in which the spreadsheet user needs to choose from a list which content is dependent on other variable(s). In case this choice is made by a drop-down, the solution will have to be flexible enough to accommodate this dynamism. Luckily this is relatively easy to achieve with Excel.
To demonstrate how this happens the Gas money splitting tool is next upgraded with the desired capabilities.
The file below is used at first. It can be developed further by following the steps that follow. More complete version will be available at the end of the post.
The drop-downs on sheet Calculator need be configured to use appropriate ranges.
If the short list of unique values is not entirely populated (as in the case) then the vacant spaces are best not popping-up in the drop-down. However, as entries are introduced or withdrawn, the number of rows needed will vary.
Had the list been static, a simple reference would have been enough to do the job.
The dynamic solutions essentially construct the range needed on the fly — taking into consideration the starting point of the range and using a rule to determine how many rows are needed. Unfortunately, the formulas involved can’t be passed to the source definition. The way around this limitation is to use a named range that represents the cells with data.
New named range can be added by going to Formulas tab > Defined Names Group > Define Name > and choosing Define Name from the options appearing. However, it is the right context to remind the existence of the Name Manager — the place where all named ranges can be found; edited; and deleted. It is accessible from the Formula tab > Name Manager.Click to see what the already present names are.
The items already present are:
- From_User_Choice — This is the cell where the drop-down with route origination points is located on sheet Calculator. In the screenshot below it is empty but in the previous step of building the tool the value in it was used in the process of extracting the unique destinations.
- To_User_Choice — Just like the previous one but aimed at the destinations.
- RouteList — This is the table that was defined in the previous post. All tables can be seen in the Name Manager.
- Return_Flag — This is a value that will be used when putting the final touches to the spreadsheet. It is intended to carry flag if the journey was return or not.
The dialog asks for:
- The name of the new range — best case will be short and well describing the data. However, I have seen (and used) quite long names and it has been OK as long as the values behind are well labelled.
- Scope — This defines if the named range can be addressed from the entire workbook or only from the sheet it is situated. This deserves a deeper dive but for now, Workbook scope is what is needed.
- Comment — This is not a required field and I can’t remember ever seeing it used. An Excel superhero would do. Be one and put some useful words in it.
- Refers to — In its simplest form regular range will be shown in the field. However, formulas can be used in it as well which will allow the dynamic property of the range.
In general, there are couple of ways to craft the syntax of the formula:
- Using single =OFFSET() that will return the entire range on its own;
- Combining a starting point (eg. $B$2) with =OFFSET() or =INDEX() that results into the end of the range in the form of a cell reference.
On a very technical level =OFFSET() is a function that is more taxing on the performance of the spreadsheet. This is so because it belongs to a group of “volatile” formulas that are triggering recalculation of the cell (or the named range in the specific case) every time Excel recalculates. Despite usually trying to avoid using them, I have no objection doing it in now.
The following parameters would be used:
The =OFFSET() itself can return a single cell or a range of them depending on how many of the arguments of the function are filled-in. In this case all 5 are used so the outcome is a whole range:
- (1) — The reference point — This is where the named range will begin.
- (2) — In case the point above not being the exact start of the range needed; the next two arguments can shift it up/down (2nd arg.) or left/right (3rd arg.). This allows for greater flexibility which is not intended in this example.
The next two arguments, marked with (3) and (4) define the size of the returned range in terms of height and width.
- (3) — =COUNTIF() is the formula that makes the work dynamic. As vacant spaces in the shortlist of origination points are marked with “-” sign, it counts how many of them are not. This will define height of 5 rows that will encapsulate the unique items. If a new appear, one of the “-” will make way, the =COUNTIF() will return value of 6 and the range will be expanded.
- (4) — The width of the range is defined as 1 (column) due to addressing data in a single column. In case a higher number was set as the final argument, the adjacent columns’ records were going to be listed as well.
The entire expression that the named range refers to says: Take a range that starts at Routes!$E$2 and is 5 rows high and 1 column wide. This makes it the Routes!$E$2:$E$6 range.
Going back to the argument marked with (3) — there are different functions that can arrive at the same figure for the number of rows to be taken. =COUNTIF() is the single criteria version of =COUNTIFS(). It is also compatible with Excel 2003 (if 17 years later someone still uses it). If the empty rows in the shortlist were not marked with “-” but stayed empty, =COUNTA() was going to be enough. Since my personal preference is to be able to easily see how many records there are in one such shortlist, I tend to put the figure in a cell below or above the column. This cell in question can be referenced instead of duplicating the formula when outlining the named range (and thus making Excel calculate the same thing twice).
Having single =OFFSET() is the most elegant implementation. However, in a large volume of data, and unless the circumstances require this approach, constructing the range with the help of =INDEX() might be appropriate.
Another named range would be made with the parameters below:
In this case the range will be constructed by explicitly stating Routes!$E$2 as its beginning and then taking the result of the =INDEX() as ending cell.
The =INDEX() itself will take the entire list of unique starting points (it 1st argument) and within its borders will select the row and column that are defined by the next two arguments:
- The =COUNTIF() was already demonstrated with the previous syntax. It will result in addressing cell that is on the 5th row in the indexed range (column of unique origins).
- The last argument will tell the index to address cell in the 1st column of the range. If this parameter is not filled-in, it will be assumed to be 1 so in this case, it could have been omitted.
The result of the =INDEX() would be cell Routes!$E$6. Put together with the Routes!$E$2 in the beginning of the formula, it will make Routes!$E$2:$E$6 — the range needed.
Finally, the same effect as with the =INDEX(), can be achieved with =OFFSET(). Consider the following range:
This expression once again explicitly points to Routes!$E$2 as the beginning of the named range. The ending of it, however, is discovered by =OFFSET() with just 3 arguments used — meaning it will not return an entire range with sizing defined by the missing 4th and 5th arguments, but a single cell address instead.
- The first argument is a reference point. In this case, it is chosen to be the top-most cell of the unique list. The cell just above it (the header in the example) can do the job just as well.
- The second argument dictates the number of rows (steps) that need be moved down from the reference point to find the target. Once more, it counts the lines with data but this time 1 is subtracted from the figure. Indeed, if 5 records are available and 5 steps away from them are made, the result will be position number 6 — a vacant one. Had the header been used as a reference point, the =COUNTIF() result would have been enough even if unchanged.
- The third argument of 0 sets the target in the same column as the reference point.
The entire =OFFSET() would return the cell that is 4 rows below the reference point (Routes!$E$2), which happens to be Routes!$E$6 — the one needed.
Even though doing the job, this syntax should be least practical — despite using the volatile function, it is not exploited to its full potential as in the very first approach presented. I am including it here simply because many years ago I had to come up with this hack but was working for bank at the time — meaning web access was severely limited. Smartphones were still rather new technology and most sites were not well optimized so I had no nerve going through my phone to search how it is made. That’s why I managed to patch this and the habit of doing dynamic ranges this way is still with me. However, very often with spreadsheets the question is can you do something or not, while efficiency is hardly measured. If it works, it ain’t stupid. Still, try to make it work well.
This is the file with all 3 solutions applied for both the unique route starting and ending points.