May 04 2021 03:59 PM
May 04 2021 03:59 PM
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.
May 04 2021 08:26 PMSolution
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
May 07 2021 11:57 AM
May 09 2021 11:56 PM
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.
May 13 2021 05:28 AM