Dynamic length named range for drop-down list

This entry is part 3 of 7 in the series The gas mon­ey split­ting tool series

It is a com­mon sit­u­a­tion in which the spread­sheet user needs to choose from a list which con­tent is depen­dent on oth­er variable(s). In case this choice is made by a drop-down, the solu­tion will have to be flex­i­ble enough to accom­mo­date this dynamism. Luck­i­ly this is rel­a­tive­ly easy to achieve with Excel.

To demon­strate how this hap­pens the Gas mon­ey split­ting tool is next upgrad­ed with the desired capabilities.

The file below is used at first. It can be devel­oped fur­ther by fol­low­ing the steps that fol­low. More com­plete ver­sion will be avail­able at the end of the post.

The drop-downs on sheet Cal­cu­la­tor need be con­fig­ured to use appro­pri­ate ranges.

If the short list of unique val­ues is not entire­ly pop­u­lat­ed (as in the case) then the vacant spaces are best not pop­ping-up in the drop-down. How­ev­er, as entries are intro­duced or with­drawn, the num­ber of rows need­ed will vary.

Had the list been sta­t­ic, a sim­ple ref­er­ence would have been enough to do the job.

The dynam­ic solu­tions essen­tial­ly con­struct the range need­ed on the fly — tak­ing into con­sid­er­a­tion the start­ing point of the range and using a rule to deter­mine how many rows are need­ed. Unfor­tu­nate­ly, the for­mu­las involved can’t be passed to the source def­i­n­i­tion. The way around this lim­i­ta­tion is to use a named range that rep­re­sents the cells with data.

New named range can be added by going to For­mu­las tab > Defined Names Group > Define Name > and choos­ing Define Name from the options appear­ing. How­ev­er, it is the right con­text to remind the exis­tence of the Name Man­ag­er — the place where all named ranges can be found; edit­ed; and delet­ed. It is acces­si­ble from the For­mu­la 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 orig­i­na­tion points is locat­ed on sheet Cal­cu­la­tor. In the screen­shot below it is emp­ty but in the pre­vi­ous step of build­ing the tool the val­ue in it was used in the process of extract­ing the unique destinations.
  • To_User_Choice — Just like the pre­vi­ous one but aimed at the destinations.
  • RouteList — This is the table that was defined in the pre­vi­ous post. All tables can be seen in the Name Manager.
  • Return_Flag — This is a val­ue that will be used when putting the final touch­es to the spread­sheet. It is intend­ed to car­ry flag if the jour­ney was return or not.

The dia­log asks for:

  • The name of the new range — best case will be short and well describ­ing the data. How­ev­er, I have seen (and used) quite long names and it has been OK as long as the val­ues behind are well labelled.
  • Scope — This defines if the named range can be addressed from the entire work­book or only from the sheet it is sit­u­at­ed. This deserves a deep­er dive but for now, Work­book scope is what is needed.
  • Com­ment — This is not a required field and I can’t remem­ber ever see­ing it used. An Excel super­hero would do. Be one and put some use­ful words in it.
  • Refers to — In its sim­plest form reg­u­lar range will be shown in the field. How­ev­er, for­mu­las can be used in it as well which will allow the dynam­ic prop­er­ty of the range.

In gen­er­al, there are cou­ple of ways to craft the syn­tax of the formula:

  • Using sin­gle =OFFSET() that will return the entire range on its own;
  • Com­bin­ing a start­ing 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 tech­ni­cal lev­el =OFFSET() is a func­tion that is more tax­ing on the per­for­mance of the spread­sheet. This is so because it belongs to a group of “volatile” for­mu­las that are trig­ger­ing recal­cu­la­tion of the cell (or the named range in the spe­cif­ic case) every time Excel recal­cu­lates. Despite usu­al­ly try­ing to avoid using them, I have no objec­tion doing it in now.

The fol­low­ing para­me­ters would be used:

Refers to:=OFFSET(Routes!$E$2;0;0;COUNTIF(Routes!$E$2:$E$21;”<>-”);1)

The =OFFSET() itself can return a sin­gle cell or a range of them depend­ing on how many of the argu­ments of the func­tion are filled-in. In this case all 5 are used so the out­come is a whole range:

  • (1) — The ref­er­ence point — This is where the named range will begin.
  • (2) — In case the point above not being the exact start of the range need­ed; the next two argu­ments can shift it up/down (2nd arg.) or left/right (3rd arg.). This allows for greater flex­i­bil­i­ty which is not intend­ed in this example.

The next two argu­ments, marked with (3) and (4) define the size of the returned range in terms of height and width. 

  • (3) — =COUNTIF() is the for­mu­la that makes the work dynam­ic. As vacant spaces in the short­list of orig­i­na­tion points are marked with “-” sign, it counts how many of them are not. This will define height of 5 rows that will encap­su­late the unique items. If a new appear, one of the “-” will make way, the =COUNTIF() will return val­ue of 6 and the range will be expanded.
  • (4) — The width of the range is defined as 1 (col­umn) due to address­ing data in a sin­gle col­umn. In case a high­er num­ber was set as the final argu­ment, the adja­cent columns’ records were going to be list­ed as well.

The entire expres­sion that the named range refers to says: Take a range that starts at Routes!$E$2 and is 5 rows high and 1 col­umn wide. This makes it the Routes!$E$2:$E$6 range.

Going back to the argu­ment marked with (3) — there are dif­fer­ent func­tions that can arrive at the same fig­ure for the num­ber of rows to be tak­en. =COUNTIF() is the sin­gle cri­te­ria ver­sion of =COUNTIFS(). It is also com­pat­i­ble with Excel 2003 (if 17 years lat­er some­one still uses it). If the emp­ty rows in the short­list were not marked with “-” but stayed emp­ty, =COUNTA() was going to be enough. Since my per­son­al pref­er­ence is to be able to eas­i­ly see how many records there are in one such short­list, I tend to put the fig­ure in a cell below or above the col­umn. This cell in ques­tion can be ref­er­enced instead of dupli­cat­ing the for­mu­la when out­lin­ing the named range (and thus mak­ing Excel cal­cu­late the same thing twice).

Hav­ing sin­gle =OFFSET() is the most ele­gant imple­men­ta­tion. How­ev­er, in a large vol­ume of data, and unless the cir­cum­stances require this approach, con­struct­ing the range with the help of =INDEX() might be appropriate.

Anoth­er named range would be made with the para­me­ters below:

Refers to:=Routes!$E$2:INDEX(Routes!$E$2:$E$21;COUNTIF(Routes!$E$2:$E$21;”<>-”);1)

In this case the range will be con­struct­ed by explic­it­ly stat­ing Routes!$E$2 as its begin­ning and then tak­ing the result of the =INDEX() as end­ing cell.

The =INDEX() itself will take the entire list of unique start­ing points (it 1st argu­ment) and with­in its bor­ders will select the row and col­umn that are defined by the next two arguments:

  • The =COUNTIF() was already demon­strat­ed with the pre­vi­ous syn­tax. It will result in address­ing cell that is on the 5th row in the indexed range (col­umn of unique origins).
  • The last argu­ment will tell the index to address cell in the 1st col­umn of the range. If this para­me­ter 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 togeth­er with the Routes!$E$2 in the begin­ning of the for­mu­la, it will make Routes!$E$2:$E$6 — the range needed.

Final­ly, the same effect as with the =INDEX(), can be achieved with =OFFSET(). Con­sid­er the fol­low­ing range:

Refers to:=Routes!$E$2:OFFSET(Routes!$E$2;COUNTIF(Routes!$E$2:$E$21;”<>-”)-1;0)

This expres­sion once again explic­it­ly points to Routes!$E$2 as the begin­ning of the named range. The end­ing of it, how­ev­er, is dis­cov­ered by =OFFSET() with just 3 argu­ments used — mean­ing it will not return an entire range with siz­ing defined by the miss­ing 4th and 5th argu­ments, but a sin­gle cell address instead.

  • The first argu­ment is a ref­er­ence point. In this case, it is cho­sen to be the top-most cell of the unique list. The cell just above it (the head­er in the exam­ple) can do the job just as well.
  • The sec­ond argu­ment dic­tates the num­ber of rows (steps) that need be moved down from the ref­er­ence point to find the tar­get. Once more, it counts the lines with data but this time 1 is sub­tract­ed from the fig­ure. Indeed, if 5 records are avail­able and 5 steps away from them are made, the result will be posi­tion num­ber 6 — a vacant one. Had the head­er been used as a ref­er­ence point, the =COUNTIF() result would have been enough even if unchanged.
  • The third argu­ment of 0 sets the tar­get in the same col­umn as the ref­er­ence point.

The entire =OFFSET() would return the cell that is 4 rows below the ref­er­ence point (Routes!$E$2), which hap­pens to be Routes!$E$6 — the one needed.

Even though doing the job, this syn­tax should be least prac­ti­cal — despite using the volatile func­tion, it is not exploit­ed to its full poten­tial as in the very first approach pre­sent­ed. I am includ­ing it here sim­ply because many years ago I had to come up with this hack but was work­ing for bank at the time — mean­ing web access was severe­ly lim­it­ed. Smart­phones were still rather new tech­nol­o­gy and most sites were not well opti­mized so I had no nerve going through my phone to search how it is made. That’s why I man­aged to patch this and the habit of doing dynam­ic ranges this way is still with me. How­ev­er, very often with spread­sheets the ques­tion is can you do some­thing or not, while effi­cien­cy is hard­ly mea­sured. If it works, it ain’t stu­pid. Still, try to make it work well.

This is the file with all 3 solu­tions applied for both the unique route start­ing and end­ing points.

Series Nav­i­ga­tion« Extract­ing unique items from list of entriesFind­ing data in Excel with INDEX()+MATCH() »