Formula/ macro help needed.

[Chamathi]

Hey there guys, New to the forum and I really need some help.
Heres my problem Im attaching screen shots to better aid you.

*IF D5>2 C7 AND D7 SHOULD BE HIDDEN.

* F6 is a drop down list with FR4,Polyimide
G6 is a drop down list based on F6

I.E
IF F6 is FR4 G6 would have options FR406,FR407,FR408
IF F6 is Polyimide G6 would have options Poly1,Poly2,Poly3

So I need to have either FR4 set of option or Polyimide set of options shown in G6.
If F6 has something other than FR4 or polyimide then G6 should be hidden.

-- removed inline image ---

FIG1


-- removed inline image ---

FIG 2

Now onto my next problem

I need a lookup formula that would look up Company name(D1 FIG 1) to the given part number in D2(FIG1).
Part number isnt unique to companies so different companies may have same part numbers so its important formula would look for company name and find part numbers. and produce it to D3

Thanks in advance

Chamathi.

 

[sijpie]

Welcome to the board.
I'll see if I can answer your first problem shortly. But first a few rules:

• Don't put two question in one post. It is confusing because people won't understand necessarily what is being replied to. So please make a new thread for your 2nd question.
• You cannot upload images or files to this board (I hope one day we can). To show an image, upload the image to Google+, or some other sharing site, then set the share to public and post the URL here in [/img ] tags and it will show up properly.
  [*]For uploading files same thing. But if you want to show just part of a sheet, then for a quick fix put borders around the cells and then copy paste into the post, that gets a decent display.
  [*]Alternative download HTMLJeanie or MrExcelHTMLMaker ([URL]http://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html[/URL]) and get a far better representation of your sheets and formulas.
  [/LIST]

 

[sijpie]

Your linked dropdown lists can be handled with formulas or with macros.

Formula route:
If you want the cells to be hidden you could use conditional formatting. For a dropdown you can just set the list to empty to get the same effect
To link the lists: do a search on excel linked lists
for instance: http://www.contextures.com/xlDataVal02.html has excellent examples

 

[Chamathi]

Thanks for the info.

I managed to solve most of my 1st problem by using the formula
=IF($C$3="FR4",$G$3:$G$6,$I$3:$I$6)
but that leaves me with the problem of the value of the previous selection being left over,I want it to clear if i change from FR4 to Polyimide

Material	Sub material			FR4 material		Polyimide
Polyimide	1			1		q
				2		w
				3		e
				4		r

<tbody>
</tbody>

 

[sijpie]

You will need a little macro to run when you change the first (Fr4<>Polimide) listbox.

 

[Chamathi]

How would I go about that?

 

[sijpie]

Assuming that your sheet looks someting like this, and that the cell being changed is C3 and the range to be cleared is F3:G6 (well to the bottom of the list)

Excel 2010
	C	D	E	F	G
2	Material	Sub Material		FR4 Material	Polyimide
3	FR4	1		1	q
4				2	w
5				3	e
6				4	r
7
Sheet1

then right click on the name tab of the worksheet and select View Code
The vba editor will open in the module of the sheet. This is a special module where things like changes to the sheet can be intercepted. So that is what we are going to do. Past the following code into the empty right hand panel

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">' this will clear range F3:Gx when cell C3 changes</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("C3")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Range("F3").Resize(Range("F3").CurrentRegion.Rows.Count - 1, 2).ClearContents<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Now check the range to be cleared. Does it start from F3? else change F3 in the code to the top left cell to be cleared. I have assumed both columns F & G are to be cleared.
Which Cell is changing? is that C3? Else chang C3 in the code to the cell that is changing from FR4 to polyimide. Now back to your sheet and change the selection. It should clear the two columns