Rank order

[grogmi1]

I have a list in a spreadsheet which shows all of our vehicle numbers in sheet 4 column c, when a vehicle is given a job the details are entered into a seperate sheet (sheet1). When a job is entered into sheet 1 the vehicle number is taken out of the list in sheet4 column C leaving a blank cell.

In some cases a vehicle can be picked from right down the list which leaves gaps in sheet 4 column C. I already have a macro that hides the rows of blank cells in column C so I can always see which vehicles are in the queue.

What I need, is to display in sheet 1 the position of the vehicles in the queue and for it to update regardsless of where they have been taken from in the queue. e.g. vehicle 32 may be showing 1st in the queue but may not be able to do a particular job so I have to pick vehicle 176 which is 5th in the queue but able to do the job. I then need the queue to update 5th position with the next vehicle as the vehicle that was in 5th will be removed from the queue.

Clear as mud !!

Any help would be much appreciated. I'm not great with Excel but I will try anything !!

Thanks

 

[Jerry Sullivan]

Chip Pearson has a nice explanation on how to remove blanks from a list.
http://www.cpearson.com/excel/NoBlanks.aspx

Applying that to your application, you can...
1. Create a range named "VehicleNrRange" on Sheet4.
2. Then enter the array formula shown below on Sheet1 under your Queue header
3. Then copy that formula down as many rows as you need.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ccccff; font-weight:bold; text-align:center; ">Queue</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; ">33</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; ">11</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; ">16</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; ">18</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; ">25</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:center; ">19</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B4</td><td >{=IFERROR(INDEX<span style=' color:008000; '>(VehicleNrRange,SMALL<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>(IF<span style=' color:#804000; '>(LEN<span style=' color:#ff7837; '>(VehicleNrRange)</span>,ROW<span style=' color:#ff7837; '>(INDIRECT<span style=' color:#8000ff; '>("1:"&ROWS<span style=' color:#545fa5; '>(VehicleNrRange)</span>)</span>)</span>)</span>)</span>,ROW<span style=' color:#ff0000; '>(A1)</span>)</span>,1)</span>,"")}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4