Excel Help: Master List

Question

I am trying to create a master list on Excel to where I am able to pull all of the different specs when typing in a certain material item. Each row on the master list would contain multiple columns such as: item, labor time, weight. So for example, in the actual sheet I am working in, I could just type in the material item and it would automatically fill out all of the labor and weights in the cells following the material item cell. I was wondering if there was anyway to get this done and if anybody may have a template to go off of? Thanks for any help

Answer 1

Hi Mcm1110_1,

I’m Sharon one of the Independent Advisor and I’d be happy to help you out with your question.

Steps for Setting It Up
Open the _Master List_ spreadsheet: https://colleaga.box.com/s/q1sh25dv3dy5tk2kc6aosw8vo0cc90hl (If you use this link, you will need to download it once it opens so you have it open in Excel and you can edit it.)
Save it using the proper Colleaga naming method (e.g. this document that is called Col001-20190319-001-How to use a master list based on the date and its purpose) with the current date it was created and the purpose section being “*community name* Master List”. Save it into the “Administration” folder within the “Stuff for Sherpas” folder in your program’s folder structure.
Gather a list of participants’ full names and email addresses and input them into the list in both the “Master List” sheet and the “Issue Tracking” sheet (only names are required here).
Fill out the rest of the information, as the template guides you to do except for the “ORGANIZATION” and “LEVEL OF ENGAGEMENT”.
To set up the columns “ORGANIZATION” and “LEVEL OF ENGAGEMENT”, complete the following steps once for each column:
On the sheet called “Drop Down Lists”, fill in the Level of Engagement and Organization lists according to your information. List them like the example demonstrates on the “Example Drop Down Lists” sheet: vertically, one after another, with one item per cell.
Select back to “Master List” sheet. Navigate the top menu bar to the “Data” tab (the tabs are File, Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, Help).
Select the whole column you want to set up by selecting the letter at the top of the column. C for ORGANIZATION and F for LEVEL OF ENGAGEMENT.
In the Data tab, there are subsections with titles along the bottom of the menu bar: Get & Transform Data, Queries & Connections, Sort & Filter, Data Tools, Forecast, Outline. Look under the Data Tools subsection.
1 master list template.JPG

There should be an icon that looks like this:
2 master list template.JPG

Select the down arrow to the right and this drop-down menu will appear:
3 master list template.JPG

Select “Data Validation…”. From the drop-down menu, select list.
4 master list template.JPG

Once list is selected, the window will change to look like the photo below:
5 master list template.JPG

Select the arrow pointing upwards as circled in red above.
Now the window will collapse to look like this:
6 master list template.JPG

Select the “Drop Down Lists” sheet. Then select the first item in the list you want to include and hold down on the mouse as you drag downwards to include ALL the other items in the list. The window will begin to look like this:
7 master list template.JPG

Once you have let go of your mouse and all the items are selected, select the down arrow to the right of the Data validation window to expand the window. The window will look like this after you expand it:
8 master list template.JPG

Select “Ok” button to complete the data validation.
Repeat these steps for the other column.

Notes on How to Use It
Remember to update the list with new information as you find it. Always make new versions of the master list with each time you change information so there is a record of what the master list looked like in the past. You can do this by changing the 001 to 002 and then 002 to 003 and so on.

You can find a more in-depth description of the uses here: https://www.colleaga.org/article/master-lists-purpose

Reference:
https://www.colleaga.org/article/how-use-master-list

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

I hope this information helps. If you have any questions, please let me know and I’ll be glad to assist you further.

Best regards,
Sharon

Answer 2

Hello Mcm1110_1,

Thank you for the update.

You may follow the threads below for more options:

https://www.excelcommand.com/excel-help/excel-how-to.php?i=217634

https://superuser.com/questions/1310263/create-a-master-list-that-other-sheets-reference-and-auto-update-when-master-cha

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

I hope this information helps. If you have any questions, please let me know and I’ll be glad to assist you further.

Best regards,
Sharon

Answer 3

Hi,

The VLOOKUP()/SUMIF() function should work.

Answer 4

Here is something that might work for you if you are using 365.

Create your Materials sheet in whatever way you want, but let the first column be the one you will use to enter an item over on your work sheet. Create a sorted list as seen in F:F of the column A:A or whichever column you listed first - note, that every item in this list must be unique - you can not use the same name more than once.

This example uses the Item name list. The FILTER function is used to exclude the column header ("Item") and blank rows. SORT ensures that the items are listed alphabetically. Notice that this formula is in F2 - the header "Select Item" was hand-typed.

=SORT(FILTER(A:A,(A:A<>"")*((A:A<>"Item"))

Move over to your worksheet and set it up with the lookup item listed first followed by the items to be loaded automatically - notice that it does not matter which order the items in the header are located.

Click in cell A2 and create a Data Validation list - in the Data tab, under Data Tools, select Data Validation. Under Allow, select List; next, click in the Source box; then select the sheet where the materials list was prepared, followed by the column where the Select Item list was prepared. Click OK.

Back in the Worksheet, select "Select Item" in the dropdown list in A2; then with that cell selected, FILL DOWN to as many cells below it that you wish to be able to look up an item.

Place the following formula in B2 and FILL DOWN to as many rows that have a "Select Item" drop down list in them.

=XLOOKUP(A2,'Master List'!B:B,'Master List'!A:D,"")

Whenever you select an item from the drop-down list in column A, columns B, C, and D enter automaticall.

And, due to the way things were set up in the sorted list in the materials page and since the data validation box used the entire column, if you add a new item to the materials list, it populates into your Select Item drop-down lists automatically.

I hope this helps you!