Placement of [previously used dropdown box] based on data

[dwheggen1]

I have created a [previously used drop down box] located on workbook "sheetdown"

The dropdown list works well on the this page, now I want to use it on the "Daily order" workbook starting on cell D:21

I can get it to show the list, however when I select a truck and move to cell D:22 all trucks still show up ??

My ultimate goal is if the order requests "Tandems" that only a list of available tandems would be pulled in.
The quantity and type of trucks is pulled in automatically from a userform and can range from 1 to 15 trucks.

I appreciate direction on this next step

Trucking Orders 3-30-2015 703 PM.xlsm

[humdingaling]

Not sure i understand the question completely but
so D21:d23 are data validations

the DV list is based on what is in C21:C23 (you can change this later) this i have marked blue

named ranges i took from truck companies
Tandem, Semi, tubs

[dwheggen1]

Not what I am looking to do. Let me try to explain a little more.

1. I start at "Truck order entry workbook"
2. select "Add Truck order"
3. Fill out the user form
4. Select " Add daily order" - The order data is entered either order (#1 through 10)
The first order of the day would be # 1, 2nd order # 2, and so on depending on orders
received,
5. Each order is then being sent to the appropriate order line on the "Daily order sheet"
at top of page in rows 2-11
6. Each order summary is pulled into the appropriate order area below when the "process
order button is pressed". such as order #1 area (A:17 to I-19)

(There is 1 problem here... If I do no want to wait until all 10 orders are received,
and every time the select "process orders" is selected it re enters all qty's and
types of trucks again. I would like to enter each order as they are received, select
the order for that job and be ready for order # 2 and so on. Maybe each order
needs processed 1 at a times so as to not repeat.

7. Based on each order the (qty. and type of trucks) the order is then populated starting
at B:21 & C:21)

8. If the order requires say Tandems, I only wish for a Tandem list to become available
starting at cell D:21 This is the column I would like a "previously used drop down box
to become available to select from "available tandems" that have not already been
dispatched. If the individual order requires Tubs only the tub list would be available and so on.

Here lies what is important, Any 1 truck can only be entered 1 time each day, thus as soon as it is used the next time that type of truck is needed it would not show up in
the drop down list

I hope you can follow this, as I am building the program as I go, and there may be some area's that could have or should have been processed differently.

Thanks for wherever you can add assistance or corrections.

Dennis

[humdingaling]

For #6
after your press process order
how do you return the order form back to original?
it creates space for lines but how does it get deleted for next time use?


For #8

where is the list of trucks taken from?
is that the "sheetdown" sheet?

[dwheggen1]

# 6 - After you select "process orders" and data is populated in area's below, next select the "Save daily to
new tab" and the day's sheet will be saved to a new workbook and the workbook will be named the date
in (Order # 1)

# 8 - This is where I began building a dropdown list that could be used for tandems. The goal is the macro
only would allow a truck in the list to be used 1 time per day. ie If (Jones 1) is selected, it would not be
available when the tandem dropdown list is used to fill the next tandem order.


Return to "Daily order" workbook and it will be reset for the next day's orders processed on the userform.

Dennis

[humdingaling]

so you press save daily then process
so never will you process order before save daily?
if that is the case....what i had in mind wont work

#8
sorry not a truck person
how do i know which truck is which type?
i was looking at truck companies and
Mack
Mack-1
Mack-2
go under all 3 columns?
are they all the same truck or different trucks that fit under all 3 types?

[dwheggen1]

Actually I t would be nice to populate order # 1, fill the order, and when next order comes in process, ect.

Problem being with the code I have under process orders is each time you select process orders it will enter all orders again. Maybe it would work better when orders are processed it would store the orders on a sheet and continue to update the sheet as the next orders come in. Then only save the daily order to a new workbook after all orders for the day are complete ??, or somehow only let order # 1 be entered 1 time per day.

As far as trucks, there could be up to 8 different types. Tandem, Dump Trailer, Flow Trailer, Stock Pile, Wet
Batch, Semi Tractor, Tub Trailer. The names in the list are samples for now. There would need to be a selectable list dropdown for all 8 types

I can be flexible as the program continues to be built, other issues will come up I am sure

Dennis

[humdingaling]

so here is the file
what i added...

on sheetdown sheet
there is list of trucks and types in column S and T

on daily order tab
added truck list in column N (manual...should just run of list in sheetdown)
column P is unique truck list
Column Q is type of truck (vlookup against sheetdown)

^^part of this is redundant but can fix tomorrow if this actually works for you

coding
some changes to your button10_click coding

mainly

Please Login or Register  to view this content.

so insert validation using offset formula with "t" to populate

i also added some code on daily order worksheet

Please Login or Register  to view this content.

can refine the code more so it doesn't do anything spastic but for now it works ok
but essentially it just adds anything between D20:D100 at the bottom of column N

what this does is column P gets updated and duplicates get removed
so if it appears twice in column N it gets taken off column P

play around see if it works for you

[humdingaling]

so running through it
from daily order tab
click on Save Daily to new tab
click on Process orders
adds lines for orders as well as DV
select DV...gets dropped off column P so cant be selected twice

oh i added one named range....
trucklist=OFFSET('Daily Order'!$N$14,1,0,COUNTA('Daily Order'!$N$14:$N$50))
its actually not that important in the overall

its only used in the formula for column P

Please Login or Register  to view this content.

i initially tried to do it all with named ranges but it was just too hard to control

[dwheggen1]

Great changes so far, what I am looking for. "Perfect"

However,
1. when I select process it works all good.
2. If I go back and add another order, it pulls the order into the correct list area rows 2-11, however
3. It does not update the next order I just added.

I would really like to process the orders as they come in (assign trucks needed), and if another order comes in process and assign it. This way throughout the day I can be dispatching to keep up and not have to wait until I say 6:00 at night to process an order that may have been received at 9:00 am

Also, If you can On each line there is a truck order added I would like a box outlined with a checkmark that I can go back to when the company is notified so I know the company has been notified and called.

Thanks so much for what you have done. This project is coming together nicely.

Dennis

[humdingaling]

in that case....
how about you only press Save Daily to new tab once the day is complete?

there is a new column M which indicates if order has been processed or not
have changed some of the coding to help this process along

if this works for you
just need to re-code the "save daily" and "copy & clear" macros

[dwheggen1]

As per my previous post. I prefer to process orders as they are received. The current program can handle 10 jobs and 15 trucks each (150 Trucks). If I had 10 orders, I would want to start dispatching as soon as orders come in. In construction if you do not notify your trucks on the next day's dispatch they will look for work else where..

Thanks
Dennis

[humdingaling]

in the latest revision (v3)
you would add order then click process on daily order tab
this will populate daily truck orders with required number of data validations and mark Y in column M
if you press process order again...it will only process orders that are not marked Y in column M

the only thing not added at the moment is copying off and clearing daily order tab
we can also look into making your order number table larger to encompass more orders per day

[humdingaling]

latest revision
Unique truck list now runs off sheet sheetdown
Copy and clear actually copies and clears to reset the daily order sheet completely

added template sheet to copy original format of daily order table
added named range Availtruck for the DV

revised coding on Button_click10 module and daily order worksheet change so it adds lines to onto Sheetsdown column S when you use it on the DV

[dwheggen1]

OK, I entered order # 1 for 3 trucks. Selected process orders and filled the order.
Went back added order # 2 and when I clicked process orders it hung up in the line of code below marked red.


With Cells(k + 3, 4).Offset(i, 0).Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=availtruck"
End With

Did I do something wrong ?

Dennis

[dwheggen1]

OK, Found my problem. If available trucks run out (More ordered than in my list in "Sheet Down" it hangs with the error. When I went in and added more trucks it would work again.

Maybe the thing to do would be if There are no more available trucks a message could pop up saying " All trucks of this type used ! Need to call additional companies to help out"

What you have done is "exactly" what I am looking to do.

Can you add a tally of truck types processed for the day and have it show up on the "Daily Order sheet" rows 13 & 14 ?? Truck types on 13 and trucks processed under the type on 14 ? That would be real nice so when the daily order is printed at the end of the day this would be included on the report.

Dennis


Dennis

[dwheggen1]

OK, Working off the latest ver4 you updated all the trucks available are not showing up to select from ?
When orders are saved for the day and when starting to fill orders the next day all trucks should again become available ?

Regards,
Dennis

[humdingaling]

sorry for not answering,
its was Easter break

one problem at a time then

i think tally would be good, it can be incorporated into pre-check when you press process order

i have included this into on V5
also i put in code to "reset" trucks when you copy/clear or save to daily tab

[dwheggen1]

Where is the Tally area located? I entered 1 order and do not see it ??

Thanks again,
Dennis

[humdingaling]

on the right hand side of daily order sheet

its rudimentary but i think it serves its purpose of checking
expand list for more truck types of course