Housing Solutions

“Do you have a housing problem? Then this is where you can find a housing solution!”

The Housing Solutions spreadsheet can suggest an available housing (box, envelope, etc) for a collection item, based on the dimensions of the item. It also notes whether the selected housing is available in the current supplies inventory of the institution.

I built Housing Solutions using Google Sheets, with a combination of filter formulas to show results based on user entered data. The filter functions can also accommodate null values in certain columns, where certain user-entered values are optional. So as to prevent it from giving results that were too small or too large for the object being housed, the filter criteria add “tolerance” values to the dimensions entered by the user. For example, it will only gives results for housings that are at least 1/4″ larger than the length, width and height dimensions provided by the user; it will also not provide results for boxes that are more than 2 1/2″ larger in any dimension.

Here is the filter formula used:

IFERROR(TRANSPOSE(SORTN(TRANSPOSE(
IF(AJ2=TRUE, SPLIT(TEXTJOIN(“”,TRUE,UNIQUE(FILTER({TEXT(MEGALIST!$F:$F,“# ?/?”)&” x “,TEXT(MEGALIST!$G:$G,“# ?/?”)&” x “,TEXT(MEGALIST!$H:$H,“# ?/?”)&“|”}, D2=MEGALIST!$A:$A,(E2=MEGALIST!$B:$B)+(ISBLANK(E2)), F2<= MEGALIST!$F:$F, F2+K2+Tolerance!$B$2 >= MEGALIST!$F:$F, G2 <= MEGALIST!$G:$G, G2+K2+Tolerance!$B$2 >= MEGALIST!$G:$G, O2<= MEGALIST!$H:$H, O2+J2+Tolerance!$B$4 >= MEGALIST!$H:$H))),“|”),

SPLIT(TEXTJOIN(“”,TRUE,UNIQUE(FILTER({TEXT(MEGALIST!$F:$F,“# ?/?”)&” x “,TEXT(MEGALIST!$G:$G,“# ?/?”)&” x “,TEXT(MEGALIST!$H:$H,“# ?/?”)&“|”}, D2=MEGALIST!$A:$A,(E2=MEGALIST!$B:$B)+(ISBLANK(E2)), F2+Tolerance!$B$3 <= MEGALIST!$F:$F, F2+K2+Tolerance!$B$2 >= MEGALIST!$F:$F, G2+Tolerance!$B$3 <= MEGALIST!$G:$G, G2+K2+Tolerance!$B$2 >= MEGALIST!$G:$G, O2+Tolerance!$B$5 <= MEGALIST!$H:$H, O2+J2+Tolerance!$B$4 >= MEGALIST!$H:$H))),“|”))),4,0,1,TRUE)), “No products available in that size”)

To provide the line-by-line data validation that generates the drop-down menus in Columns N,O and P, I used a script created by Google user AD:AM to copy the row-specific data validations based on the filter results in other columns within the same row.

Mark McCann also provided a generous amount of help setting up the original filter formula concept for me, which I then refined and modified over time.

The internal version used at ASU Library is slightly different in that the list of supplies queried by the user is dynamically created through an ImportRange function that calls in the data from a centralized list of supplies, known as the “MEGALIST”. The internal ASU Library version also adjusts the results, via different tolerance values, for items larger than 21″, due to the particular storage availability for oversized materials in the ASU Library collections.

I created and shared the first version of this spreadsheet in November 2018.

The most recent version was published on February 22, 2019.