Dynamic Sheet Queries

[TheSliink]

Hi,

Im new to all this so go easy and ill try my best to keep it simple but dont think there is an overly concise way to summarise this. Im trying to produce a workbook which serves as a one stop shop for our orders recieved and dispatching side of the business whilst trying not to change too much aesthetically on the surface so the transition is smooth and embraced as currently there is duplication for this in 4 separate places as well as a POS portal, which as you can imagine is leading to a large amount of human error and poor productivity.

I wish to have two control documents (probably can be one if im honest) which is a list of our customers as a master list, then a list of the product lines which pack size and qty etc per customer depedant on which ones are applicable to them.

I wish to use the latter template as the daily order sheet which means i duplicate it for each day (sheets to be named by date ie. 010624). I then want to be able to pull that sheet through to a 'Load Sheet' where it removes all the lines where there is a qty of 0 so we can see how many orders we have got, how many pallets it equates to etc. Id like to be able to do this through a drop down list (which is auto populated by the sheet names as and when they are added)


Any help on the set up would be greatly appreciated

Data is dummy for obvious reasons with blanks which i can fill at a later date.

This is the Master Customer List.

Book2.xlsx
	A	B	C	D	E
1	Customer	Site	Product	Del. Site	Haulier
2	Customer A	F	K	P	U
3	Customer B	G	L	Q	V
4	Customer C	H	M	R	W
5	Customer D	I	N	S	X
6	Customer E	J	O	T	Y
Customer List MASTER

This is the Master Customer Lines List

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Customer	Site	Product	Del. Site	Haulier	Count	Pack Weight	Box Type	Boxes/Pallet	Pallets
2	Customer A	F	K	P	U	1	2
3	Customer A	F	K	P	U	3	4
4	Customer A	F	K	P	U	5	6
5	Customer A	F	K	P	U	7	8
6	Customer A	F	K	P	U	9	10
7	Customer B	G	L	Q	V	11	12
8	Customer B	G	L	Q	V	13	14
9	Customer B	G	L	Q	V	15	16
10	Customer B	G	L	Q	V	17	18
11	Customer B	G	L	Q	V	19	20
12	Customer B	G	L	Q	V	21	22
13	Customer C	H	L	Q	V	23	24
14	Customer C	H	L	Q	V	25	26
15	Customer C	H	M	R	W	27	28
16	Customer D	I	N	S	X	29	30
17	Customer E	J	O	T	Y	31	32
18	Customer E	J	O	T	Y	33	34
19	Customer E	J	O	T	Y	35	36
20	Customer E	J	O	T	Y	37	38
21
Customer Lines MASTER

This is the Daily Order Sheet with 010624 as the name

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Customer	Site	Product	Del. Site	Haulier	Count	Pack Weight	Quantity	Box Type	Boxes/Pallet	Pallets
2	Customer A	F	K	P	U	1	2	1
3	Customer A	F	K	P	U	3	4	2
4	Customer A	F	K	P	U	5	6	3
5	Customer A	F	K	P	U	7	8	4
6	Customer A	F	K	P	U	9	10
7	Customer B	G	L	Q	V	11	12	5
8	Customer B	G	L	Q	V	13	14	6
9	Customer B	G	L	Q	V	15	16	7
10	Customer B	G	L	Q	V	17	18
11	Customer B	G	L	Q	V	19	20	8
12	Customer B	G	L	Q	V	21	22	9
13	Customer C	H	L	Q	V	23	24
14	Customer C	H	L	Q	V	25	26	10
15	Customer C	H	M	R	W	27	28
16	Customer D	I	N	S	X	29	30	11
17	Customer E	J	O	T	Y	31	32	12
18	Customer E	J	O	T	Y	33	34
19	Customer E	J	O	T	Y	35	36	13
20	Customer E	J	O	T	Y	37	38
010624

As mentioned, i then want to choose a named worksheet from a drop down list on the Load Plan sheet which will filter out lines with 0 qty and then allow me to assign load numbers in a Load Number column to then slice view on the despatch page to assign pallet numbers etc. This is where im pretty fixed in method otherwise too much change, old dogs new tricks etc etc .


Thanks in advance

 

[Vogateer]

I hope this might give you something to start with.

You will need to either adjust the sheet names in the VBA or adjust the sheet names in your workbook with the following VBA, but here is how I would generate a list of those daily order sheets, removing the non-daily-order sheets from consideration. I started most of the sheet names with "Master" just so I could remove those with one if statement, but I also have a "Load Plan" sheet included

Sub CreateSheetList()
    ' Create a named range on a "Master Sheet List" for dropdown menu
    Dim DatedSheet As Worksheet, EntryRow As Long, LastRow As Long
    Dim SheetListName As Name
    
    With ThisWorkbook.Sheets("Master Sheet List")
        .Range("A:A").Delete
        EntryRow = 1
        For Each DatedSheet In ThisWorkbook.Sheets
            ' Don't add "Master" sheets or "Load Plan" to list
            If Left(DatedSheet.Name, 6) <> "Master" And _
                DatedSheet.Name <> "Load Plan" Then
                ' Include a ' mark in front so the leading 0s are not removed
                .Cells(EntryRow, 1).Value2 = "'" & DatedSheet.Name
                EntryRow = EntryRow + 1
            End If
        Next DatedSheet
        
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        
        If RangeExists("DatedSheetList") Then
            Set SheetListName = ThisWorkbook.Names.Item("DatedSheetList")
            SheetListName.RefersTo = .Range(.Cells(1, 1), .Cells(LastRow, 1))
        Else
            Set SheetListName = ThisWorkbook.Names.Add("DatedSheetList", _
                .Range(.Cells(1, 1), .Cells(LastRow, 1)))
        End If
        
    End With
End Sub

This will create a daily order sheet list on the Master Sheet List, and create a named range ("DatedSheetList") that you can use for a Data Validation Drop-down.

Then on the Load Sheet, I have A1 as "Select Sheet:", and B2 as the drop-down list of daily order sheets.

The following formula is pretty nasty in my eyes, but it seems to be getting the job done. I'm assuming you'll need to adjust the range, and I don't know how you plan to use it or save it, but this should show a table of those that actually have a quantity listed:

=TEXTSPLIT(TEXTJOIN("|",TRUE,LET(array,INDIRECT("'" & $B$1 & "'!A1:K20"),join,BYROW(array,LAMBDA(a,IF(CHOOSECOLS(a,8)<>"",TEXTJOIN(";",FALSE,a), ""))),FILTER(join,join<>""))),";","|",FALSE)

That gives me this:

The Indirect formula has to refer to the daily order sheet cell that was selected, and the !A1:K20 would have to be adjusted for whatever range you need to pull from the sheet. The thing I'm not sure about is how you might want to enter more data based on that info. You might need more VBA to copy the data over to another sheet for further processing or something like that. I hope this helps get you started.