Backend System v2.0

Converting Address Input into Area Categories

How it was done previously

Within the google form, I would manually get users to self-identify where they stay in Singapore. Do they stay in the east? the west? central?

How it is currently done

STEP 1: Extract only the Postal code from the string
=RIGHT(M2,8)

STEP 2: Remove all letters
=IFERROR(SUBSTITUTE(N2,CHAR(CODE(LEFT(N2))),""),"")

STEP 3: Remove all letters again
=IFERROR(SUBSTITUTE(O2,CHAR(CODE(LEFT(O2))),""),"")

STEP 4: Remove any spacing
=TRIM(P2)

STEP 5: Get the first 2 numbers
=LEFT(Q2,2)

STEP 6: To determine the Area Category
=IFERROR(VLOOKUP(R2,location,2,FALSE),"")

Mapping 3 Major Indexes

There are 3 major functions occurring throughout this excel sheet.

Extracting Specific Orders based on Unique ID's

The basis of all of these indexes relies on one very simple mechanic

Now that I have identified the relevant rows, I just need to display the specific information that I want. In this particular case, I want to display the product items, unique ID has placed and ordered.

I defined a range for the products and quantities in the original CSV data file. The important thing to note is that the column you want to extract the information out of should be the first column of your defined name range. In this case, I have defined the name to be productquantities

=IFERROR(INDEX(productandquantities,$X3,COLUMNS($AA$2:AA2)),"")

Extracting products ordered on the day of production as well as customers that ordered on a particular day

These 2 other concepts follow the same execution as seen above. This is just to ensure that I get different types of views for different purposes each time I extract my CSV.

Managing Production Quantities

As you can see, my parents just need to go in to select a dropdown (created via data validation)

The product and qty columns are a result of querying the results from the index. As there can be duplicates in these columns, how I approached consolidating quantities are as such:

=SUMIFS($J$2:$J$500,$I$2:$I$500,R24)

Printing out an Order Form

Here you can see the culmination of all the indexes I have used. You also notice that there are only 2 dropdowns that my mother needs to control and print out the respective order forms.