Using Excel Lookup to Auto-Populate Two Different Excel Documents

[SafariGirl]

Please point me in the right direction. Our physicist visit medical clinics/hospitals to inspect and calibrate equipment. A) We have an Excel client list that contains a unique REC_ID field'; B) we have an Excel appointment scheduler that uses the same REC_ID and we want to auto-populate client appointment details and then captures onsite service information the physicist inputs; C) Lastly, we want to auto-populate the physicists' service call information & costs into an Excel invoice template using the REC_ID to pull information from the "Scheduler" to create the "Invoice." That's my dream, but I'm clueless as to how to use Lookup to accomplish the goal. Attached you will find a mockup Client List, Scheduler and Invoice Spreadsheet.  Looking forward to leaving cut & paste world behind!  All comments and ideas are appreciated.

[#2 · Accepted solution]

@SafariGirl 

WHY is the Scheduler laid out as it is--it's a single record on the sheet. Yet it's the one you want to pull most of your invoice data from. The other two are created as decent databases, with multiple clients or services in a tabular format.

So one thing I'd recommend considering, at any rate, is reorganizing the Scheduler sheet so it's laid out horizontally rather than vertically, to allow for more instances.....  then the XLOOKUP formula could be consistently designed to integrate the various elements.

Is it necessary that these be separate workbooks, as opposed to being separate spreadsheets, but all in a single workbook? That makes the use of functions that go across sheets a lot easier.

An unsolicited observation: don't take time to make the spreadsheets pretty before you get the functionality established. It's a common mistake, but often interferes with getting the job done (just psychologically, if nothing else--you don't want to alter something you've made look so great). Get it to work, then you can add colors and other bells and whistles, if you need them at all.

Anyway, I think XLOOKUP might be the best function.... Here's a resource that can help you with it, once your datasheets are arrayed appropriately. https://exceljet.net/excel-functions/excel-xlookup-function

[#3]

The SCHEDULER doubles as a worksheet for our physicists to input data for billing purposes.

Thank you, Mathetes, your observations, thoughts and link were very helpful. I made all the changes you suggested and reviewed the tutorials on exceljet.net. After a little trial and error everything is now working perfectly. Could not have done it without your insight...much appreciated.... SafariGal

[#4]

You're very welcome. Glad it has worked for you

[#5]

@SafariGirl 

Here's a modified sample file that you provided with the solution:

1. Added a RecID drop down list to select from the client database the RecID that then autopopulates the Bill To

I also made the client database into a table format that way the table becomes a dynamic range so when you add new clients, the range follows without redefining the ranges of the formulas.  Let me know if you have any questions.

Cheers

[#6]

Good morning, and thank you for your input. Yes, I 'm using the power of a unique record id to connect client services to invoicing, and to a checkbook register, but formatting the database as a Table was a brilliant new concept for me. I'm a neophyte and have much to learn. Loving Excel, especially using XLookup and Concatenate in the same formula.

Cheers