I recently got asked to help with setting up a formula that would autofill standard entries for particular species. The idea would be that the user would enter a species name and then the other details would fill in as normal. While the example I’ve given here is for scientific purposes, you could also adapt it to similar scenarios where you need to autofill a lot of data. Here are the headings…
I don’t know about you but my memories of school biology are pretty vague 🙂 but it turns out that these classifications are actually pretty standard. Apparently they are only ever revised every couple of years. So my solution was as follows:
- Enter the data on a separate sheet that has all the data – see Taxonomy sheet in attached file. I got this data from the person concerned.
- Click anywhere in this list. Convert it to a table (Ctrl and T or Insert: Table)
- Making sure you are still clicked in this list, check the Design tab on right hand side.
- On the left you will see a box that says Table1. Amend this to read Species and press Enter (to make it stick)
- Now view the Sample formulas sheet.
- Click in cell B5
- From the Formulas tab, choose Lookup & Reference
- Scroll down to end to get vlookup and enter in the following..
What it all means:
Lookup value: $A5 (this is what’s common to both lists – the item they are trying to identify). It’s set to $a5 so that it’s locked to column A so that I only have to enter the lookup value once.
Table Array: I just typed in Species here because this is the name I gave to the table in an earlier step
Column Index: Column (b2). I found this great little trick at www.datapigtechnologies.com. Essentially by doing this it means all I have to do is enter the column reference once. B2 means the SECOND column in the table array. So when I copy it across it adjusts automatically.
False – to ensure an exact match
You can click on either of the links below to get the files with and without the formulas…
Taxonomy file without_formulas
As always if you have found this useful…comments always appreciated
Excel Products for Sale
Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.
MrExcel.com
Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!) that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.
MyExcelOnline.com
Xtreme Pivot Tables course
This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.
He also has a useful Pivot Table webinar…
This is also a useful chart helper.
Debra Dalgleish – Contextures.com
Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of $10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.
Excel UserForms for Data Entry
The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.
PivotPowerPremiumRibbon
The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function
Chandoo.org
Click here to view more details about Excel Dashboard Video Tutorial
Click here to view more details on Excel Dashboard Templates
Click here to view more details on Excel Dashboard Templates Premium
Click here to view more details on Excel Formula Helper E-Book
Click here to view more details on Excel Formula Crash Course – View Option
Click here to view more details on Excel Formula Crash Course – Download Option
Click here to view more details on Excel School, Online VBA Classes & Dashboard Templates
Click here to view more details on Advanced Excel & Power Pivot Training Classes
Click here to view more details on Excel School & Online VBA Training Classes
Click here to view more details on Excel School & Dashboard Templates
Click here to view more details on Excel School – View & Download
Click here to view more details on Excel School – View Only
Click here to view more details on Excel Project & Portfolio Management
Click here to view more details on Advanced Power Pivot Class
Click here to view more details on Project Management Templates 2003
Click here to view more details on Project Management Templates 2007
Click here to view more details on Project Management Templates Both
Click here to view more details on Project Portfolio Templates
Click here to view more details on the VLOOKUP Combo Book
Click here to view more details on the VLOOKUP eBook
Click here to view more details on Online VBA Training Classes – View & Download
Click here to view more details on Online VBA Training Classes – View Only