Aug 17, 2017 Click the Settings tab, then in the Allow pop-up menu, click List. Click in the Source box, then type the text or numbers (separated by commas) you want to appear in the list. To close the dialog box, click OK. The menu bar on Excel has changed to black and I am unable to view all the icons which is very frustrating. How can I alter the colour of the menu bar back to Classic? I am using macOS Sierra, Version 10.13.6 and a late Mac 2012. Question too Me too Me too. All replies Drop Down menu. First Page 1 of 1 Page 1.
Most of the blog posts that deal with customizing the toolbars on Microsoft Office products are written for PC users. Now that MS Office has become the standard for office applications on the Apple Macintosh OS, too, let’s discuss how to.
Please note that these instructions are for the Office 2016 version of the software. Add/Remove Commands to the Ribbon Toolbar To access the configuration screen for the Ribbon toolbar, open a new workbook in Excel and then go to the Preferences menu and select Ribbon and Toolbar. The screen that comes up is of the double list box variety. If you’re unfamiliar with this type of screen, basically you have two boxes with buttons in between them that have arrows facing in opposite directions. You can move items in either of the lists back and forth to the other by selecting an item from the list you want to move the command from and then hitting the arrow button of the direction you wish to move it to. You can also change the order in which the commands appear by dragging them up or down the list as you wish.
There are also drop down list controls at the top of each box so that you can select which category in the toolbar that you want the command to come from or go to. This gives you the most flexible interface for customizing the ribbon toolbar any way you desire. Below is an example image of the Ribbon Toolbar configuration screen.
Adding/Removing Toolbar Tabs or Groups By default, when you open the toolbar configuration screen, it opens with a list of the main tabs in the right-hand box and a list of available commands in the left-hand box. To access the other tabs, you can select another category from the Customize the Ribbon drop down list.
If you want to create your own tabs or groups that contain a custom list of commands, you can create a new tab by clicking the + button at the bottom of the Ribbon Customization list box and selecting New Tab or New Group from the context menu that appears. If you decide that there are too many tabs and they are cluttering up your user experience, then you can select the tab you want to go away and then select the – button at the bottom of the Ribbon Customization list box. If you want to rename the tab or group, select it in the list and then can click the gear button to the right of the – button. This will give you the settings context menu that will allow you to make changes. Quick Access Toolbar Customization If you wish to configure the Quick Access Toolbar, which is the one at the very top left-hand corner of the application, you need to select the Quick Access Toolbar option on the right of the toolbar selection bar. Then you can select from the commands list box on the left and add any commands that you use consistently.
The Quick Access Toolbar is designed to house commands that you can access quickly without having to hunt for them inside other toolbars, tabs, or groups. This toolbar is for things like saving and printing and other regularly used file commands. You can even create custom macros and commands and add them to the Quick Access Toolbar or Ribbon Toolbar if you like. Being able to customize the Excel toolbar on a Mac is a little different than on a PC, but has made great strides in making the applications more consistent, robust, and easier to configure for both platforms. They’ve worked on making them look and behave similarly, too, since now most people use both platforms. This is very different from 10 years ago when most people were in either one camp or the other.
No matter which platform you use, you can always benefit by getting trained on Microsoft Office. Explore the many today.
In this post, we’ll explore a method to allow a user to select an item from a drop-down list and then use formulas with the VLOOKUP function to retrieve multiple values from the related item table. Objective Before digging into the mechanics, let’s review our objective with an example. We would like to allow our user to select items from a drop-down list, and then have Excel automatically retrieve multiple values into separate columns for the selected item, as illustrated below. Once the user selects an ItemNum from the drop-down, Excel retrieves the related attributes (Description and Price) from a related items table. Details There are several moving parts to this solution, and we’ll take them one at a time. Overall, the drop-down list is created with the Data Validation feature, and provides the user with the list of choices based on the item table. Once the user selects an item from the drop-down, Excel formulas populate the price and description columns with the VLOOKUP function.
The steps that follow demonstrate:. How to store the items in a table. How to create a named reference. How to create the drop-down with the data validation feature. How to retrieve the item attributes with the VLOOKUP function Let’s do this. Store the Items in a Table The first step is to store the items in a table.
The main benefit here is that the table will automatically expand to include any new items added in the future. To store the items in a table, use the Insert Table ribbon icon. (Note: Tables were first available in Excel 2007 for Windows.) We want to assign our table a descriptive name, so rather than using the default table name of Table1, we’ll change it to tblitems with the Table Tools Design Table Name ribbon field. The resulting items table, named tblitems, is shown below.
Create a Named Reference In order to create our drop-down list based on the ItemNum column of the tblitems table, we’ll need to set up a custom name. To do this step, simply select the ItemNum column, excluding the header, and then open the Name Manager (Formulas Name Manager). In the Name Manager dialog, click the New button. In the resulting New Name dialog, set the Name to dditems (or any other preferred name) and ensure that the name refers to the table column tblitemsItemNum as shown below. Click OK and then close the Name Manager dialog. Create the Drop-Down We’ll create the drop-down with the Data Validation feature. Simple select the cell that should contain the drop-down, and then the Data Data Validation icon.
We want to Allow a List equal to dditems, as shown below. If you’d like your user to add multiple items, as we’ve done in the first animation above, it is convenient to store the input range in a table as well. That way, the Table will automatically carry the drop-down into any new rows. Retrieve Attributes with VLOOKUP Once the user selects an item from the drop-down, we’d like Excel to retrieve the related attributes from our items table (tblitems). There are a wide variety of ways to accomplish this part, and we’ll use a basic VLOOKUP function. If you have a preferred approach, please share by posting a comment below.
In the Description column, we’ll ask the VLOOKUP function to look up the ItemNum in the items table and retrieve the related description with the formula below. =VLOOKUP(@ItemNum,tblitems,3,0) Where:. @ItemNum is the lookup value, the item num in our input table. (If you aren’t using an input table, you can use a traditional cell reference, such as A1.). tblitems is the lookup range, the items table.
3 is the table column that has the value we wish to return, the 3rd column, the description column. 0 tells the function to perform an exact match of the item num Optionally, if we wanted to return an empty cell instead of an error, we could wrap the function in an IFERROR function, as follows. =IFERROR(VLOOKUP(@ItemNum,tblitems,3,0),') Where:. VLOOKUP returns the related description, or #N/A if not found. “” tells Excel to return an empty text string if the VLOOKUP is an error We use a similar formula to return the price from the 2nd column, and we are all set. Now, the user can select an item num from the drop-down and Excel automatically retrieves the description and price from the item table, as shown below. If you prefer other approaches to the steps presented above, please share!
Post a comment belowthanks! Additional Resources. Sample file:.
Other VLOOKUP posts:. Other Table posts:. Other Data Validation posts. January 20, 2015 at 3:53 pm. Hi, I believe that I followed your instructions correctly. However, I continually get an error when I try to enter the VLOOKUP function.
This is what I am entering in for the formula: VLOOKUP(@code,insurancepolicies,2,0) Where “code” is Column A of the spreadsheet and “insurancepolicies” is the name of the table created. Yet, I continually get this error “The name that you entered is not valid. Reasons for this can include: name does not begin with a letter or an underscore, name contains a space or other invalid characters, the name conflicts with an excel built-in name or the name of another object in the workbook.” I changed the name of Column A in table manager to see if that worked and I still got the same error. Where did I go wrong?. jefflenning Post author. January 30, 2015 at 8:55 pm.
I was able to get this to work but I am wondering how I would do it based on two drop downs. Example being I have one drop down that has 1/2″ (or 3/4″, 5/8″) and the next has 12# (or 14″, 16″) they can mix and match but depending upon the two chosen I would like to output the data that corresponds to it in the chart. Currently, I have it in one cell as (1/2″ 12#, 1/2″ 14#, 1/2″ 16#) and (3/4″ 12#, 3/4″ 14#, 3/4″ 16#) and so on. It would make for a more efficient drop down list if they were two separate lists. Please advise.
jefflenning Post author. February 11, 2015 at 8:27 am. Hi Jeff, Thank you for the tutorial, I found it very helpful. This Is exactly what I have been trying to do, well mostly. I have two questions.
In my item description cell after the vlookup function is entered I recieve and error #N/A. I think this is because the first cell of my item code column is blank.
I need it blank because I do not want a value in it until I select which one. Once I select a value it auto populates the item description correctly. Is there any way I can get rid of this error and just have the item description blank as well until i select an option in the item code drop down list. My second question may be a little more difficult. I want to auto populate the item code cost as well, but with a hitch. I have three different billing rates depending on the customer,(subsidized,non-subsidized, and external) so three different cost columns.
I created another drop down with the three customer types. Can I tie it in so that I first select the customer type, then the item code and have it auto populate with the correct item code price?
Greg. jefflenning Post author. February 11, 2015 at 4:06 pm. Greg, To handle issue 1, I’d suggest using the IFERROR function to return “”this is illustrated in the final formula of the blog post: =IFERROR(VLOOKUP(@ItemNum,tblitems,3,0),””) It is also in the sample file in case you want to check it out in a working Excel file. To handle issue 2, I’d suggest using the MATCH function because it can figure out which column has the value to return based on your customer type label. You can use the MATCH function as the third argument of the VLOOKUP function or use the powerful INDEX/MATCH combo instead.
This combo is discussed here: Hope this helps! Thanks Jeff. frank Sloan. February 13, 2015 at 8:36 am. Frank, One possible approach is to store the changing price values in a table that includes dates. For example, in a table named Archive, the columns would be DateFrom, DateTo, ItemNum, and Price. Then, you store an archive of all historical and current prices.
To retrieve the price, instead of using VLOOKUP you use SUMIFS with comparison operators. For example: =SUMIFS(ArchivePrice,ArchiveDateFrom,”=”&A1,ArchiveItemNum,B1) This way, the transaction date in A1 has be be greater than the DateFrom value and less than the DateTo value. Hope this idea helps! Thanks Jeff.
Lee. March 23, 2015 at 12:53 pm. Jeff, Thanks for posting! This is exactly what I’ve been researching.
I’m having some difficulties using the VLOOKUP formula because my source data (table array reference) is located in a different worksheet from the one where I want the data to populate. Whenever I type in the VLOOKUP formula, it seems to me that I get an error because of the table column reference is on another worksheet. When I tried input the name of the worksheet and the table column number, I generate an error unless I type in the worksheet name and the column letter + column number. (e.g., SourceData!H8 must be input rather than SourceData!8).
Either way, the formula does not yield my desired result. What, if any, suggestions do you have?. jefflenning Post author. April 8, 2015 at 12:13 am. Hi Jeff; Thank you for the above details, they are very helpful. I am trying to use your Vlookup to retrieve some items from inside my table. It is not working ? i am using Excel 2013.
My table name is: daysinamonth the first column from where i am taking the name of the month is called “monthlist” this is the header the cells below “monthlist” have the name of the months and the whole chunk is called “mymonth” my drop down list (in another sheet) is reading from the mymonth list. The formula used is: =IFERROR(VLOOKUP(“@myinputlist”,daysinamonth,6,FALSE),”error”) and it is giving me error all the time. I tried: =IFERROR(VLOOKUP(“@mymonth”,daysinamonth,6,FALSE),”error”) also it is giving me the same error.
Can you help please? Robert.
jefflenning Post author. April 14, 2015 at 12:56 pm. Robert, AhI seethanks. In that case, the lookup value is in a table, so, the structured table reference for the current row would include square brackets around the column name.
For example, if myinputlist is the column header, use @myinputlist instead of “@myinputlist” and if mymonth is the column header then use @mymonth instead of “@mymonth” and so on. The thing is, Excel should automatically insert the correct structured table reference for you if you click the desired table cell while writing the formula. Hope it helps! Thanks, Jeff. Robert.
June 29, 2015 at 4:19 pm. Hi Jeff I admire your patience for all the questions!
I’d appreciate it if you could answer one more. I’m using Excel 2007 and got the formula to pull back data, but it’s only pulling the corresponding value from the row where the formula is entered. The formula in cell B2 is ignoring my selection from the drop down (A2) and pulling back a value from cell I2 (When it should I7) Target Sheet Ingredient Cost per g/ml Quantity Cost (Pence) Flour (self raising) 0.24 3 0.72 Sugar (granulated) 3 4 3 Source Table Ingredient Size of pack Price Cost per g/ml Sugar (brown) 1000 249 0.249 Sugar (muscovado) 1000 240 0.24 Eggs 12 13333 Formula =IFERROR(VLOOKUP(Ingredient,Recipe,4,0),””) Ingredient is the look up column and Recipe is the table name. jefflenning Post author. June 29, 2015 at 8:37 pm. My best guess at this point is that you’re telling Excel (unintentionally) to use an implied intersectionwhich is simply a fancy term that means when you name or reference an entire column, such as naming a column Ingredient, a formula that references the name will reference the cell in the column that is in the same row.
Thus, if you name column I Ingredient, VLOOKUP(Ingredient) tells Excel to reference the cell that is in the Ingredient column and the same row as the formula. One possible solution is to use a specific cell reference such as I7, or VLOOKUP(I7,). Hopefully that addresses the issue and resolves the problemthanks!
Thanks, Jeff. renier smit. July 6, 2015 at 4:24 am.
hi, i wonder if anyone can help me please. I managed to get my drop down list right (A1 down to A10) options are “quoted” and “invoiced” in column B1-B10 i will put my own values. The question i have if what is the exact formula should i want a quick look at the end result for example on sheet 2 i just want to see what are the total invoiced and what are the total quoted.
I hope my question make sense, i would appreciate if someone can assist me. Thnx in advance. Debbie Maiella. July 6, 2015 at 12:47 pm.
I’m trying to make a schedule whereas the first column of my drop down consists of all available shifts. The second column is the number of working hours that will worked on that shift.
In the problem cell, I want the cell to look at what shift has been selected, and display the number of work hours. There will be multiple days in the range, so in full swing, I would like it to add together all the work hours selected in that week so we can keep track of hours vs. Kurt LeBlanc. August 7, 2015 at 5:16 pm. Hi Jeff, Don’t know if you’re still monitoring this site, hopefully you are as I am having great difficulty in getting this to work.
I have created a range of data S7:W79 Row 7 are headers. My Drop Down is B6. I want to read data from column T into B13 I want to read data from column U into C13 I want to read data from column V into D13 I want to read data from column W into E13 I have tried Insert Table $S$7:$W$79 tick My table has headers OK. September 3, 2015 at 10:06 am. Karen, The VLOOKUP function isn’t really designed to do that, and although there is probably a clever way to write a formula that does such a task, there are some easier options to consider. For example, if you have Excel 2013 for Windows, you could have the user select the author from a Slicer instead of a drop-down cell, and then the table will immediately filter to show all related titles, genre, cost, and any other attributes stored in the table. To do this, convert the data range into a table by selecting any data cell and then select the Insert Table ribbon icon.
Then, Insert a Slicer for the author column. Now, when you pick an author from the Slicer the table is immediately filtered for the selected author. If you are on Excel 2010 for Windows instead of 2013, then you could create a PivotTable report to display the desired attributes and then use a Slicer to filter the report for the selected author. In Excel 2010, Slicers can’t be used to filter a table, but, they can filter a PivotTable report.
Hope it helps! Thanks Jeff. Kevin.
September 17, 2015 at 10:31 am. Kevin, If you just want the total on the summary sheet, then, I would recommend using the SUMIFS function to compute the total of all matching data rows based on the conditions found in the four drop downs. If you want to view the details instead, that is, all matching data rows instead of a single total, then I would probably use Slicers instead of drop downs. Starting with Excel 2013 for Windows, Slicers can operate on (filter) tables. So, I would store your data in a table (Insert Table), and then set up slicers for the desired columns, and then as the user makes selections in the slicers then the table will filter accordingly, displaying all matching data rows. Alternatively, the detail table is stored on a data sheet, and then you could create a PivotTable on the summary sheet and use Slicers to help the user filter the summary report.
Slicers can operate on PivotTables in Excel 2010+ for Windows. Hope these ideas help! Thanks Jeff. Kevin. September 15, 2015 at 12:55 am. Hi Jeff, This article really helped me alot!
However, I have a few queries. I would like to do up an equipment list with select-able quantities. What I mean is that, there will be a drop down menu to select the type of equipment I would like, and the quantity will be dependent on the type of equipment chose. So let’s say, I have 5 of equipmentA and 10 of equipmentB, when I select equipmentA, on the quantity side, I will only want to see 1 to 5, but when I select equipmentB, I would want to see 1 to 10. Is there way to do this? And also, excel do not allow spaces when defining names, so I read about using vlookup to create a table but I’m lost as to how it works.
Can you help me out?. jefflenning Post author. September 17, 2015 at 10:14 am. Pauline, A couple of ideas come to mind.
One way, which I think is pretty cool, is to use Data Validation to create a drop-down for the Equipment column. Then, for the quantity column, use Data Validation to allow a Whole Number between 1 and X, where, X is the allowable quantity. You could use a lookup function like VLOOKUP to retrieve the allowable max quantity from a related table. If you prefer to have the quantity cell be a drop-down instead, then, you could store the quantities in a related table but the thing is, you’ll need to list out each quantity individually, that is, have say 5 rows for EquipmentA that show 1, 2, 3, 4, 5.
This is a bit tedious, especially if the max allowable quantities change frequently. That’s why I think I personally prefer the first option above as it would be easy to maintain over time.
Hope this helps, and best of luck! Thanks Jeff. Alex. October 8, 2015 at 11:48 am. Alex, Sure thingyou can tell Excel which sheet should be referenced by prepending the sheet name (inside single quotes) with an exclamation mark to the range reference.
For example, if your lookup value in stored on a sheet named Sheet 1 in cell A10, then, you’d use ‘Sheet 1’!A10. Likewise, if the lookup range is stored on a sheet named Sheet 2 in a range A4:B5, then you’d use ‘Sheet 2’!A4:B5. This is how you can control which sheet is used for each argumenthope it helps, and best of luck! Thanks Jeff. Alison.
October 12, 2015 at 11:54 am. Hi Jeff, I hope I’m not repeating the same question as everyone else, but I’m having a problem with this. Here is my situation I have several tabs with vendor information (name, email, phone, etc), I am trying to create a drop down for each vendor that will automatically pull in the email and phone information off of the name. I thought I had everything working, but the vlookup is not validating off of the vendor name. I am using =vlookup(Electrical!A:A,tblElectrical,2,0). I’m thinking the problem lies in that I am not able to use the @columnname for some reason.
On a separate note, I realize that I would have to create a vlookup for the other columnsunless there is a way to pull in multiple columns in one vlookup? Thank you for your help and this awesome tutorial! October 13, 2015 at 8:36 am. Hi Jeff, I figured out one part of my issue. But now have another one that I’m not sure is related to this thread. Please feel free to delete if not applicable On the same workbook, I have about 30 vendor tabs with data (name, email, contact, phone). I want to create a form (per se), that allows the user to select the vendor type and once that is selected, the drop down will list the appropriate vendors to choose from (ie, plumber will only show the plumbers we have listed); I have these two drop downs created, but now I want to vlookup the other information to pull into the form.
![Mac Mac](/uploads/1/2/5/4/125492925/380495643.png)
If I use vlookup and if then statements, the code will be huge because I have to include all 30+ vendors. Is there an easier way to bring in the additional data? Thanks in advance!. jefflenning Post author. October 19, 2016 at 9:22 am.
Hey Gram I figured it out:) It takes a little work since Excel doesn’t recognize the alphabet as a data type. I created a table of the alphabet named “alphabet.” Then you’ll have your drop-down somewhere. Below it I used an INDEX with MATCH for the row number. The MATCH will look at the cell above, in the “alphabet, 0 for exact match.
Add 1 to it to return the next letter in the sequence: =INDEX(MATCH(A1,alphabet,0)+1) Then I wrapped the formula with IFERROR and depending if you want the sequence to start over whenever it reaches “z,” have the second argument be “a,” or if you want the cells to be blank after “z,” have the second argument as “”. =IFERROR(INDEX(MATCH(A1,alphabet,0)+1),”a”) =IFERROR(INDEX(MATCH(A1,alphabet,0)+1),””) Let me know how that works out, Kurt LeBlanc. Ross Day. January 15, 2016 at 9:06 pm. I have used this and it works perfectly.
I am now trying to go one step forward. I have a list of plants, plant sizes, and pricing that i want to use. I would like to select from a drop down list a plant name, then in the next cell have the size option available in a drop down, and once that is chosen the price automatically populate. I can email the sheet or a jpeg if that would help. I’m not sure i’m describing this 100% but hoping i can get some help.
jefflenning Post author. January 18, 2016 at 6:52 pm.
Thank you so much for this information. I fear that I am trying to do something that cannot be done. I have a set of worksheets that compute the costs associated with a construction job, based on the information in an “ItemCost” worksheet. I have recently run into a situation where I need to maintain 2 different ItemCost worksheets.
I don’t want to duplicate all the various other worksheets that reference ItemCost (too complicated to maintain), but thought that if I could have 2 ItemCost worksheets (say 1 and 2), then I could use the VLOOKUP function to pick the cost sheet that should be used, and then substitute that worksheet name into the cell formulas. So, for example, if I have a worksheet titled PRICE1, and want to retrieve the value that is in cell A1, normally I would just reference =’PRICE1′!A1 I have played around with this, and have gotten as far as: =”‘”&VLOOKUP(B6,PriceTable,2,0)&”‘!A1″ where B6 is the cell where I have picked the desired table choice from the drop down as you describe above. What I get back is the desired string, but displayed in the cell as a string, instead of being used to reference the contents of the cell in the other worksheet.
So, when I select an item from the drop down, in the VLOOKUP cell, it displays: ‘PRICE1’!A1 just like I want it to (so I know it is retrieving the information I want) – it just isn’t using that information to reference the defined cell and retrieve the information from that cell. I have tried an alternative to this method – having 2 different columns in the ItemCost table – one for each variation in price, and then using the VLOOKUP to return the column identifier (say, “A” or “B”). But I get the same result. The cell displays the string that was built instead of the result of referencing the “foreign” cell contents.
Is there another level of redirection, or addressing that I have to use in order to force it to use that cell reference, instead of just displaying the string? Or perhaps VLOOKUP is the wrong tool to use? Thanks again for all your help developing this information!!.Dave. jefflenning Post author. February 4, 2016 at 4:23 am. Hi there, Please help.
I have a 15 sheet excel 2007 spreadsheet. I have all my data on the 15th sheet. On the first 14 sheets are our 14 tenants. The data on the 14th sheet contains menu items in column B and the corresponding cost of each item in column C. I have inserted a table constituting the two columns and rows making up the menu items and their associated costs B4 to C21 (if I include the headers).
I named this menuitems. I have managed to create a drop down option in column B of each of the tenant sheets but cannot for the life of me work out how to get it to populate the corresponding cost in column C on the tenant sheets. Any help you could provide would be hugely appreciated!. jefflenning Post author. February 4, 2016 at 12:33 pm. Tess, I have a feeling you are really close, so hang in there.
In your menuitems table, I’m assuming that the menu items are in the first column, and the related costs in the second column. If that is true, then a VLOOKUP should do the trick. You’ll use a VLOOKUP function on each of your 14 tenant sheets. Assuming the first menu item selected by the user is stored in cell B1, the related formula in say C1 that retrieves the related cost would look like this: =VLOOKUP(B1,menuitems,2,0) That formula should lookup the selected menu item in cell B1, within the menuitems table, return the value in the second column (the cost), and 0 means exact match. Give this a tryI have a good feeling it will work. Feel free to hit me back if it doesn’t! Thanks Jeff.
Matt. February 22, 2016 at 6:55 pm. Hi Jeff, I’m really struggling to get this to work and I have absolutely no idea why.
I’m using an input table with the table name of tblservices, although I’ve tried various alternatives. The column header is referred to as Provided So I’m creating a VLOOKUP as follows =VLOOKUP(@Provided,tblservices,2,0) But whenever I press enter to finish, I get an error that says the name I entered is not valid. It lists 3 reasons, – the name does not begin with a letter or an underscore. – the name contains a space or other invalid character – the name conflicts with an in-built Excel reference or the name of another object in the workbook I’ve genuinely tried everything. Changing table names, table headers, tried copying the formula from your example to fit it around my requirements. But I’m no Excel expert and it’s proving time consuming.
I’d rather my own attempt just work. Regards, Matt. Cindy. February 23, 2016 at 8:09 am. Hello, It seems I’m having the same trouble a few others in the comments had. I have a table named “ReferenceTable” on Sheet 2 set up as follows: Column A – item number Column B – price Column C – description (I named this column “packages” ) The drop down menu is on Sheet 1 Column C. I’d like the prices to auto-populate in Sheet 1 Column M depending on which item in the drop down menu is selected.
I’m entering this formula into cloumn M: “=VLOOKUP(packages,referencetable,Sheet2!B:B,0)” but keep getting a “#N/A” or “#VALUE” error message. I’ve tried replacing the zero with “FALSE” and tried naming the price column and referencing it instead of using “Sheet2!B:B.” Still no luck, please help! Thanks a ton!. Kurt LeBlanc. March 22, 2016 at 2:15 am. hi jeff, i think my situation is also similar to earlier discussions on here, i have two sheets, one with a schedule based on morning,afternoon and night shift for team leaders and i’m hoping to populate the shifts done daily on the other sheet.
How do i make the data auto populate based on changing a particular cell to morning,afternoon and night shift. Also, there are Associates attached to each Team lead and i’m also trying to have a count of associates working daily under the Team Leaders. I hope my explanation isn’t too vague to understand. jefflenning Post author. March 25, 2016 at 12:51 pm. Where do I enter the =Vlookup funtion? Am I entering it in the same cell that the drop down is in, or am I entering it somewhere in my reference table I’m confused?
I have a table called discrepCodes. I selected the cells underneath the first column which is called “Descriptions” and created a name (just used yours, dditems). The table has two columns. The description column and the codes column and I want when I select the description in the drop down for it to populate that cell on another sheet and then simultaneously add the code in the cell right next to it.
I don’t know what I’m doing wrong but I enter: =VLOOKUP(@Description,discrepCodes,2,0). I have tried it without the @ and I still get “Name you entered is not valid”, or “the formula you typed contains an error”. Kurt LeBlanc.
April 5, 2016 at 3:14 pm. Jeff, nice work. Im looking for a multi dependent dropdown list solution. Here is the problem. I have an Electrical Components list with headers Model, Voltage, Wattage, etc.
Each model can have none or multiple values for any header eg Voltage could be 12, 24, 120 for model A, 120, 240 for model B, no entries or model C etc. What I want is to have in Col A (Model) a dropdown of models (easy), then in col B (Voltage), a dropdown list that would have entries 120 and 240 if Model B chosen or 12,24,120 if Model A chosen or nothing if Model C chosen. Similarly the column for Wattage and other items that describe the component. I have looked at basic dependent dropdowns using indirect and vlookup and get these working but this is beyond any examples I have seen online so far. Kurt LeBlanc. May 2, 2016 at 5:54 am. Hi Jeff, You did exactly what i need, but i tried most of my day to get it working, and it still doesnt work.
Sheet 1, Collumheads Day of month, day of week, schoolname,phonenummer, contactperson Sheet 2 collumhead, naamschool; phonenr;visitingadreszipcodecontactperson After selecting the right school from the drop down (that works) in sheet 1 i would like to have all the other information filled in from the same list. The function i use to get the phonenr: =VLOOKUP(@Schoolnaam;Scholentbl;2;FALSE) But i get a #N/A Hope you can help.
Kurt LeBlanc. May 13, 2016 at 6:16 am. You are doing great Jeff! Please I need your help on this: I’m working on generating report sheet for each student in a class.
I have the students performance on each subject on separate worksheet which I named with the subject name. On the report card worksheet, I created a drop down list of all the children in the class. My aim is to select a child’s name in the drop down and all the child’s score in each subject is pulled from all the worksheet and populated into the corresponding table created on the report sheet worksheet. Kurt LeBlanc. May 20, 2016 at 10:54 am. Hi – Your articles are very helpful, so I’m hopeful you’ll be able to help me out. I’m a gifted education teacher with 65 students.
I offer 40 field trips a year to them, and they can pick which ones they want to go on. I created an excel sheet with their names down the left side, A3, and the trips across the top, Starting with D1. Where they intersect, I placed a number 1 in the cell showing they attended the trip. I need to create a summary of trips for each student. Besides copying and pasting for each student, Can excel do this? I have SOME knowledge of excel. Seems like you’re the guy to ask, based on everything I have read.
?. jefflenning Post author. July 3, 2016 at 11:43 am.
Hi Jeff, Its awesome to learn a new function. Thanks for explaining in detail. I have tried VLOOKUP function to develop a diet chart. The dropdown menu selects the food items and its specified quantity in the first column. Like ‘Chicken wings, 100gms’ and the second to fifth columns shows up the total calories, fat, carbohydrate and protein respectively.
Its working perfect. I have extended it down for further meals and items. On the top, the date is given and using the ‘SUM’ function calculates the whole details as required. My question is, in a scenario where the user wants to input 150 or 200gms of chicken wings (in the above given example), what modification should I do to get an automated results in the second to fifth columns?. Kurt LeBlanc. July 30, 2016 at 1:09 pm. Jeff, thanks for this tutorial.
After running through it, I’m very close to a solution for my scenario but revieve a name error in the cell containing the VLOOKUP formula. As with the tutorial, I’m matching a dropdown list item to a related value, with Items / values stored in a two-column table on a separate tab. Tab name: LookUp Values Table name: fooditems Column 1 Header: Food Item (I have named this column’s values as ‘dditems’) Column 2 Header: Calories The formula I’m trying to use (grabbed from a comment above): =VLOOKUP(“@Food Item”,fooditems,MATCH(“calories”,fooditems#Headers,0)) This is on Excel for Mac 2011. Any help is greatly appreciated. Kurt LeBlanc. August 1, 2016 at 12:53 pm. Wesley/Kurt – I’ll also chime in here and say that if you receive a #NAME?
Error, that typically indicates that Excel doesn’t recognize a function name, structured table reference, or named range. After reviewing your formula, it would appear that the first argument of the VLOOKUP function is enclosed in quotes “@Food Item” and since it actually represents a structured reference, you’ll want to remove the quotesuse @Food Item instead. Also, as Kurt pointed out, you’ll want to define the 4th VLOOKUP argument as 0 for exact match. Hope it helpsthanks! Thanks, Jeff. Weslley. August 1, 2016 at 1:51 pm Thanks to both of you — I greatly appreciate the assistance.
I’m not very familiar with Excel’s syntax, so your feedback is really helpful. I’ve modified the formula to read: =VLOOKUP(fooditemtable@Food Item,fooditemtableFood Item,2,0) The table name is fooditemtable. Column one in labels Food Item, and the name for this column’s range of values is source. Column two is a simple list of number values that correspond to the matching cell in column one. This edit handled the #NAME error, but results in a #REF error. The table has only the two columns, and this version of the formula I created using the formula builder.
Based on the information provided, any ideas on where the #REF error could come from?. Kurt LeBlanc. August 4, 2016 at 9:55 pm. Hi Jeff I really appreciate your efforts in putting this post and your patience in answering all the questions.
I hope you won’t mind answering one more question. I want to use a range in lookup value instead of a single cell reference because I am want Description and Price value for ItemNum that I select from the Drop Down, which means if I select a different ItemNum in Drop Down then values for Description and Price shall also change accordingly. In your example the formula can be written in 2 ways: 1) =vlookup(@ItemNum,tblitems,3,0) OR 2) =vlookup(A1,tblitems,3,0) Whereas I want to use something like this =vlookup(A1:A5,tblitems,3,0) as this will help me to automatically update the values for Description and Price according the ItemNum selected in the Drop Down. I hope I have made my question clear to you. Thanking you in anticipation for your support. Kurt LeBlanc.
August 20, 2016 at 10:24 am. No problem Joe:) I’m not sure what the exact cell references are for your project, so I will just assume the drop-down is in A1 and the cell you want to change is in B1 (but you can adjust the references as needed). So in B1 you would wrap the existing formula or figure with IF and put it as the second argument of each IF: =IF(A1=Yes”,existing data + 20,IF(A1=”No”,existing data)) Let me know if you understand that and if any parts confuse you. I’m monitoring these blog comments as well so feel free to ask whatever you need:) Kurt LeBlanc. Kash.
September 6, 2016 at 8:09 am. Hey Kash, The blog can show you how to create the drop-down, but the issue you’re facing is pretty simple:) I suggest creating a table of the two columns with the corresponding numbers and letters on the same row. Then your formula back in the second column by your drop-down can lookup the letter in the table you created and return the corresponding number:) I don’t know the specific locations of your data, but this is the logic: =VLOOKUP(DROP-DOWN LETTER, TABLE, 2, 0) Let me know if that helps! Kurt LeBlanc.
Rabea Rohde. September 8, 2016 at 7:12 pm. Good Day! I have this scenario I would like your help with please.
I have a table (on sheet2) with Ingredients, amount bought, its cost and cost per gm. Now I have my Recipe (on sheet 1), where i choose the Ingredients of a drop down list created from “sheet 2” but I don’t want the amount bought or the costs to be shown in my recipe. What i want to happen is this: I add the recipe amount in column 2 and in column 3 I’d like to have the correct cost of gm (which corresponds with the chosen ingredient of the drop down list) multiplied with the amount shown in column 2. As I have many Recipe’s to calculate I don’t want to look up the cost in gm manually. Can you help me with this, please?. Kurt LeBlanc.
October 6, 2016 at 10:58 am. Hey Isaac Fortunately I just did something similar to my own spreadsheet:) The way to do it is to have the VLOOKUP have a range-type lookup where you put the price points, per their effective date, in a table and sort it by the oldest price first. The function will return the first price for any date up to the next price-change date. That may be a little confusing so I’ll give an example: =VLOOKUP(report date,price table,nth column, 1(for range)) Have the record in the final report include a date column that you can use to link to the pricing table and return the price. Let me know if you can understand that and if it works out! Kurt LeBlanc.
Mary Balian. October 31, 2016 at 7:03 am.
Good day: I need a bit of help since this tutorial is good for retrieving items in different columns. I think I need something similar but retrieving info form different rows within the same column. Let me explain. I have two columns with different dates. The first column is the StartDate column, the second is EndDate; these are columns in my tblcicles table. In my tblpc price calculation table I have a cell where I manually input the length in months the contract is for. Beside that cell I used the data Validation to have a drop down list and select any of the dates from my startdate column in the tblcicles table.
But in the cell beside the start date, I want excel to calculate the end date based on the months I input manually, and grabbing the dates from the end-date column in my tblcicles table. So that if I input 3 months, and I select 1-Mar-2017 from my drop down Start Date list, excel would automatically return 30-Jun-2017. Please help and thanks in advance! October 31, 2016 at 9:44 am. Hey Kurt, Thank you for the super quick reply! The EDATE is indeed a very good tool and would be the perfect solution for the application I am trying to solve, were it not because it returns one more day than what I desire.
To clarify, I have my “Cycles Table”, which has dates based on two cycles, not quite bi-weekly, but exactly from the 1st of each month to the 15th of each month, and from the 16th of each month to the last day of each month. My “Start Date” column contains the dates such as 1-Mar-2017, 16-Mar-2017, 1-Apr-2017, 16-Apr-2017 and so on on the “End Date” column I have dates such as 15-Mar-2017, 31-Mar-2017, 15-Apr-2017, 30-Apr-2017 respectively.
This is because a contract can start either the 1st of each month, or the 16th of each month. However, the contract length is measured by month, not by cycle.
So if I select from the dropdown menu 16-Mar-2017 in the “Start Date” column of my Price Calculation table, and I want to price out a contract for 2 months, which equal 4 cycles, I want excel to calculate “End Date” grading the information from the “End Date” column I have as part of my “Cycles Table”, because this table contains the exact end date, which is the last day of the month, or the 16th. If I use EDATE, and I select 1-Mar-2017 as my start date, and I input 2 months as my contract length, it returns 01-May-2017, not the desired 30-Apr-2017. I wish I could somehow select the cel that contains an equal match to the selected Start Date, and offset it by one column and 4 rows, if that makes any sense. Kurt LeBlanc. October 31, 2016 at 3:47 pm Hey Frida I understand:) The EOMONTH function will handle those that start on the first. What you can do is have a simple IF for your formula where it uses EOMMONTH if the start date is the first and EDATE otherwise like follows: =IF(DAY(startdate)=1,EOMONTH(startdate,contractterm),EDATE(startdate,contractterm)-1) I subtracted 1 from the EDATE based on what you wanted in your example. You can leave that out if you need.
Let me know how that works! Kurt LeBlanc. chip kelyman. November 2, 2016 at 11:05 am.
Hi Jeff, I was wondering if you could help me out with this.i found your tutorial on the web. This is a simple form, on the “requisition” sheet I would like for an operator to be able to select a piece of tooling from a dropdown box and have it automatically fill in the price to save him (or her) from having to look it up. I am by no means fluent with excel beyond the simple functionsand I seem to be making this far more complicated than I think it really is. Again, I’d appreciate any help you could give me. Thanks, Chip Kelyman. Kurt LeBlanc. November 3, 2016 at 7:22 am.
Hmmmwhich cell is the formula in? And it’s just a simple mistake in your formula otherwise: the cell reference is in quotes, so Excel reads that as the actual text. Remove the quotes and make sure the formula is in the cell you want the result in. Cells have 2 values: stored and displayed values.
Every cell can store a formula and display the results. I think you might be thinking you need the formula somewhere else. Let me know if that helps, Kurt LeBlanc. chip kelyman. November 11, 2016 at 9:49 am. Hello Kurt, I have created a data entry page in Excel that populates an Invoice/Purchase Agreement form as well as other necessary forms. The invoice has columns titled “Quantity”, “Item”, “Description” and “Price”.
The Item column contains a drop down box in each row that is attached to an Item Table. I would like to do exactly what your demonstration above shows, but the formula does not work. This is the formula I entered. =IFERROR(VLOOKUP(@Item,tblitems,3,0),””) The error message I get says “The name you entered is not valid”.
When I click OK, Excel returns to the formula with @Item highlighted. I have also tried the same formula except with the “@” in front of “Item” deleted. What am I doing wrong? Any help would be greatly appreciated. Sincerely, Chris. Kurt LeBlanc.
November 19, 2016 at 8:31 am. Hello Again Kurt, Tried the suggestion from your email, but it didn’t work.
Excel gave me the same name error message. Copied and pasted the exact formula from your email, but that didn’t work, same error message.
I’m missing a step here somewhere. I have 4 tables connected to drop down boxes, 2 of them have an Item column. In the formula, it doesn’t seem that I’ve told Excel which table or which column of data to return.
![Down Down](/uploads/1/2/5/4/125492925/832144126.png)
What have I missed? Did I set up the tables incorrectly? They work correctly with the drop down boxes.
Thank you for your help. Chris.
Kurt LeBlanc. November 11, 2016 at 5:40 pm. This is a very useful tutorial but I have a slight expansion on this that I need to make. We have a list of items and I can retrieve it multiple ways, but once I select the item I need to provide 2 sizes that can be selected to calculate the price.
For example: Item 1 – 1; 2′; $1.00 Item 1 – 2; 2′; $2.00 Item 1 – 1; 3′; $2.00 Item 1 – 2; 3′; $6.00 Item 1 – 3; 3′; $9.00 I have a sheet containing every item individually and a grid that lists the items based on the sizes select. So once they pick the item from the drop down list, I want the 2 size columns to list the available size choices. How can I provide this secondary lookup to list my seizes?. Kurt LeBlanc. December 19, 2016 at 5:32 pm. Hello, Hoping you can help me out, can’t quite seem to figure this out! I have created a Table called “SpaceTypes” on the second Sheet of a workbook, that has 3 columns.
I named the second column “SpaceTypeNames” and it includes the items that I want in the drop down list. On the first Sheet of a workbook, I am able to get cell A8 to have the drop-down with those names. In cell C8 of that first Sheet, I’ve typed =VLOOKUP(A8, SpaceTypes,3, 0) but keep getting a #N/A. Not sure what I am doing wrong??? Thanks in advance!!.
Kurt LeBlanc. January 4, 2017 at 9:00 am. Hey Tracie The #N/A error is returned because the formula can’t find something. VLOOKUP looks at the left-most column in a range, so when you use the table as the reference, the formula looks in the first column of the table to find the lookup value. That’s why it can’t find the value you are searching for:) You can handle this two ways: 1.
Move the column to the first position or 2. Use the INDEX/MATCH combo for your formula instead. This blog explains this process really well: Let me know if you need any further help! Kurt LeBlanc. Tracie. April 18, 2017 at 12:20 pm. Jeff: I thought my autopopulate problem was solved with the Item Num table you used.
In my case all values in my table are text. I defined my first column ads typeitems for the data for my drop down list. Based on user selection of an item from typeitems I want to populate a text field from colum 2 of the full table tbltypefill. I entered the following formula =vlookup(@typeitems,tbltypefill,2,0) and get syntax error messages even with variations such as =vlookup(typeitems,tbltypefill,2,0) and replacing 0 with FALSE. Is the problem the fact that my data is text, not numerical?? Advide is most appreciated.
Bob. Jeff Lenning Post author. April 25, 2017 at 10:58 pm. Hi Jeff!!
Based on the selection in my drop-down list, I want to return a value by looking up in one of my multiple sheets. For example, my first drop down list has two options Fruits and Vegetables. My second drop down list then gives the following options for Fruits: Red Fruit and Green Fruit; for vegetables: Red Vegetables and Green Vegetables. And maybe a third drop down dependent list where Red fruit can contain apples, strawberries etc.
Now I have different tables/sheets for Red Fruit, Green fruit, Red Vegetables and Green Vegetables which describe the mass, date of harvest, etc for each fruit. I want to return for example the Mass in my main sheet based on the drop down lists. How can I do that? I believe the problem is slightly complex. Thanks a lot in advance!. Jeff Lenning Post author. May 5, 2017 at 4:55 am.
Hi Jeff How can I best do this for a table that needs to look down a column as well as across rows? I’m trying to design a tool using v-lookup that will help my agents quickly identify the suitability AND cost of a product, based on the age of the customer. Currently my date looks a bit like this: Age Product 1 Product 2 Product 3 0-39 5000 4600 2500 40 – 59 2500 2000 1500 60 – 70 800 500 N/A I’d like the tool to be able to call the prices of the different products based on the age selected – I hope this is possible and this whole thing makes sense! Love this site – learnt so much so far! Lloyd. Jeff Lenning Post author.
July 10, 2017 at 1:08 pm. Hi Jeff, I have a question regarding drop down list and auto update to a cell after every new entry. Please help me.
My excel sheet is as below: Column A= Empty Column B= Date Column C= Store Name Column D= Cost in USD Column E= Purpose (This is where my drop down list is chosen from, the Validation Criteria list is obtained from Column S List (List has Rent, Grocery (Cell=S12) equivalent value is T12, Phone, Medical, Utility etc as options – something similar to a monthly budget) So, I would like the Grocery Cell value (T12 in column T) to fill automatically and get the cost updated/added up (up to date cost) every time I add a grocery charge. Such as below: Data–Store Name–Cost–Purpose “Column T” for “Cell T12” value to be –Walmart–$26.50-Grocery After Entry - T12=$26.50 –Sams Club–$16.50-Grocery After Entry - T12=$43.00 –Walmart–$26.25-Grocery After Entry - T12=$69.25 –Costco–$6.50-Grocery After Entry - T12=$75.75 –Walmart–$10.50-Grocery After Entry - T12=$86.25 And so on. Thank you, Sam Arthur.
August 9, 2017 at 11:23 am. I have looked and looked and don’t see anywhere how to create an input table. I have seen on line how to create a table but not an input table. Simply creating a table won’t allow me to copy the drop down list and the Vlookup rules to lower rows. PLEASE help me.
I am so frustrated. I have my companies on sheet 2 called Rules I want sheet one to have a input table called Quotes. I want to be able to do a drop down from Rules under the Company column in Quotes. Then I will manually input prices on that row.
I also want to drop to lower rows, use the drop down list to pull in different Company from Rules and it populate the specifics for that company so the Quotes table ends up having several companies with their specific info populated from the Vlookup so I can compare quotes for various companies. Thank you. Harpreet P. August 14, 2017 at 10:28 am. Hello Jeff, I am trying to do something a little more complex.
For example I have a column that states width, one for height, one for depth, and then also one for type of product. I want to be able to select all the correct information and based on that I want he price to populate.
Type (colour): white (this could change to other styles and colours) Width: 10 (this could change to another number) Height: 3 (this could change to another number) Depth: 10.26 (this could change to another number) Based on the above, I would like the price (with a combination of all selections) to generate in another selected cell. Is this possible? Please advise.
Thank you, Harpreet P. Darren. May 24, 2018 at 12:58 pm. Hi Jeff, I have a drop down list in cell D12 named Function.
I have 8 options that can be chosen from the drop down. (example 1000,2100, 2210). In Cell E12 named Object, would like to create a drop down list that depends on what is selected from Function D12.
Function 1000 has 19 selections, 2100 has 3 selections, 2210 has 19 selections. I don’t know which “EXCEL FUNCTION OR FORMULA” to use to make this happen. If D12 drop down is used, would like for E12 selections to be determined by what is selected in D12. Almost a drop down list inside a drop down list. Familiar with VLookup, but it only allows for one column to be selected instead of a drop down list. I look forward to your response. Kurt LeBlanc.