+ Reply to Thread
Results 1 to 5 of 5

INDIRECT function DATA validation

  1. #1
    Registered User
    Join Date
    10-23-2014
    Location
    Malaysia
    MS-Off Ver
    MS2013
    Posts
    30

    INDIRECT function DATA validation

    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
    Last edited by keby1nko; 10-24-2014 at 01:26 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: INDIRECT function DATA validation

    Hi, welcome to the forum

    From the looks of it in your sample file, the prices of both CS and SS are the same, although I doubt that is the case in your real file?

    If they are indeed the same, why try and split them? If they are not the same, you will need a 2nd set of range names for each set of data. I would call the ranges CSblah, CSblahblah and SSblah, SSblahblah etc
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: INDIRECT function DATA validation

    data validation for plate/pipe ((I19): =INDIRECT($H$19)
    DV for size (J19):=INDEX(INDIRECT(H19&"Size"),,1)
    Formula for weight (k19): =INDEX(INDIRECT($H$19&"Size"),MATCH($J$19,OFFSET(INDIRECT($H$19&"Size"),,,,1)),2)
    Formula for price (L19):=INDEX(INDIRECT($H$19&"Size"),MATCH($J$19,OFFSET(INDIRECT($H$19&"Size"),,,,1)),3)
    Attached Files Attached Files
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    10-23-2014
    Location
    Malaysia
    MS-Off Ver
    MS2013
    Posts
    30

    Re: INDIRECT function DATA validation

    Hi FDibbins thanks for the reply and welcome.

    Yup the actual value are not the same. So there's no other option other than replacing it with completely different 2nd set of range names?

  5. #5
    Registered User
    Join Date
    10-23-2014
    Location
    Malaysia
    MS-Off Ver
    MS2013
    Posts
    30

    Re: INDIRECT function DATA validation

    Quote Originally Posted by protonLeah View Post
    data validation for plate/pipe ((I19): =INDIRECT($H$19)
    DV for size (J19):=INDEX(INDIRECT(H19&"Size"),,1)
    Formula for weight (k19): =INDEX(INDIRECT($H$19&"Size"),MATCH($J$19,OFFSET(INDIRECT($H$19&"Size"),,,,1)),2)
    Formula for price (L19):=INDEX(INDIRECT($H$19&"Size"),MATCH($J$19,OFFSET(INDIRECT($H$19&"Size"),,,,1)),3)
    Thanks for the reply protonLeah, I dun think this is the question that I have posted but by using your way definitely made my life alot easier.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help with Data Validation using INDIRECT function
    By ddub25 in forum Excel General
    Replies: 7
    Last Post: 07-14-2012, 08:33 AM
  2. INDIRECT function and data validation
    By penguintar in forum Excel General
    Replies: 2
    Last Post: 03-23-2011, 04:02 PM
  3. Dynamic Data validation and the indirect function
    By jboyd123 in forum Excel General
    Replies: 1
    Last Post: 01-13-2011, 06:32 PM
  4. Data Validation - Indirect function
    By Newport Count in forum Excel General
    Replies: 7
    Last Post: 06-09-2009, 05:11 PM
  5. Data Validation and Indirect Function
    By solnajeff in forum Excel General
    Replies: 4
    Last Post: 01-05-2008, 04:23 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1