Study Planner ; Sort Multiple Conditions using Index Small

x0nar

New Member
Joined
May 10, 2016
Messages
34
I wanted to make a study planner for myself.
For that I have created three Excel Sheets namely Schedule, Tool and Status

In "schedule" Sheet I have created a table as below
Slot 1Slot 2Slot 3
DateStatusTopicLookupStatusTopicLookupStatusTopicLookup

<tbody>
</tbody>


This table will be my planning table and I will be inputting the data here every week
Date refers to the date when I'll be studying a topic
Status refers to if the topic was completed (I will be inputting if Complete or Incomplete at the end of the day here)
Topicrefers to the topic I'll be studying
Lookup refers to a unique ID provided to each topic. (I have arranged for that)
Slots : I have divided each day into 3 Slots Morning, Afternoon and Evening ie Slot 1, 2 ,3 and Date, Topic and Lookup are subcolumns for the respective slots.

In the Tool Sheet
In this sheet I would like to know when I have allotted time for each Subject in the Table as below

Lookup Code : (Enter the Lookup code of Topic Here)

DateSlotStatus
FormulaFormulaFormula

<tbody>
</tbody>


As seen above I would like to enter formula in each of the column, such that
The excel sheet after looking at the Lookup Code goes to the Schedule Sheet and analyses the list of occurrences matching with the lookup code.
It then serially posts all of the entries in the above table (Date wise)



Eg. If I input in Schedule Sheet as follows

DateSlot 1Slot 2Slot 3
StatusTopicLookupStatusTopicLookupStatusTopicLookup
01/01/16DoneIntegersTPM1DoneProbabilityTPM4IncompleteIntegrationTPM3
02/01/2016IncompleteIntegrationTPM3DoneDerivativesTPF2IncompleteEnergyTPS1
03/01/2016DoneEnergyTPS1DoneIntegrationTPM3DoneGravityTPS7
04/01/2016DoneProtonsTPS8DoneDistributionsTPM9DoneEarth
TPG3

<tbody>
</tbody>
Desired Solution in Sheet "Tool"

Now If In the Cell of Lookup Value if I enter TPM3 (ie Lookup value for Integration), then it should display results as follows

DateSlotStatus
01/01/20163Incomplete
02/01/20161Incomplete
03/01/20163Done

<tbody>
</tbody>

Or if I enter TPS1 in the Lookup cell it should display as follows

DateSlotStatus
02/01/2016
3Incomplete
03/01/20161Done

<tbody>
</tbody>

Now if there was just Slot One, it would not be a problem since I would have used the formula given below
However the problem arises due to multiple slots, and I would prefer the results being arranged serially on the basis of dates


FORMULA ENTERED IN DATE COLUMN

Code:
=IF($D$2="","",IFERROR(INDEX(Schedule!B$3:B$189,SMALL(IF($D$2=Schedule!$E$3:$E$189,ROW(Schedule!$E$3:$E$189)-2,""),ROWS($B$5:B5))),""))

Where D2: The Lookup value (to be inputted by user) of the Cell for which results will be displayed
B3 : B189 Refers to the range containing dates in the Schedule Sheet
E3 : E189 refers to the range of Lookup Values inputted in Schedule Sheet

However the above formula considers only one slot. Hence I would like to know how to adjust it to collect multiple rows from other slots as well.
Please Help.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,214,875
Messages
6,122,047
Members
449,064
Latest member
scottdog129

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top