Can't figure out how to approach this.
I'm trying to put together a price menu based on selections from a user based on car info: make, model, category and service.
Once a user selects a service based on those criteria via drop downs the hour for the job is populated from a pricing table.
Suggestions?
Cell D13 = SUMIFS(PRICING[Labor Hours],PRICING[MAKE],$E$9,PRICING[MODEL],$F$9,PRICING[Category],[@Category],PRICING[Service],[@Service])
Please complete your location and MS Office version.
Last edited by josephteh; 05-13-2021 at 11:31 PM.
Yes, you need to update your profile with your version - it makes a difference as far as the solution. If you are NOT using MS365, you can try this in cell D13:
=INDEX(PRICING[Labor Hours],MATCH($E$9&$F$9&[@Category]&[@Service],PRICING[MAKE]&PRICING[MODEL]&[Category]&PRICING[Service],0))
if you are on MS365, you can try this in D13:
=XLOOKUP($E$9&F9&[@Category]&[@Service],PRICING[MAKE]&PRICING[MODEL]&PRICING[Category]&PRICING[Service],PRICING[Labor Hours],"Not found",0)
or this:
=FILTER(PRICING[Labor Hours],(PRICING[MAKE]=$E$9)*(PRICING[MODEL]=$F$9)*(PRICING[Category]=[@Category])*(PRICING[Service]=[@Service]),"None found")
Corrected formula in Post #2.
Thanks, XLOOKUP worked. Awesome!!!
I updated my profile I'm in the USA and on Office 365.
I had to change first two cell references and make them both absolute.
I also removed the "not found" message to blank ""
=XLOOKUP($D$9&$E$9&[@Category]&[@Service],PRICING[MAKE]&PRICING[MODEL]&PRICING[Category]&PRICING[Service],PRICING[Labor Hours],"",0)
This will work, but it'd be nice if . . .
Is there a way to display only related choices in the drop downs? I couldn't figure out how to make drop downs from the PRICING table that only showed one instance for each make, then model . . .
example: If Toyota is selected in the Make, can the model choice be limited to only Toyota models. Same thing would apply to the category and services.
Thanks!
I'm not an expert at it, but I made it happen for Make and Model on the attached and you can probably continue to the others. You may get better responses here, or by doing a google search.
But here's what I've done in the past and what works for me. For the secondary level categories, I first create a table for one of the Top Level categories - for Ford for instance. I would select the column in the table, and give it the same name as the top level category (in this instance, I named it "Ford"). In your case, the user would be selecting the Make in cell D9, so when I'm in the cell where I want to see the secondary drop-down, I'll go to data validation and select "List", and in the "Source" box, I entered:
=INDIRECT(SUBSTITUTE(SUBSTITUTE($D$9," ",""),"&",""))
So the D9 is where the top level category is, and this assumes that the named range of your secondary level is named the same as your top level category.
See attached.
Last edited by Gregb11; 05-14-2021 at 09:46 PM. Reason: add attachment
How many Makes, Models, Categories and Service Types are there?
See attached: Would "Price_tbl" work for you? As Models are unique to a Make they can be "ignored" in the Price table. the Make headings are purely for information.
The Price "lookup" is simply a match between type of Service and Model.
in D13
=IFERROR(INDEX(Price_Tbl!$D$3:$K$9,MATCH($C13,Price_Tbl!$C$3:$C$9,0),MATCH($E$9,Price_Tbl!$D$2:$K$2,0)),"")
A further possibility is to list (Data Validation) only those Service types applicable to a given Model, using the presence of an "Hourly Rate" as the criterion for including in the service list.
Added a dynamic Named Range "Service_list" in sheet "Lists"
in A2
Copy down
Data Validation C13
List ==> "=Service_List"
Last edited by JohnTopley; 05-15-2021 at 11:33 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks