JohnnytheKid
New Member
- Joined
- May 7, 2017
- Messages
- 4
Hello pros.
I received outstanding help when I posted a question here before, and now I'm back to see if anyone may have some words of wisdom on a new issue I'm stumped on.
Here is my task:
Create a spreadsheet that has multiple columns of different parts for a dog fence system (wire, collars, flags, splices, etc.). As criteria is selected from a drop down list in each column, the associated price is posted in a cell beneath the drop down list’s cell.
A separate cell will add up the prices that are posted in each column’s price cell, and give a total.
I use a second sheet “DATA” for the data that will show in the drop down list for each column. In DATA, I several ranges setup, each with 3 columns (Item Name, Cost, and Weight)
There are several ranges for Dog Collars, each includes “1 collar”,”2 collars”,”3 collars”,”4 collars”,”5 collars” in the item name column. Each different dog collar range is based on pricing for collars that associate with different fence systems.
On the main page, when someone selects ‘FenceA’, in the first drop down list, I need the drop down list in the Dog Collars column to offer the range on the DATA sheet that is associated with FenceA.
If they choose ‘FenceB’, from the first drop down list, I need the Dog Collars column’s drop down list to be the one on the DATA sheet that is associated with FenceB, and so on.
I have gotten as far as making the item name show up in the Dog Collars column, when a fence system is selected.
Where I am hitting the wall is populating the cell beneath the Dog Collars selection with the associated price for the number of collars in the range that populates that drop down list.
I used the named range “Fence” for the range of fences in DATA, and called that named range to create the drop down with Data Validation.
I used named ranges for all the collar ranges as well (ie: “FenceA”,”FenceB”,”FenceC”, etc.) Of course, the ranged names are the same as the name of the system they tie in to in the “Fence” range.
In the price cell of each of the other columns, it’s a static set of options for that column’s drop down list, so I was able to use a formula I understand, such as:
=IFERROR(VLOOKUP(D2,DATA!A57:C61,2,FALSE),"")
However, I’m at a loss on how I can format the Dog Collars price cell to post the price associated with the list item selected from the dynamic list that comes up based on selection made in the Fence column.
I apologize if I explained poorly. I would greatly appreciate any assistance or guidance. I’m just learning Excel and may have taken on a task that is just a bit advanced for my level of knowledge.
Thank you in advance.
I received outstanding help when I posted a question here before, and now I'm back to see if anyone may have some words of wisdom on a new issue I'm stumped on.
Here is my task:
Create a spreadsheet that has multiple columns of different parts for a dog fence system (wire, collars, flags, splices, etc.). As criteria is selected from a drop down list in each column, the associated price is posted in a cell beneath the drop down list’s cell.
A separate cell will add up the prices that are posted in each column’s price cell, and give a total.
I use a second sheet “DATA” for the data that will show in the drop down list for each column. In DATA, I several ranges setup, each with 3 columns (Item Name, Cost, and Weight)
There are several ranges for Dog Collars, each includes “1 collar”,”2 collars”,”3 collars”,”4 collars”,”5 collars” in the item name column. Each different dog collar range is based on pricing for collars that associate with different fence systems.
On the main page, when someone selects ‘FenceA’, in the first drop down list, I need the drop down list in the Dog Collars column to offer the range on the DATA sheet that is associated with FenceA.
If they choose ‘FenceB’, from the first drop down list, I need the Dog Collars column’s drop down list to be the one on the DATA sheet that is associated with FenceB, and so on.
I have gotten as far as making the item name show up in the Dog Collars column, when a fence system is selected.
Where I am hitting the wall is populating the cell beneath the Dog Collars selection with the associated price for the number of collars in the range that populates that drop down list.
I used the named range “Fence” for the range of fences in DATA, and called that named range to create the drop down with Data Validation.
I used named ranges for all the collar ranges as well (ie: “FenceA”,”FenceB”,”FenceC”, etc.) Of course, the ranged names are the same as the name of the system they tie in to in the “Fence” range.
In the price cell of each of the other columns, it’s a static set of options for that column’s drop down list, so I was able to use a formula I understand, such as:
=IFERROR(VLOOKUP(D2,DATA!A57:C61,2,FALSE),"")
However, I’m at a loss on how I can format the Dog Collars price cell to post the price associated with the list item selected from the dynamic list that comes up based on selection made in the Fence column.
I apologize if I explained poorly. I would greatly appreciate any assistance or guidance. I’m just learning Excel and may have taken on a task that is just a bit advanced for my level of knowledge.
Thank you in advance.
