Data Validation - Dependent Columns

[baranboz]

Hello All,

I am new to Excel VBA programming and I would like to ask you a question related to data validation.

Here is my case:

In sheet1, I have two columns. I have report names (include duplicate report names) in column A, I have report metrics in column B.

For Example:

1st Quarter 2013 Review Data Sub Claim Count
1st Quarter 2013 Review Data Subrogation Recoveries
2012 4 Qtr Review_California_Final Avg Net Rec / Referral
2012 4 Qtr Review_California_Final Avg Recovery Per File($)
2012 4 Qtr Review_California_Final Closed Files (#)

I have a simple VBA code removing duplicate report names and copy & paste them to sheet 2 - column A.

In sheet3, I have column A which has dropdown list(Data Validation from sheet2 - column A). I have column B for report metrics.

I select a report name on sheet3 - column A and I would like to see all its metrics on column B.

I believe that I should use VBA for this purpose. Could you please help me to understand how I can show all the report metrics related to the report name which is selected from dropdown list?

Thanks in advance

Baran

[JBeaucaire]

As long as sheet1 has all the column A values that match together, then you do not need VBA to do the dependent drop down to show the column B options as a list based on the chosen column A value. A data validation list using OFFSET() can handle that nicely in realtime.

Attach a small sample workbook and I can show you directly. Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[baranboz]

Hi Jerry,

Thank you very much for your response.

Please find enclosed Sample.xlsm file.

In Metrics sheet:

We have two columns, report names & related metrics (report names are duplicate because one report may have multiple metrics)

In Unique_Reports sheet:

We have one column and this column is filled by CopyUnique macro. We only copy unique report names from Metrics sheet here.

In Report Comparision sheet:

We have four columns, report 1 name, report 1 metrics, report 2 name and report 2 metrics.

I would like to add two dropdown lists or combo boxes on both column A and column C.

The user will select the name of the report and then the related metrics will be found on metrics sheet & will be listed on column B for report 1 and column D for report 2.

I could not remove duplicate report names with data validation feature so I had to add Unique_Reports sheet to copy unique report names for dropdown lists in report comparision sheet.


I hope I could explain you well what I am trying to do.

Thank you for your help!

Baran

[JBeaucaire]

1) We created a named named range called ReportOptions that updates itself to include all the values listed in column A of Unique_Reports.
2) On the Report sheet cells A2 and C2 now use the new ReportOptions for their data validation
3) B2 and now uses this dynamic formula to list the metrics for the chosen Report in A2:
=OFFSET(Metrics!$A$1, MATCH($A$2, Metrics!$A:$A, 0)-1, 1, COUNTIF(Metrics!$A:$A, $A$2), )

In D2 is a similar formula except the A2 references are C2.

[baranboz]

Hi Jerry,

Thank you very much for your help, it looks great!

In this case, I would like to ask you one more thing.

Currently, the user selects the first report name on column A and then the dropdown list on column B is filled with the related report metrics.

Is it possible to change this a bit as following:

As soon as the user selects the first report name on column A, column B is filled with the related report metrics without dropdown list. All the related metrics can be written row by row on column B. (The same for column C and column D)

My point is that the user may forget about selecting all the metrics on column B and column D. If we can fill column B and column D automatically as soon as the report name is selected, it would be perfect for me.

Do you think this is possible in Excel?

Thank you !

Baran

[JBeaucaire]

Directly below the selected report we can indicate how many metrics there are.
Then we remove the drop downs from the metric columns.
Then this formula in B2, copied down:
=IF(ROW(A1)>A$3, "", INDEX(Metrics!$B:$B, MATCH(A$2, Metrics!$A:$A, 0)-1+ROW(A1)))

[baranboz]

Hi Jerry,

Thank you very much! It works great!

A very last question if you don't mind.

We have two metric columns (column B and column D). The user selects both report names and these columns are filled automatically.

I would like to compare & count all common metrics in column B and column D. (exclude empty/blank cells)

I tried the formulas below however they don't give the correct result. I believe the problem is empty/blank cells

Please Login or Register  to view this content.

Do you think you may help me to understand this case as well?

I attached the excel file with common metrics.

Thank you for your time.

Baran

[JBeaucaire]

1) Create a named range called Report1Metrics:
=OFFSET('Report Comparision'!$B$1, 1, , COUNTIF('Report Comparision'!$B$2:$B$200, ">a"), )

2) Create a named range called Report2Metrics:
=OFFSET('Report Comparision'!$D$1, 1, , COUNTIF('Report Comparision'!$D$2:$D$200, ">a"), )

3) Formula to count matches:
=SUMPRODUCT(COUNTIF(Report1Metrics, Report2Metrics))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[baranboz]

Hi Jerry,

It works, thank you very much for your help!

Baran