Excel Gurus, lend me your lug'oles.

[The Shaggy D.A.]

May be a simple thing, but my Google-fu is weak today, I have the dumb. I have a single spreadsheet with two sheets, the first contains header information with a summary, and the second sheet contains multiple rows with details relating to the header. As a trimmed down example, sheet one has :-

[Evil Hans]

A quick way to do it would be to have an extra column on sheet one that has the name in it, but only if active is true ... something like

=IF(C2,A2,"")

Then use that column to populate your validation list.

Quick 'n' Dirty
____________________
Triumph Sprint ST 1050. And it's Red.

[The Shaggy D.A.]

Aye, I tried that, problem was that if (say) Thing2 was inactive, you'd get a dropdown list of :-

Thing1
<blank>
Thing3

Eventually, you'd be scrolling through tons of blanks before getting to a valid value.
____________________
Chances are quite high you are not in my Monkeysphere, and I don't care about you. Don't take it personally.
Currently : Royal Enfield 350 Meteor
Previously : CB100N > CB250RS > XJ900F > GT550 > GPZ750R/1000RX > AJS M16 > R100RT > Bullet 500 > CB500 > LS650P > Bullet Electra X & YBR125 > Bullet 350 "Superstar" & YBR125 Custom > Royal Enfield Classic 500 Despatch Limited Edition (28 of 200) & CB Two-Fifty Nighthawk > ER5

[carvell]

[GSTEEL32]

[Evil Hans]

[el_oso]

ignore blanks allows you to not select a dropdown, i.e leave the field blank iirc.

the way i would it, assuming the order doesn't matter on the detail sheet is sort the detail sheet so that the blanks appear at the bottom.

The other way I can think about doing it would be creating a VBA function that recreates the list on worksheet update. Bit of a pain as you have to do all the heavy lifting yourself due to VBA not having any useful helper methods.
____________________
Duke 390
Previous: '05 XR125L | '96 XJ600S Diversion |'05 Suzuki GSXR1000 | '05 Honda CBR125-R | '97 YZF 600R Thundercat | '11 Honda CBR250
Car: Jeep Wrangler 4.0L

[The Shaggy D.A.]

Problem solved, inactive header entries moved to a third archive sheet
____________________
Chances are quite high you are not in my Monkeysphere, and I don't care about you. Don't take it personally.
Currently : Royal Enfield 350 Meteor
Previously : CB100N > CB250RS > XJ900F > GT550 > GPZ750R/1000RX > AJS M16 > R100RT > Bullet 500 > CB500 > LS650P > Bullet Electra X & YBR125 > Bullet 350 "Superstar" & YBR125 Custom > Royal Enfield Classic 500 Despatch Limited Edition (28 of 200) & CB Two-Fifty Nighthawk > ER5

[adriansk]

You can delete all blank rows by selecting your column, press F5, then Special, check Blanks, then OK. Then right click on one of the blank cells and select Delete.
____________________
CBR600F4

[The Shaggy D.A.]

[Andy_Pagin]

This is a classic example of spreadsheets being a two edged sword. They allow non-IT people* to create fairly fancy 'applications', but eventually you hit a point where you need to do something seemingly simple like this and find the spreadheet app cannot do it. Now your stuck because no-one in the company has any real programming skills or knowledge of anything but spreadsheets.

It's not just Excel, all spreadsheet apps stall like this.

*I'm not implying that The Shaggy D.A. is thick, just illustrating the problem.
____________________
They're coming to take me away, ho-ho, hee-hee, ha-haaa, hey-hey,
the men in white coats are coming to take me away.
Yamaha Vity -> YBR125 -> FZS600 Fazer -> FZ1-S Fazer

[The Shaggy D.A.]

My biggest problem was wording my Google query in such a way that would give me decent results. Any hint of "drop down" "validation" "conditional" "vlookup" "index" and it returns the same results. I might have found what I wanted on page 20, but I lost the will to live after about an hour.

Serves me right for trying to build a relational database in Excel.

[edit] Looks like there's an add-in called PowerPivot that lets you set up table relationships :-

https://support.office.com/en-gb/article/Power-Pivot-Add-in-a9c2c6e2-cc49-4976-a7d7-40896795d045#__toc262226430

Might as well just use Access though.
____________________
Chances are quite high you are not in my Monkeysphere, and I don't care about you. Don't take it personally.
Currently : Royal Enfield 350 Meteor
Previously : CB100N > CB250RS > XJ900F > GT550 > GPZ750R/1000RX > AJS M16 > R100RT > Bullet 500 > CB500 > LS650P > Bullet Electra X & YBR125 > Bullet 350 "Superstar" & YBR125 Custom > Royal Enfield Classic 500 Despatch Limited Edition (28 of 200) & CB Two-Fifty Nighthawk > ER5

[GSTEEL32]