Detailed Data Validation Help

[dxd]

When I started doing this I didn't know how to sum two numbers, but I learned plenty through this board. Thank you. Now I know how to make the first dropdown "magazins" and connect it with the second one "menshealth" "nationalgeographic" "life" (bolded names below are the names of the groups). The problem is with third and the fourth, because I have the same values and I don’t know how to name them and then how to connect them. I want to, when I select "menshealth” = “1/1 Page" = "130x110+3mm blead" and that costs 140,350.00 EUR.
The data is on the Sheat1 and I want the dropdown boxes on the Sheat2 starting with the “magazines” at A1
I used formulas,
=MAGAZINS
=INDIRECT(SUBSTITUTE($A$1;" ";""))

I made this threat this detailed because for the beginners like me it is really hard to understand and follow advanced questions on this board. I hope that your answers will help someone else

Pleas help

Book1
	A	B	C	D	E	F	G	H
1	MENSHEALTH	1/1Page	1/1Page	1/1Page	130x110+3mmbleed	150x140+3mmbleed	180x110mm
2	NATIONALGEOGRAPHIC	1/4Page	1/4Page	1/4Pagedown
3	LIFE	1/1Insidecover	1/4PageDown		_1_1_PAGE	_1_4_PAGE	_1_1_INSIDE_COVER
4			2/1Insidebeckcover
5
6	MAGAZINS	MENSHEALTH	NATIONALGEOGRAPHIC	LIFE	12x85+3mmbleed	130x120+3mm	104x120+3mmbleed	115x185mm
7
8
9					Idon'tknowhowtonamethis	Idon'tknowhowtonamethis	_1_4_PAGE_DOWN	_2_1_INSIDE_BACK_COVER
10
11
12					120x130+3mmbleed	185x140+3mmbleed
13
14					Idon'tknowhowtonamethis	_1_4_PAGE_DOWN
15
16
17					140,350.00EUR	20,00.00EUR	351,248.00EUR
18
19					HowdoInamethis????	HowdoInamethis????	HowdoInamethis????
20
21
22					53,321.00EUR	350.00EUR	14654.00EUR
23
24					HowdoInamethis????	HowdoInamethis????	HowdoInamethis????
25
26					456.46EUR	13654EUR	46401EUR
27
28					HowdoInamethis????	HowdoInamethis????	HowdoInamethis????
Sheet1

 

[thorpyuk]

If you select data - validation, then then select a list from the dropdown, you can select the data allowed in that cell..... eg, the 3 magazine names.

You could then do a VLOOKUP formula to bring in this additional information. Something like:

=VLOOKUP(C10,A1:B3,2,FALSE)

where: C10 is the cell you are evaluating (eg the cell that they select the magazine), A1:B3 is the range you are looking in (and which contain the values you want to pull in) and 2 is the column you want to bring in. false dictates that you want an exact match.....

Hope this is some help?

 

[dxd]

thx thorpyuk, I have tried your suggestion, but it didn’t work (I probably did something wrong). I have managed to make two dropdown lists, with above mentioned formulas. One with three magazines and the other with formats, that is dependent on the firestone. The problem is with the third dropdown list. I am really bad with this, and would appreciate step by step. Thx

 

[thorpyuk]

Can you post me some code or a screenshot of what you have, and any formulas?

 

[dxd]

For the three magazines in the row A I selected all three of them and went

1. Insert → Name → Create → and named that category "MAGAZINES".

2. Then I went to the Sheat2 and did: Data → Validation → list → and in the source book wrote =MAGAZINES.

3. for the rows B, C, D I repeated actions from step 1 but named them "MENSHEALTH", "NATIONALGEOGRAPHIC", "LIFE", as Sean on my screenshot.

4. Then again on the Sheat2 i went Data → Validation → list → and in the source book wrote =INDIRECT(SUBSTITUTE($A$1;" ";""))

Now I need to create a third dropdown list that is dependent on the second. Third list should be with measurements and forth with amounts. When my categories are only words I don’t have any problem, with these two formulas I am able to create up to 6 dependent dropdown lists. The problem is with odd letters and numbers.
Thanks

 

[thorpyuk]

As you're in staroffice, try:

=VLOOKUP(C10;A:B;2;FALSE)

This will look at cell C10, and match column A and return the value in column B.... you can use variations on this

 

[dxd]

Ok, I got this working, as you said the problem was in dot. Btw I’m using excel 2003. This works perfectly if I want to pull in exact matches to the magazines from the column 2. And I tried changing the number of column to pull the other data, that works ok but I need to move the cell I write the formula in, but the problem is that I need B1:D4 data displayed in the same cell dependent on the selection in dropdown box eg C10. any suggestions

 

[thorpyuk]

Hi dxd,

Try this:

=CONCATENATE(VLOOKUP(C10,B1:F2,1,FALSE)," ",VLOOKUP(C10,B1:F2,2,FALSE), " ", VLOOKUP(C10,B1:F2,3,FALSE))

Where C10 is the cell you are looking for, and B1:D4 is your range of values. It will bring everything together into 1 cell for you....

 

[dxd]

HI thorpyuk,
I am moving slowly but at least I am moving forward. I tried your suggestion and with little playing with it I made it work. It is going to be useful to me later in my project. But still I didn't resolve my basic problem. Here is the situation I am in now. Menu 1 and 2 are validation lists (dropdown menus), menu 3 (it is not a dropdown) I got with the formula
=VLOOKUP(D24;D13:F21;2;FALSE), menu 4
=VLOOKUP(D19;D8:F16;2;FALSE),
the problem Is when I select a National geographies in menu 1 I get the options In menu 2 and I select eg ¼ page, then in menu 3 and 4 I should get data from the national geographic line (green colour) (130x120+3mm => 350.00 EUR) but instead I get the data that are meant for men’s health (yellow colour), (12x85+3mm bleed => 53,321.00 EUR), I presume this is because there are two same values. I am going to have many same values for different categories
Is there some way I can get around this, you see what I am trying to do
thx

Book3.xls
	A	B	C	D
1	MENSHEALTH	1/1Page	130x110+3mmbleed	140,350.00EUR
2	NATIONALGEOGRAPHIC	1/4Page	12x85+3mmbleed	53,321.00EUR
3	LIFE	1/1Insidecover	120x130+3mmbleed	456.46EUR
4		1/1Page	150x140+3mmbleed	20,00.00EUR
5		1/4Page	130x120+3mm	350.00EUR
6		1/4PageDown	185x140+3mmbleed	13654EUR
7		2/1Insidebeckcover	135X13mm	351,248.00EUR
8		1/1Page	180x110mm	14654.00EUR
9		1/4Pagedown	104x120+3mmbleed	46401EUR
10
11	Menu1	Menu2	Menu3	Menu4
12	NATIONALGEOGRAPHIC	1/4Page	12x85+3mmbleed	53,321.00EUR
13
invoice