beginer trying to make a load sheet
IE merc = 10 models =Price
i have done drop down for make and model ok but am having problems trying to auto fill
the price sell with the value of deffernt models
see attached wookbook
many thanks in advance
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Drop down menu with auto complete
0
Answers
0
Selected Answer
I created named ranges on your List worksheet as follows.
- "Titles" = A1:K1
- "List" = A2:L13
In the formulas I will show you below you may replace the names with the addresses they represent but the idea is that when you add items to your list you just modify the named range (or you even made it dynamic) and don't need to modify all the formulas because they already have the name, which doesn't change.
With that done, you can now use the formula below in your data validations. Use the formula in E2 and copy down.
=INDEX(List,0,MATCH($D2,Titles,0))The list will now show the models for the make selected in column D. And here is the formula you asked about. It's designed for F2. Copy down from there.
=XLOOKUP($E2,INDEX(List,0,MATCH($D2,Titles,0)),INDEX(List,0,MATCH($D2,Titles,0)+1),"")As you see, the two formulas are related. The are based on the INDEX function. In this case A2:L13, which I named "List". INDEX(List,1,1)) specifies the first cell in the first row of A2:L13 which would be A2, of course. INDEX(List,0,1)) is a variation of this where the row number is 0. Of course, there is no row number 0. Therefore Excel understands this instruction to mean "all rows in column 1". SUM(INDEX(List,0,2))) would return 95, being the sum of all prices in the second column of List. This method of addressing a column is used in both above formulas.
The column numbers are defined using the MATCH function. MATCH($D2,Titles,0) simply looks for "Vauxhall" in A1:K1 and returns the column where it was found. Because of the relationship between Titles and List (they share columns) the match found can be used to specify a column of List. This is a recurring theme in both above formulas.
Finally, the Price column is always the column next to the one with the make and models. So, the price is found by finding the make and model and then picking whatever is in the adjacent column. You can embed the formula in an IFERROR function to avoid seeing #N/A but, for my taste, #N/A is a clearer answer than a blank cell staring at me, saying nothing.