I can't figure out how to get the fields to populate from the Invoice file..

[phxsportz99]

I've got this file that wants to get working file "TSC G - 001 Leadership Office.xlsx" to populate the fields of "INVOICE DATE", "INVOICE", "FROM", "TO", "BALANCE". The following are the Screen shots of the worksheets on the file.. I'm sorry, I've had a stroke so.. It's a little bit crappy but I hope you can figure it out.....

"MASTER"

MASTER.xlsm
	A	B	C	D	E	F
1
2				NEW
3
4		Working File:	TSC G - 001 Leadership Office.xlsx
5		Invoice Date:	The Sons Children
6		INVOICE:	TSC "G" - 001
7		From:	Valley Install's, L.L.C.
8		To:	The Sons Children
9
10		Balance:	$3,180.00
11
12
13
14
15
16
17
18
19
20
21
22			Edit	Cancel
23
24
25
MASTER

Cell Formulas
Range	Formula
C6:C7	C6	=Invoice!I4
C8	C8	=Invoice!B5
C10	C10	=Invoice!J41

Cells with Data Validation
Cell	Allow	Criteria
C4:D4	List	='Working Folders'!$B$22:$B$70

"INVOICE"

TSC G - 001 Leadership Office.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	INVOICE
2
3							Invoice Date:		5/16/2023
4							Invoice Number:		TSC "G" - 001
5	To:	The Sons Children					From:		Valley Install's, L.L.C.
6		2030 N 36th Street							Richard Hamilton
7		Phoenix, AZ 85008							6441 W. Whitton Ave
8									Phoenix, AZ 85033
9
10	Trade:	Demo / Install
11
12	Comments Or Special Instructions:
13	Demo Floor and do the install of flooring								Terms:
14									Due Upon
15									Receipt
16
17	Customer Name			Jobsite Address >		2030 N 36th Street
18	Austin					Phoenix, AZ 85008
19						Phone #	602-956-7370		Gate Code
20	Description							# of Items	Price	Amount Due
21	Demo Office							1	$ 750.00	$750.00
22	Flooring 552.25 Sq Ft + 10 Percent							607.475	$ 4.00	$2,429.90
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37	Misc.							Sub Total:		$3,179.90
38								Miscellaneous:
39								Total Due:		$3,179.90
40	Attachments:							Deposit:
41	Make All Checks Payable to:							Balance:		$3,179.90
42	Valley Install's, LLC							Date Paid:
Sheet1

Cell Formulas
Range	Formula
H22	H22	=(23.5*23.5*1.1)
J21:J35	J21	=IF(LEN(A21) <> 0,(H21*I21),"")
J37	J37	=SUM(J21:K35)
J39	J39	=SUM(J37+J38)
J41	J41	=J39-J40

"WORKING FOLDERS"

MASTER.xlsm
	A	B
59	D:\Master\Building B\TSC Other	Invoice -TSC Laundry.xlsx
60	D:\Master\Building B\TSC Other	Invoice -TSC U.L. #2 Paint.xlsx
61	D:\Master\Building B\TSC Other	Invoice -TSC-2'sRmRePaint.xlsx
62	D:\Master\Building B\UL Double Door	Sons Children UC DD Demo.xlsx
63	D:\Master\Building B\UL Double Door	Sons Children UC DD Paint.xlsx
64	D:\Master\Building B\UL Double Door	Sons Children UC DD Wainscot.xlsx
65	D:\Master\Building B\UL Next to Kitchen	Sons Children UC NK DEMO.xlsx
66	D:\Master\Building B\UL Next to Kitchen	Sons Children UC NK PAINT.xlsx
67	D:\Master\Building B\UL Next to Kitchen	Sons Children UC NK Wainscot.xlsx
68	D:\Master\Building B\UL Next to Kitchen	TSC B - 017 UC NK.xlsx
69	D:\Master\Building G\Leadership Office	TSC G - 001 Leadership Office.xlsx
70	D:\Master\Building G\Staff Breakroom	TSC G - 002 Staff Breakroom.xlsx
Working Folders

The "MASTER" file, "Working File" Is a Drop Down List..

Thanks... John

 

[Herakles]

Anything is possible but I would contain all of the information needed to generate an invoice in one or more
worksheets and generate the invoices from this information.

You could then access the information that you need from these sheets.

You are where you are though so I think that some code in the Worksheet Change event would work.

I'll see what I can do.

 

[phxsportz99]

Thank you..

 

[Herakles]

One problem I have encountered is that one of the file names you have given has a ' character in it's name.

Invoice -TSC-2'sRmRePaint.xlsx

This is used by the INDEX function to look up the value from the file without it being open.

Is it possible to replace this character or remove it?

 

[Herakles]

I've come up with this.

Paste this code into the worksheet code module for the worksheet containing the Working File drop down validation list.

The worksheet containing the folder and file list needs to be called 'Working Folders' or change the reference to it on this line of code.
Set rngFound = Worksheets("Working Folders").Range("B:B").Find(strWorkingFile, LookIn:=xlValues)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strFormula As String
Dim strWorkingFile As String
Dim rngFound As Range
Dim strFolder As String
Dim strMsg As String

    ' On Error GoTo Err_Handler
    
    If Target.Count > 1 Then
        Exit Sub
    End If
                
    If Target = Range("C4") Then
    
        Range("C5:C10").Value = ""
        
        Application.ScreenUpdating = False
                
        strWorkingFile = Trim(Target.Value)
                        
        Set rngFound = Worksheets("Working Folders").Range("B:B").Find(strWorkingFile, LookIn:=xlValues)
        If rngFound Is Nothing Then
            Exit Sub
        End If
        
        strFolder = Trim(rngFound.Offset(0, -1) & "\")
        
        ' Check to see if the workbook actually exists.
        If Len(Dir(strFolder & strWorkingFile)) = 0 Then
            strMsg = "The invoice workbook '" & strWorkingFile & "' does not exist or has been saved in the incorrect folder."
            MsgBox strMsg, vbOKOnly, "Warning"
            Exit Sub
        End If
        
        ' Check to see if the workbook name contains a "'" character. (ASCII Code 39)
        ' This character is invalid as it is used a marker in the formula.
        If InStr(1, strWorkingFile, "'", vbTextCompare) > 0 Then
            strMsg = "The invoice workbook '" & strWorkingFile & "' contains an >> ' << character in the name." & vbCrLf & _
                "This is an invalid character." & vbCrLf & _
                "Please rename the file and edit the reference to it in the '" & "Working Folders" & "' worksheet."
            MsgBox strMsg, vbOKOnly, "Warning"
            Exit Sub
        End If
                                                        
        ' Invoice Date.
        With Range("C5")
            .Formula = "=INDEX('" & strFolder & "[" & strWorkingFile & "]Invoice'!I:I,3,1)"
            .NumberFormat = "dd/mm/yyyy"
        End With
        
        ' Invoice Number.
        Range("C6").Formula = "=INDEX('" & strFolder & "[" & strWorkingFile & "]Invoice'!I:I,4,1)"
        
        ' From.
        Range("C7").Formula = "=INDEX('" & strFolder & "[" & strWorkingFile & "]Invoice'!I:I,5,1)"
        
        ' To.
        Range("C8").Formula = "=INDEX('" & strFolder & "[" & strWorkingFile & "]Invoice'!B:B,5,1)"
        
        ' Balance.
        With Range("C10")
            .Formula = "=INDEX('" & strFolder & "[" & strWorkingFile & "]Invoice'!J:J,41,1)"
            .NumberFormat = "$#,##0.00"
        End With
        
    End If
    
Exit_Handler:
    
    Application.ScreenUpdating = True
    
    Exit Sub
    
Err_Handler:

    strMsg = "There has been an error." & vbCrLf & _
        "Number : " & Err.Number & vbCrLf & _
        "Description : " & Err.Description
        
    MsgBox strMsg
    
    Resume Exit_Handler

End Sub

 

[phxsportz99]

Solved Thank You..

 

[Herakles]

Solved Thank You..

Did it work as is or did you have to change anything?