Data Validation Using Filtered Data

[MattGreer]

I have a project estimate spreadsheet I'm developing. In the cost estimate worksheet, I have a cell where I would enter "project type". The project type would define what cost codes are available for that kind of project.

So on Sheet1, I have the cost estimate table and the project type cell. So for example, let's say this is an HVAC project. I enter HVAC in the project type. In another table on another worksheet, I have the table of cost codes and the applicable project type. A cost code is tied to only one project type. I would like the validation in the cost estimate table to only choose from cost codes that apply to HVAC projects.

I can't seem to figure out how to do this. Even when I manually filter the cost code table, all the cost codes appear in the data validation combo box in my cost estimate table.

Your help is much appreciated. Thank you!

Thanks!!


Matt

 

[SkipVought]

Hi,

It would help to show the table that contains your ProjType and CostCode data, from which you will generate the CostCodes for a given ProjType like HVAC.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Generally speaking, if your lookup table (ProjType; CostCode) is sorted by ProjType and then CostCode, then, using Named Ranges based on those table headings, this formula...
[tt]
=OFFSET(CostCode,MATCH(SelectedProjType,ProjType,0)-1,0,COUNTIF(ProjType,SelectedProjType),1)
[/tt]
...in a Data > Validation — Formula or LIST (I’m sitting in a doctor’s office on a iPad)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

For stable sets of codes and project types you can name each range with set of codes and for the set of project types.
In DV list source you can use formula [tt]=INDIRECT("Name")[/tt]. In the best situation (project types are valid names) you can select project type in named cell from one DV list, and have DVs for codes with another DV formula.
Example:
1. Named range [tt]ProjTypes[/tt] with HVAC as one of entries,
2. Named cell [tt]ProjType[/tt] with DV formula [tt]=INDIRECT("ProjTypes")[/tt],
3. Named ranges with codes, as [tt]HVAC[/tt] etc,
4. in cell with code DV formula [tt]=INDIRECT("ProjType")[/tt].

If the source of data is two column table with project types and permitted codes, Skip's solution is more universal.

combo

 

[MattGreer]

edit: Posting from a doctor's office?? Dang skip you are dedicated!! Thanks!

Hey guys, here's a combined screenshot of the AFE cost summary table and the table from which we'd pick cost codes. I shortened it a bit for easier consumption.

Thanks!!


Matt

 

[SkipVought]

Matt, a picture may be worth a thousand words, but...

You previously referred to “project type” and cost codes”.

???

Where are they in your nearly worthless picture example?

I might guess, but we should not need to do that.

AFE Type???

AFE Number???

Description???

Account???

Please adequately explain all this in relation to your original question.

BTW, ACTUAL DATA that we could COPY ‘n’ PASTE would be nice, else anyone who might like to recreate your table would need to transcribe your picture.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MattGreer]

Sorry for the trouble and again, thank you for your help.

I hope the attached annotated spreadsheet makes things more clear.



Thanks!!


Matt

 

[SkipVought]

Your workbook returned with modifications

1) Make Selected Parameters Named Ranges ie AFE_Type

2) Name CostCodes table range using Structured Table references via Formulas > Defined Names > Name Manager
rDescription, rCostCodes, rType

Use OFFSET formula in Data Validation List
=OFFSET(rDescription,MATCH(AFE_Type,rType,0)-1,0,COUNTIF(rType,AFE_Type),1)

I'd also make the selection of AFW Type a DV List. Query your CostCodes table to return a DISTINCT list of Types.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}