After a long back and forth with a kind forum member on one of my questions, I realized this is something I should have done first. I need some feedback on the best way to organize the table for my textbook order workbook.
This is the newly adapted table; I've added / rearranged the following columns in the Listing worksheet: Programme, Academic year, Major, Minor, Year, Semester. Below is the table, and here is the link to the uploaded anonymized file. Am I on the right track when it comes to the organization of the table? Would I be able to eventually develop a drop down menu that has been added above the table and change what is shown below it depending on what options are chosen? Is this achieved with data validation, or with pivot tables and slicers? Or should this be done solely with filtering?
The main question is, how to remove the hardcoded formulas and draw the textbook quantities based on the programme, major, minor and year of the students in the Data worksheet?
This is the newly adapted table; I've added / rearranged the following columns in the Listing worksheet: Programme, Academic year, Major, Minor, Year, Semester. Below is the table, and here is the link to the uploaded anonymized file. Am I on the right track when it comes to the organization of the table? Would I be able to eventually develop a drop down menu that has been added above the table and change what is shown below it depending on what options are chosen? Is this achieved with data validation, or with pivot tables and slicers? Or should this be done solely with filtering?
The main question is, how to remove the hardcoded formulas and draw the textbook quantities based on the programme, major, minor and year of the students in the Data worksheet?
| ORDER CALCULATIONS MINOR COLUMNS.xlsx | ||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | |||
| 1 | THIS IS A PLACEHOLDER | |||||||||||||||||||||||||||||
| 2 | Programme | Academic year | Major | Minor | Year | Semester | ||||||||||||||||||||||||
| 3 | FOREIGN | SUPPLIERS | LOCAL | SUPPLIERS | ||||||||||||||||||||||||||
| 4 | DIRECT | ORDER | ||||||||||||||||||||||||||||
| 5 | Programme | Academic year | Major | Minor | Year | Semester | Code | Title | Lecturer | Textbook | Author | Publisher | ISBN | Quantity | Instructor | In stock | TO ORDER | CENGAGE | PEARSON | SUPPLIER1 | SUPPLIER2 | QUOTE | Supplier Final | Price final | Availability | Tracking | COST | ARRIVED # | ||
| 6 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 1 | 2 | CS121 | Computer Organization and Architecture | Name Surname | Computer Organization and Architecture | McGraw-Hill | 5 | 1 | 0 | 6 | $ 40.00 | $ 50.00 | 1 | SUPPLIER1 | $ 40.00 | Pending | $ 240.00 | ||||||||
| 7 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 1 | 2 | CS122 | Programming and Problem Solving II | Name Surname | Programming and Problem Solving II | Pearson | 5 | 1 | 0 | 6 | $ 50.00 | 0 | PEARSON | $ 50.00 | Pending | $ 300.00 | |||||||||
| 8 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 1 | 2 | Math123 | Discrete Mathematics | Name Surname | Discrete Mathematics | Pearson | 5 | 1 | 16 | 0 | $ 50.00 | 0 | $ - | Pending | $ - | ||||||||||
| 9 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 2 | 2 | CS221 | Database Systems | Name Surname | Database Systems | CENGAGE | 5 | 1 | 0 | 6 | $ 50.00 | 0 | CENGAGE | $ 50.00 | Pending | $ 300.00 | |||||||||
| 10 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 2 | 2 | CS222 | Operating Systems | Name Surname | Operating Systems | Wiley | 5 | 1 | 0 | 6 | $ 50.00 | $ 40.00 | 1 | SUPPLIER2 | $ 40.00 | Pending | $ 240.00 | ||||||||
| 11 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 2 | 2 | CS223 | Cloud Computing | Name Surname | Cloud Computing | Pearson | 5 | 1 | 0 | 6 | $ 50.00 | 0 | PEARSON | $ 50.00 | Pending | $ 300.00 | |||||||||
| 12 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 2 | 2 | CS224 | Design and Implementation in Web Environments | Name Surname | Design and Implementation in Web Environments | Pearson | 5 | 1 | 0 | 6 | $ 50.00 | 0 | PEARSON | $ 50.00 | Pending | $ 300.00 | |||||||||
| 13 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 2 | 2 | CS225 | Design and Implementation in Web Environments | Name Surname | Design and Implementation in Web Environments | Pearson | 5 | 1 | 0 | 6 | $ 50.00 | 0 | PEARSON | $ 50.00 | Pending | $ 300.00 | |||||||||
| 14 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 3 | 2 | CS321 | Project management | Name Surname | Project management | Project Management Institute | 5 | 1 | 0 | 6 | $ 40.00 | $ 50.00 | 1 | SUPPLIER1 | $ 40.00 | Pending | $ 240.00 | ||||||||
| 15 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 3 | 2 | CS322 | Business Intelligence | Name Surname | Business Intelligence | Pearson | 5 | 1 | 17 | 0 | $ 50.00 | 0 | $ - | Pending | $ - | ||||||||||
| 16 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 3 | 2 | CS323 | Software Engineering | Name Surname | Software Engineering | Pearson | 5 | 1 | 16 | 0 | $ 50.00 | 0 | $ - | Pending | $ - | ||||||||||
| 17 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 3 | 2 | ENG324 | Reports and Professional Correspondence | No textbook | 0 | 0 | 0 | 0 | 0 | $ - | Pending | $ - | |||||||||||||
| 18 | Bachelor Degree Programme | 2023/24 | Computer Science | Electrical Engineering | 3 | 2 | EE325 | Electrical Engineering | Electrical Engineering | Wiley – IEEE Press | 0 | 0 | 0 | 0 | $ 50.00 | $ 40.00 | 0 | $ - | Pending | $ - | ||||||||||
| 19 | Bachelor Degree Programme | 2023/24 | Computer Science | Business | 3 | 2 | EC326 | Marketing fundamentals | No textbook | 0 | 0 | 0 | 0 | 0 | $ - | Pending | $ - | |||||||||||||
| 20 | Bachelor Degree Programme | 2023/24 | Computer Science | Economics | 3 | 2 | EC327 | Introduction to Macroecenomics | Name Surname | Introduction to Macroecenomics | Pearson | 3 | 1 | 0 | 4 | $ 50.00 | 0 | PEARSON | $ 50.00 | Pending | $ 200.00 | |||||||||
| 21 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 4 | 2 | CS421 | Applied Programming | Name Surname | Applied Programming | Pearson | 5 | 1 | 0 | 6 | $ 50.00 | 0 | PEARSON | $ 50.00 | Pending | $ 300.00 | |||||||||
| 22 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 4 | 2 | CS422 | Embedded Systems | Name Surname | Embedded Systems | Springer | 5 | 1 | 17 | 0 | $ 40.00 | $ 50.00 | 0 | $ - | Pending | $ - | |||||||||
| 23 | Bachelor Degree Programme | 2023/24 | Computer Science | 0 | 4 | 2 | CS423 | Dissertation Research and Writing | Name Surname | Dissertation Research and Writing | Red Globe Press / Macmillan | 5 | 1 | 16 | 0 | $ 50.00 | $ 40.00 | 0 | $ - | Pending | $ - | |||||||||
| 24 | Bachelor Degree Programme | 2023/24 | Computer Science | Electrical Engineering | 4 | 2 | EE425 | Multimedia Electronics | Name Surname | Multimedia Electronics | Pearson | 2 | 1 | 0 | 3 | $ 50.00 | 0 | PEARSON | $ 50.00 | Pending | $ 150.00 | |||||||||
| 25 | Bachelor Degree Programme | 2023/24 | Computer Science | Electrical Engineering | 4 | 2 | EE426 | Selected topics in engineering | Name Surname | Selected topics in engineering | Wiley – IEEE Press | 2 | 1 | 0 | 3 | $ 40.00 | $ 50.00 | 1 | SUPPLIER1 | $ 40.00 | Pending | $ 120.00 | ||||||||
| 26 | Bachelor Degree Programme | 2023/24 | Computer Science | Business | 4 | 2 | EC427 | Corporate Strategy and Strategic Management | Name Surname | Corporate Strategy and Strategic Management | CENGAGE | 2 | 1 | 0 | 3 | $ 50.00 | 0 | CENGAGE | $ 50.00 | Pending | $ 150.00 | |||||||||
| 27 | Bachelor Degree Programme | 2023/24 | Computer Science | Business | 4 | 2 | EC428 | Introduction to finance | Name Surname | Introduction to finance | Pearson | 2 | 1 | 0 | 3 | $ 50.00 | 0 | PEARSON | $ 50.00 | Pending | $ 150.00 | |||||||||
| 28 | Bachelor Degree Programme | 2023/24 | Computer Science | Economics | 4 | 2 | EC429 | Corporate Social Responsibility | Name Surname | Corporate Social Responsibility | Pearson | 1 | 1 | 0 | 2 | $ 50.00 | 0 | PEARSON | $ 50.00 | Pending | $ 100.00 | |||||||||
Listing | ||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| N6:N8 | N6 | =IF(J6="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"-",Table1[Year],"1"))) |
| O6:O28 | O6 | =IF(J6="No textbook",0,COUNTIF(I6,"<>")) |
| N9:N13 | N9 | =IF(J9="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"-",Table1[Year],"2"))) |
| N14:N17 | N14 | =IF(J14="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Year],"3"))) |
| N18 | N18 | =IF(J18="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Electronic and Electrical Engineering",Table1[Year],"3"))) |
| N19 | N19 | =IF(J19="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Business",Table1[Year],"3"))) |
| N20 | N20 | =IF(J20="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Economics",Table1[Year],"3"))) |
| N21:N23 | N21 | =IF(J21="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Year],"4"))) |
| N24:N25 | N24 | =IF(J24="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Electronic and Electrical Engineering",Table1[Year],"4"))) |
| N26:N27 | N26 | =IF(J26="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Business",Table1[Year],"4"))) |
| N28 | N28 | =IF(J28="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Economics",Table1[Year],"4"))) |
| V6:V28 | V6 | =IF(AND(ISBLANK(R6),ISBLANK(S6))*(Q6<>0),1,0) |
| W6:W28 | W6 | =IFERROR(FILTER($R$5:$U$5, ($R6:$U6 = X6) * ($R6:$U6 <> "")), "") |
| X6:X28 | X6 | =MIN(R6:U6)*(Q6<>0) |
| Q6:Q28 | Q6 | =IF(N6+O6-P6<0,0,N6+O6-P6) |
| AA6:AA28 | AA6 | =Q6*X6 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F7:F28 | Expression | =$J7:$J29="" | text | NO |
| F7:F28 | Expression | =$Y7="Temporarily unavailable" | text | NO |
| F7:F28 | Expression | =$Y7="Print on demand" | text | NO |
| F7:F28 | Expression | =$Y7="Out of print" | text | NO |
| F7:F28 | Expression | =$Z7="Ordered" | text | NO |
| F7:F28 | Expression | =$Z7="Arrived" | text | NO |
| F7:F28 | Expression | =$Z7="Incomplete" | text | NO |
| F7:F28 | Expression | =$Z7="Late" | text | NO |
| X29:X1048576,W3:W28 | Cell Value | contains "SUPPLIER2" | text | NO |
| X29:X1048576,W3:W28 | Cell Value | contains "SUPPLIER1" | text | NO |
| K29:K1048576,J3:J28 | Cell Value | contains "No textbook" | text | NO |
| T25:U25 | Other Type | Color scale | NO | |
| T23:U23 | Other Type | Color scale | NO | |
| T22:U22 | Other Type | Color scale | NO | |
| T18:U18 | Other Type | Color scale | NO | |
| T14:U14 | Other Type | Color scale | NO | |
| T10:U10 | Other Type | Color scale | NO | |
| T6:U6 | Other Type | Color scale | NO | |
| N6:N28,Q6:Q28,AA6:AA28 | Cell Value | =0 | text | NO |
| W6:W28,L6:L28 | Cell Value | contains "CENGAGE" | text | NO |
| L6:L28,W6:W28 | Cell Value | contains "PEARSON" | text | NO |
| V6:AB28,T7:U9,T11:U13,T15:U17,T19:U21,T26:U28,A6:S28 | Expression | =$Y6="Available" | text | NO |
| V6:AB6,A6:S6 | Expression | =$J6:$J28="" | text | NO |
| V6:AB6,A6:S6 | Expression | =$Y6="Temporarily unavailable" | text | NO |
| V6:AB6,A6:S6 | Expression | =$Y6="Print on demand" | text | NO |
| V6:AB6,A6:S6 | Expression | =$Y6="Out of print" | text | NO |
| V6:AB6,A6:S6 | Expression | =$Z6="Ordered" | text | NO |
| V6:AB6,A6:S6 | Expression | =$Z6="Arrived" | text | NO |
| V6:AB6,A6:S6 | Expression | =$Z6="Incomplete" | text | NO |
| V6:AB6,A6:S6 | Expression | =$Z6="Late" | text | NO |
| V7:AB28,T7:U9,P7:S25,T11:U13,T15:U17,T19:U21,P26:U28,A7:O28 | Expression | =$K7:$K29="" | text | NO |
| V7:AB28,T7:U9,P7:S25,T11:U13,T15:U17,T19:U21,P26:U28,A7:O28 | Expression | =$Y7="Temporarily unavailable" | text | NO |
| V7:AB28,T7:U9,P7:S25,T11:U13,T15:U17,T19:U21,P26:U28,A7:O28 | Expression | =$Y7="Print on demand" | text | NO |
| V7:AB28,T7:U9,P7:S25,T11:U13,T15:U17,T19:U21,P26:U28,A7:O28 | Expression | =$Y7="Out of print" | text | NO |
| V7:AB28,T7:U9,P7:S25,T11:U13,T15:U17,T19:U21,P26:U28,A7:O28 | Expression | =$Z7="Ordered" | text | NO |
| V7:AB28,T7:U9,P7:S25,T11:U13,T15:U17,T19:U21,P26:U28,A7:O28 | Expression | =$Z7="Arrived" | text | NO |
| V7:AB28,T7:U9,P7:S25,T11:U13,T15:U17,T19:U21,P26:U28,A7:O28 | Expression | =$Z7="Incomplete" | text | NO |
| V7:AB28,T7:U9,P7:S25,T11:U13,T15:U17,T19:U21,P26:U28,A7:O28 | Expression | =$Z7="Late" | text | NO |
| T24:U24 | Expression | =$K24:$K46="" | text | NO |
| T24:U24 | Expression | =$Y24="Available" | text | NO |
| T24:U24 | Expression | =$Y24="Temporarily unavailable" | text | NO |
| T24:U24 | Expression | =$Y24="Print on demand" | text | NO |
| T24:U24 | Expression | =$Y24="Out of print" | text | NO |
| T24:U24 | Expression | =$Z24="Ordered" | text | NO |
| T24:U24 | Expression | =$Z24="Arrived" | text | NO |
| T24:U24 | Expression | =$Z24="Incomplete" | text | NO |
| T24:U24 | Expression | =$Z24="Late" | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| Y6:Y28 | List | =Data!$R$3:$R$6 |
| Z6:Z28 | List | =Data!$R$9:$R$13 |
