Excel Database and Dependant Lists

[RyanH002]

I need some help please.

I have a workbook that I use for my job. In it I have multiple tabs for data input.

What I have is a database full of types of lighting fixtures all of which have different modes and cahnnel counts. The more advanced the mode the more channels they use. As these are made by multiple different manufacturers, they all have different attributes. For example one manufacturer will have modes labelled "Basic", "Extended", another may have "2Ch", "4Ch", "Standard", "Advanced" etc.

I have a database tab labelled "FIXTURES" where all this information is stored. Column A has the names of the fixtures and along row 1 I have put the names of modes. Then it's cross referenced so I put the channel count value in to the approiate cell against the fixture type and mode.

In the tab labelled "Patch" I have 2 drop down lists. The first in column E "Type" that has a list of all the fixtures, the second in column G "Mode" has a list of all the modes. From that column H "Chans" referneces these and produces the correct channel count.

I would like to create a database that when the mode is selected in column E, the drop down list in column G only gives me the modes for that fixture.

I have included my spreadsheet for reference. Happy and open to fully redesigning the database. Less so about other pages. I did not create this spreadsheet and there are lots of formulas that I don't know how they work but they do and I fundamentally want the bulk of the document to stay the same.


Document here: Lighting Workbook


Thanks

 

[Logit]

Am I doing something wrong ? Your Google sheets are saying I need to request permission to view the file.

 

[RyanH002]

Lighting Workbook v2.xlsx

docs.google.com

Try this

 

[felixstraube]

Easiest way I can think of is creating a helper sheet with these formulas (only showing a few rows cols):

Lighting Workbook v2 - copia.xlsx
	A	B	C	D	E	F	G
1
2
3	~VariLite~
4	VL4k Spot					Standard	Extended
5	VL4k BeamWash					Standard	Extended
6	VL35k Wash					Standard
7	VL35k Wash FX					Standard
8	VL6000 Beam					Standard
9	VL5B					Standard
10	VL2600 Profile					Standard	Extended
11	VL2600 Spot					Standard	Extended
12	~Clay Paky~
13	Sharpy					Standard	Extended
14	Sharpy Wash					Standard	Extended
15	B-Eye K20					Standard	Extended
HelperSheet

Cell Formulas
Range	Formula
A3:A200	A3	=FIXTURE!A3:A200
D3:AR200	D3	=IF(FIXTURE!D3:AR200,FIXTURE!D1:AR1,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E7	List	=FixtureMode

(If you later on add more data to that list and excede row 200, you have to update these formulas.)

Then on sheet "Patch" with any cell on row 3 selected create named range

"FixtureMode"

=INDEX(HelperSheet!$D$3:$AR$200;MATCH(Patch!$E3;HelperSheet!$A$3:$A$200;0);0)

Finally in column G "Mode", set data validation to that named range:

Copy down to all cells in G.

You can hide the HelperSheet.

Let me know if this works for you.

 

[RyanH002]

So this would be a completely seperate sheet? If this was the case, is there a way of me linking them so when I add to the FIXTURES database sheet it does the same on the helper sheet?

 

[felixstraube]

Yes it is a new sheet, and it is linked to the FIXTURES sheet

 

[RyanH002]

Ok, I'll give it a try

 

[felixstraube]

Here is a working file for you to download and test:

Lighting Workbook v2.zip