Excel 2010 - Auto populating a sheet based on data from another sheet in the workbook

Question

Thanks for taking the time to view my question!

I'm pretty new to Excel, trying to teach myself the basics to get stuff done at work. I've been asked to create a tracking spreadsheet for the maintenance and work orders performed on our fleet vehicles.

I would like to automatically populate a form I created on one sheet based on data from another sheet within the same workbook.

FORM

I would like to be able to enter the work order number in this space and have the form automatically filled out based on:

TRACKER

I need the work order number, the equipment number, the date and the description to be populated within my form based on the data from my tracker.

I know it's possible as there is another tracker within our database that does the same thing, but it's not accessible by my department and the person that wrote it is no longer working here.

Answer 1

This is easily achievable with a couple very simple VLOOKUP formulas.

Here's an example spreadsheet showing the formula you would use in each field:

Here's the same spreadsheet showing how the results would work:

To make this as simple as possible, I have referenced the table by name (Table1). This means that no matter where you move the formula, you're not going to accidentally break the link (unless you move it to another Workbook entirely).

To move the Report form to another sheet in the same workbook, you can actually just select the whole form, cut it (Ctrl+X), then paste it (Ctrl+V) onto the desired sheet.

---

To Reference a Table by name, you can start typing the name (in this case Table) into any formula and it will appear as an autocomplete option: