Hi, what I'm working on currently is the construction of a spreadsheet for construction material price calculation. The main problem arise when I'm trying to link the material pricing databank to obtain the relevant price when the correspond material is selected. (For instance, when I select "CarbonSteel" as the material, "Pipe" as the parts and "1-1/2" as the size by using INDIRECT function of DATA validation, the correspond weight (kg/m) and price will be shown by using the vlookup function)
What I have faced is there's appears to be clashes in the naming of the subgroup. To explain the situation in detail I have include a detail explanation with a simple example below with reference to an EXCEL file as well.
For instance, there are two main categories of material (eg. CarbonSteel and MildSteel) and the subgroup for these two type of material appears to be the same (eg. Plate and Pipe). If I were to named one of the subgroup "Plate" under "CarbonSteel", the other subgroup under "MildSteel" could not be named after the same subgroup name "Plate". My question is: Is it possible for Excel to recognized the subgroup correspond to the main group without altering the subgroup name? Or is there any formatting formula that I can add into the validation formula so that I can alter the formatting of the data selected so that both the subgroup name appear to be "Plate"? Else is there any other better approaches where I can solve this situation without messing with the formatting?
Attached Excel File
EXAMPLE.xlsx
Bookmarks