Creating a work order

clemkonan

Active Member
Joined
Jun 21, 2003
Messages
255
My department has task that are executed daily, weekly, monthly and quarterly and these tasks have been placed in a name range list called "task" where the headers are A1= Frequency, B1= Form # and C1 = Task Description.

4 rows or lines of data reads as follows:
A2 =Daily B2 =27 C2= Preop inspection Check
A3 = Daily B3= 33 C3 = Scale Calibration
A4 = Weekly B4 19 C4 = Brittle plastic Check
A5 = Monthly c5 = 48 C5 = Freezer Calibration

So I have names this Range "Task"

I have a drop down list from which I can choose my frequency so lets say I choose daily meaning " show me the things I am scheduled to do daily"

How do I get the Vlook up to work so that it looks at the list which also has quarterly work, seni-annual work etc and only returns the weekly tasks.
Hopefully this makes sense if needed can I upload my sheet and if so how?

My output should be that I have to :do a pre-op inspection check and a scale calibration check.

Thanks in advance
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Daily</td><td style="text-align: right;;">1</td><td style=";">Op1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Daily</td><td style=";">Op1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Daily</td><td style="text-align: right;;">2</td><td style=";">Op2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Op2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Weekly</td><td style="text-align: right;;">3</td><td style=";">Op3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Op4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Daily</td><td style="text-align: right;;">4</td><td style=";">Op4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Monthly</td><td style="text-align: right;;">5</td><td style=";">Op5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table><p style="width:1.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">22</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$C$2:C6,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">$C$2:$C$6</font>)-ROW(<font color="Teal">$C$2</font>)+1</font>)/(<font color="Purple">$A$2:$A$6=$F$2</font>),ROWS(<font color="Purple">$A$1:A1</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

clemkonan

Active Member
Joined
Jun 21, 2003
Messages
255
I Never used the Index function before so I will have to look it up. Assuming I then wanted to extend my query so in real terms the employee is starting his /her shift and wanted to see what jobs are tasked for Daily, Weekly, Monthly how do we move to lets say the monthly query.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
Simply change the value in F2 to Monthly.
 

Forum statistics

Threads
1,089,644
Messages
5,409,497
Members
403,266
Latest member
HMR120

This Week's Hot Topics

Top