Detailed Data Validation Help

dxd

New Member
Joined
Jul 19, 2007
Messages
13
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
ABCDEFGH
1MENSHEALTH1/1Page1/1Page1/1Page130x110+3mmbleed150x140+3mmbleed180x110mm
2NATIONALGEOGRAPHIC1/4Page1/4Page1/4Pagedown
3LIFE1/1Insidecover1/4PageDown_1_1_PAGE_1_4_PAGE_1_1_INSIDE_COVER
42/1Insidebeckcover
5
6MAGAZINSMENSHEALTHNATIONALGEOGRAPHICLIFE12x85+3mmbleed130x120+3mm104x120+3mmbleed115x185mm
7
8
9Idon'tknowhowtonamethisIdon'tknowhowtonamethis_1_4_PAGE_DOWN_2_1_INSIDE_BACK_COVER
10
11
12120x130+3mmbleed185x140+3mmbleed
13
14Idon'tknowhowtonamethis_1_4_PAGE_DOWN
15
16
17140,350.00EUR20,00.00EUR351,248.00EUR
18
19HowdoInamethis????HowdoInamethis????HowdoInamethis????
20
21
2253,321.00EUR350.00EUR14654.00EUR
23
24HowdoInamethis????HowdoInamethis????HowdoInamethis????
25
26456.46EUR13654EUR46401EUR
27
28HowdoInamethis????HowdoInamethis????HowdoInamethis????
Sheet1
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
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:

Code:
=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

New Member
Joined
Jul 19, 2007
Messages
13
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

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Can you post me some code or a screenshot of what you have, and any formulas?
 

dxd

New Member
Joined
Jul 19, 2007
Messages
13
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

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
As you're in staroffice, try:

Code:
=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

New Member
Joined
Jul 19, 2007
Messages
13
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

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Hi dxd,

Try this:

Code:
=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

New Member
Joined
Jul 19, 2007
Messages
13
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
ABCD
1MENSHEALTH1/1Page130x110+3mmbleed140,350.00EUR
2NATIONALGEOGRAPHIC1/4Page12x85+3mmbleed53,321.00EUR
3LIFE1/1Insidecover120x130+3mmbleed456.46EUR
41/1Page150x140+3mmbleed20,00.00EUR
51/4Page130x120+3mm350.00EUR
61/4PageDown185x140+3mmbleed13654EUR
72/1Insidebeckcover135X13mm351,248.00EUR
81/1Page180x110mm14654.00EUR
91/4Pagedown104x120+3mmbleed46401EUR
10
11Menu1Menu2Menu3Menu4
12NATIONALGEOGRAPHIC1/4Page12x85+3mmbleed53,321.00EUR
13
invoice
 

Forum statistics

Threads
1,181,398
Messages
5,929,739
Members
436,686
Latest member
chanmeggichan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top