Data Validation List

[Lady123]

I would like to have a drop down list that shows only the labor category types if a user chooses Civil Servant or Onsite Contractor. I think this can be done with an offset formula but I'm not sure.

[Glenn Kennedy]

Create a Named Range called DD (very imaginative DD = Drop Down). Ctrl F3 to view edit:

=INDEX(Sheet1!L:L,AGGREGATE(15,6,ROW(Sheet1!$M$2:$M$48)/(Sheet1!$L$2:$L$48=Sheet1!O7),1)):INDEX(Sheet1!L:L,AGGREGATE(14,6,ROW(Sheet1!$M$2:$M$48)/(Sheet1!$L$2:$L$48=Sheet1!O7),1))

Then select DD as the List in the data validation in your yellow cells.

[Lady123]

Hello,

I tried your formula but I get an error message. When I downloaded your file the yellow cells do not display the dropdown list showing all the categories for Civil Servant Labor or for Onsite Contractor

[Glenn Kennedy]

Works perfectly here.

What version of Excel are you using?

[Lady123]

I switched to a newer version of Excel and it works fine but actually columns P and Q are in a different worksheet called 'Contamination FTE&WYE'. I tried to change the formula to:
INDEX(Sheet1!M:M,AGGREGATE(15,6,ROW(Sheet1!$M$2:$M$48)/(Sheet1!$L$2:$L$48='Contamination FTE&WYE'!F3),1)):INDEX(Sheet1!M:M,AGGREGATE(14,6,ROW(Sheet1!$M$2:$M$48)/(Sheet1!$L$2:$L$48='Contamination FTE&WYE'!F3),1))

[Glenn Kennedy]

You should have said that from the start!!

Anyhow. Check that the ranges in the Named Range haven't adjusted themselves to something daft!!

Please Login or Register  to view this content.

will work. I $'d a few ranges to miniminse the risk of the Named Range going strange... see sheet.

[Pepe Le Mokko]

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

[menem]

Because your data is sorted, you may try this formula in data validation list (Q2)

Please Login or Register  to view this content.

Regards.

[Lady123]

Column P and Q will be in another worksheet called "Contamination FTE&WYE". Columns L & M are in worksheet called Labor Rates: Where it says P2 do I change that too 'Contamination FTE&WYE'!F3,'Labor Rates'!$L$L?

[Glenn Kennedy]

I would advise you NOT to use INDIRECT. It is a volatile function and recalculates every time ANYTHING changes on the sheet. It can cause performance issues.

Any other important things we don't yet know???

[menem]

@Glenn, thanks for your advise. ^_^
I never know about the side effect before. T_T

Regards.

[Lady123]

When I take the formula and paste it into a different excel file and change 'Contamination FTE&WYE" to 'GDS FTE&WYE' and change Sheet1 to 'Labor Rates' it gives me an error. Everything else stays the same.
INDEX(Sheet1!$M:$M,AGGREGATE(15,6,ROW(Sheet1!$M$2:$M$48)/(Sheet1!$L$2:$L$48='Contamination FTE&WYE'!$F3),1)):INDEX(Sheet1!$M:$M,AGGREGATE(14,6,ROW(Sheet1!$M$2:$M$48)/(Sheet1!$L$2:$L$48='Contamination FTE&WYE'!$F3),1))

[menem]

You have change 'Sheet1' to 'Labor Rates' , did you change this part in the formula also ?

Regards.

[Glenn Kennedy]

Lady123,

Menem is correct, and if you open and look at the attachment in Post 10, you will see that I did that for you already.

[Lady123]

Yes, I did change Sheet 1 to 'Labor Rates' and changed Contamination FTE&WYE to GDS FTE&WYE. All the $L and $M and $F3 are correct.

[Glenn Kennedy]

I cannot diagnose what I cannot see...

[Glenn Kennedy]

One thought... Just check that Excel hasn't put " " around rgenamed range formula. If it has, remove them

[Lady123]

Hello, Attached is my file where the Data Validation is not working. I have over 20 files that I would like to paste the code. The tab names and rows could be different from each spreadsheet. The $F3 could be $E2 or $G3

[Glenn Kennedy]

Well, there were two things wrong. One I can explain... the other I have never seen before....

1. Tab: GDS FTE&WYE Delete the space before the G.

2. (Bizarre). Select G3. Open the Named Range (CTRL-F3). delete the = at the start of the formula. Type it in again. Close and save the Named range Dialogue Box. it works.

[kvsrinivasamurthy]

For Data validation
For J2:J3

=OFFSET(INDEX($E$2:$E$48,MATCH($I2,$B$2:$B$48,0)),0,0,COUNTIF($B$2:$B$48,$I2))

For Q2:Q3

=OFFSET(INDEX($M$2:$M$48,MATCH($P2,$L$2:$L$48,0)),0,0,COUNTIF($L$2:$L$48,$P2))