I actually solved my own problem. Is it appropriate for me to reply to my own question, or should I delete the post? It seems like this might be useful to someone else so I am going to go ahead and provide the solution.
This is done using the VLOOKUP function. It took me some time to work it out, but VLOOKUP searches a column for a value, and then returns the value in a specified column of the row that has the searched for value. I wish I could say that more clearly, but the following example should help:
On my invoice tracking sheet I created a table that has column headers "Invoice Number", "Client", "Address", "Date", "Project Title" etc. On my form I created a table to display the information for a specific invoice. The Invoice Number was a cell in that table. I then used the following formula for the cell I want to display the client name:
=VLOOKUP(B8, Invoice Log::Table 1::A1:M10, 2)
B8 is the cell in the table where I type in the invoice number. "Invoice Log" is the name of the sheet that contains the table with the client data. "Table 1" is the name of the table on the "Invoice Log" sheet with the client data. A1:M10 is the entire range of that table. 2 is the for the second column of that table which is the table that contains the Client Name.
The VLOOKUP formula searches the first column of the Invoice Log table for a row that matches the lookup value--in this case, the invoice number I entered into B8 of the table in the form. It then returns the value in the column I designated in the return column term.
For each additional bit of data I wanted to display on the form I just had to change the column number to correspond to the column that has the data. It works beautifully. There may be a more elegant solution, but I'm pretty proud of myself for figuring this one out. This will make creating new invoices much easier.
