Tag: Formulas

Replied to Creating a Catalogue in Google Sheets by Aaron DavisAaron Davis (readwriterespond.com)

To create the SWITCH, I created a UNIQUE list of all the folders and then pasted them as values so that I could delete those not required to be displayed in the directory. After culling the list, I then added the area in the cell next to each folder. Once I had these two columns complete, I used this formula to add quotation marks to them all:

=ArrayFormula(“”””&Sheet1!A:D&””””)

I then copied this list into the SWITCH formula and added in the commas

I have found a cleaner method for adding in the commas and quotations required for the SWITCH formula:

=char(34)&join(""",""",ARRAYFORMULA(QUERY(A1:A,"SELECT A WHERE A IS NOT NULL")&""","""&QUERY(B1:B,"SELECT B WHERE B IS NOT NULL")))&char(34)
Watched
David Krevitt walks through creating a custom formula for conditional formatting. He is correct in saying that there are not many posts or reflections written about this. The formula that he uses (which can be found here) highlights a value found in a separate list in a separate tab:

=match($F12, indirect("'rangeTab'!$B$12:$B") ,0)

I have used the following formula to highlight a whole row based on the contents of one cell:

=$O1="Yes"

Krevitt now has me wondering what else I could do.